Function help?

[Jester]

That looks good WilBert, and I can actually follow it OK!

The thing is, I want it to show when and how much to order, not necessarily what stocks are left.

I think I can use your method though, if I don't find something

[Rob]

I'd insert a blank row below row 3 and in cell b4 have:

=IF(ROUNDUP((SUM($B3:C3)-SUM($A4:A4))/100,0)<0,0,(ROUNDUP((SUM($B3:C3)-SUM($A4:A4))/100,0))*$B$2)

[Jester]

Cheers SOBD that works, but it doesn't allow zero stock.

ie. Item B
Stock = 100
Wk1 req = 300

I therefore need to order 200, whereas the formula tells me I need to order 300.
I can't immediately see what I need to change in your formula to get what I want.....?

[Rob]

I've initially assumed that B1 is the batch size and there is zero opening stock.
Also, the formula assumes a lead time of 1 week, so the formula result in C4 shows the amount to be ordered in week 1 to be able to satisfy the demand in week 2.

To answer your qeustion, to represent an opening stock figure I have added -100 to cell B3 and 300 in cell C3. The result in B4 is then 200 i.e. order 200 in week 0 so that there is enough for week 1.

[Jester]

I'm sorry, think I was still asleep when I replied before....?


It works well enough ASIS - what I'm looking at isn't actual stock, but stock required to meet predicted demand. It's perfectly right to assume a starting figure of zero.



Thanks again!
(I don't wholly understand the formula, but I think I will get there! )

[Jester]

Here we go again!

This one should be easy, but apparantly it isn't......



I have a column of numbers, with gaps in some rows at irregular intervals:

24
15
6
99
123
2
SPACE
14
7
45
SPACE

etc....

I want to add a total (or subtotal) in every space - totalling the numbers since the last space/total.
That's all


I though I could just highlight the column and select data/subtotal, but that needs a reference column.
I could add a column nect to the one with the numbers in, which works, but it turns my sheet into a pivot table and looks messy.

Can't it be done more straightforward than that?


Forgot to say there are hundreds of rows, so very time consuming to select every space and add a total that way!

[Surprised]

Why would inserting a column turn it into a pivot table?

[Surprised]

Try this

1) Select the full range of cells with blanks
2) Press F5 and click on Special
3) Select Blanks and click on OK
4) Press the Autosum button


This will put the formula in all blank cells. It wont put a total after the last set of numbers though.

[Jester]

Why would inserting a column turn it into a pivot table?

That's a very good question.....


There were lines with '+' and '-' against them on the left, misinterpreted that as a pivot table but it just defines the subtotals doesn't it.....
It put the subtotals at the top of each set of numbers as well - why would it do that?!

Reckon it's not subtotals that I need after all.... Just plain and simple totals!




Just seen your next post - giving it a go!

[Surprised]

Remove the subtotals first before you do it.

[Jester]

That works!

Thanks!




How about this:

Can you select several cells in a column, not adjacent ones (the totals I just put in as above in fact!), and copy them across to the next column?

I can't do the next columns in the same way because there are more empty cells than in the first column
eg.

12
33
space
17
1
3
space
space
77
4
total required (of the ten rows above)


That's the last one for today - I hope?!

[Surprised]

If the other cells are just numbers and not formulas you can
Do the F5 thing again and click special but this time select "formulas"
Then just copy and paste:special:values

Assuming you want values and not the formula copied?

[Jester]

They're all formulas - didn't make that clear did I....

In the next columns there are different formulas, but I still want to total in the same rows as the first column.
Because the formulas are different I can't just copy the whole column.

[Surprised]

are you adding the same number of figures?
ir if column A has
12
33
space
17
1
3
space
space
77
4

will column B need to add 2 numbers than add 3 numbers and then add 2 numbers?

[Jester]

I'm afraid not - completely random.


I think I might be expecting too much with this one....
I can do it manually of course, so don't spend too much time on it!

[Jester]

I just found out the F5 blanks thing works with formulae as well - that's going to save me loads of time!!


Thanks again!

[Surprised]

May be an easier way but this way is quick and it seems to work.

[Jester]

It's Monday - another week another challenge!

I've created a large worksheet that I now need to pull information from.
I was going to do this with VLOOKUP but have a problem.....

Each item on the sheet has a different number of lines of information.

eg.

Item 1 is in A1
The information I need to lookup is a total in, say D5

Item 2 is therefore in A6 (Left a blank row between each item)
Information required is in D16

Item 3 is in A18
Information in D21

etc.

Can I do this with vlookup or is there a better way?
Will I need to make each item take up the same number of rows (by adding empty rows for instance)

I hope it can be done....?

[murf]

If the 'total' is the maximum (highest) number in the range then why not create a new cell (in line with A1 etc? - E1??) that looks for the maximum in a range. You can then VLOOKUP in column E.

[Mystery]

If there's a differentiating column, say, the month or something in another column then you could subtotal any other column.

[Jester]

If the 'total' is the maximum (highest) number in the range then why not create a new cell (in line with A1 etc? - E1??) that looks for the maximum in a range. You can then VLOOKUP in column E.

I was thinking I might have to add a new column to lookup, but I was going to put the totals in it rather than look up the highest number in a range - same thing really isn't it?
That's a lot of work though, so I'd prefer another option!

If there's a differentiating column, say, the month or something in another column then you could subtotal any other column.

I don't understand what you mean? All the columns have headers, and the column I need is already totaled - just not totaled on the same row as the item name that I'm looking up. If I move the totals to the same row as the item name, I think that's the same solution as murf's?
(Hope that makes sense....?)



Think I should have planned my sheet a bit better from the start!?

[Surprised]

If the total is always in column D then vlookup will work if the corresponding entry in column A is unique.
Ie A5 nay be "Item A total" with the figure you want in D5
A21 may be "Item B total" with the actual total in D21 and so on

[Jester]

If the total is always in column D then vlookup will work if the corresponding entry in column A is unique.
Ie A5 nay be "Item A total" with the figure you want in D5
A21 may be "Item B total" with the actual total in D21 and so on

So I need an entry in Column A in the same row as the total I'm looking up?
I was hoping there was a way of referencing the total to the item name, but wasn't hopeful considering there are variable numbers of row for each item.

A further problem I have is that the sheet the lookup value needs to be added to is not mine and the items are already listed. Therefore names are not 'Item A total' etc but 'Item A'
I think I will need to create new intermediate lookup sheets to get around this?

[Rob]

It is possible but would be quite an involved formula. I'd be able to provide an example this evening if still required.

Is it just the item decriptions in column A? As per your example, would the cells A2 to A5 be blank?

[Jester]

It is possible but would be quite an involved formula. I'd be able to provide an example this evening if still required.

Is it just the item decriptions in column A? As per your example, would the cells A2 to A5 be blank?

Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)

It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!

[Mystery]

Can you post a screen shot. It sounds achievable but don't totally understand the question.

[Rob]

Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)

It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!

Based on the above info it can be achieved using the offset and match functions.

If the data is in Sheet1 and you have the decriptions Item 1, Item 2, Item 3, etc in cells B3, B4, B5, etc of another sheet then the formula to pull though Item 1's formula would be:
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
Item 2's formula:
=OFFSET(Sheet1!$A$1,MATCH(B5,Sheet2!A:A,0)-MATCH($B$3,Sheet2!A:A,0)-2,3)

Hope that makes sense.

[Surprised]

Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)

It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!

Based on the above info it can be achieved using the offset and match functions.

If the data is in Sheet1 and you have the decriptions Item 1, Item 2, Item 3, etc in cells B3, B4, B5, etc of another sheet then the formula to pull though Item 1's formula would be:
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
Item 2's formula:
=OFFSET(Sheet1!$A$1,MATCH(B5,Sheet2!A:A,0)-MATCH($B$3,Sheet2!A:A,0)-2,3)

Hope that makes sense.

OFFSET never even occurred to me
Clever solution.

[Jester]

Can you post a screen shot. It sounds achievable but don't totally understand the question.

Can't do that I'm afraid - sensitive information you know!!

Yes, just the description in col A - all other rows are empty.
The totals are also the only used cell in their particular rows, and there is a completely blank row between each item (not that this will make much difference I guess?)

It seems to me the problem is complicated by the fact there are different numbers of rows for each item. If that's the case, I can make them all the same using blank rows. That would make the sheet somewhat untidy but I could live with that if it helps? Maybe there is a quick way to do this too...?!!

Based on the above info it can be achieved using the offset and match functions.

If the data is in Sheet1 and you have the decriptions Item 1, Item 2, Item 3, etc in cells B3, B4, B5, etc of another sheet then the formula to pull though Item 1's formula would be:
=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)
Item 2's formula:
=OFFSET(Sheet1!$A$1,MATCH(B5,Sheet2!A:A,0)-MATCH($B$3,Sheet2!A:A,0)-2,3)

Hope that makes sense.

No sense whatsoever!

I'm tied up with something else now but will look at it as soon as I get a chance.
Thanks so much for trying to help.
I'll let you know when I've given it a go (and probably failed to get it to work!)

[Jester]

I finally got around to trying this and have to say I've got nowhere....?!

=OFFSET(Sheet1!$A$1,MATCH(B4,Sheet1!A:A,0)-MATCH($B$3,Sheet1!A:A,0)-2,3)


I can get to the column I need by changing the 3 at the end to 14 (the total is in col O), but I can't get the right row.
I can't see anything else other than the '-2' that can be a variable. If I change that I get somewhat lost as to what the result refers to.... What does the '-2' do