I've just started using Excel after about six years or so and am struggling to remember much of it....


Today I have the following problem to solve:





I want to record when I run out of donuts on another worksheet, in this case wk4

I have lots of other items, not just donuts, so I need to lookup donuts, and report the week stocks go negative on the other sheet.


Hope that makes sense?

If anyone can help I would be most apreciative!

Probably a combination of a couple of functions.
I'll have a look after lunch.

Can you do it using the same grid - if so each square on the second sheet just needs a

IF ([other sheet]A1<0,[other sheet]A1,"")

then most squares will be blank and your -ves will appear

Or you could use a Conditional Format

To Highlight in Red when stock goes below a certain figure

This would be better than waiting until it was ZERO

I'm already using conditional formating to warn me when stocks are low.

It's not a similar grid on the second worksheet, it's a summary sheet so just one column reporting the date we run out of stock. I could create another (working) sheet but I think I would still have the same problem as now?

Apologies for being slow to reply but I'm currently going through the sheet manually, and it's buggeringly slow!!!


Anyone good with SAP?!!

Could you use a working sheet to 'sort' all your data in order (lowest first) then your proper sheet 2 could just read the top of that sorted list (using IFs you could avoid showing +ves)

Index with Match function will work
ie
=INDEX(weeks,MATCH(1,INDEX(--(donuts<=0),0),0))

you will need to name the ranges but it will work then for any number of items
I'll post details a bit later if needed

Aha! Sounds interesting.
I've never worked with Index or Match, so might need to be spoon fed here....
I'll have a quick Google to see what I can learn.

I will give details in a few mins

So let's assume these are in cells A3 to F5

You will need to name the ranges for as many product lines as you have and a range for the weeks
So in this example I have named ranges as follows
weeks b3 to f3
donuts b4 to f4
pies b5 to f5

So,in a new sheet put this for instance in cells a2 and a3
donuts
pies

In b2 put this formula =INDEX(weeks,MATCH(1,INDEX(--(donuts<=0),0),0))
in cell b3 put this formula =INDEX(weeks,MATCH(1,INDEX(--(pies<=0),0),0))

Then it will tell you the first week that the stock goes to zero or lower

I can send you the sample spreadsheet if you like.

I'm trying it... Might be a while!




It works!

I need to finish the manual entry stuff I'm doing right now, then I can try to apply this to the actual workbook.

I'll let you know how I get on - thanks for taking the time to help!

I've had a quick play and it looks like just what I needed!

Thanks FISO and thanks Surprised!



No doubt I will be back again with another one soon....?!!

Your welcome

Question 2 on functions.....


VLOOKUP in fact.

All I want to do is copy a vlookup formula across several columns with just the column index increasing by one in each subsequent column.

ie. =VLOOKUP($B4,[Workbook1.xls]Sheet1!$A$1:$BA$100,12,FALSE)

So the first result would be the value from column 12 of the table array, the second column 13 and so on.
The lookup value and table arrary are absolute, hence the Dollar signs. Dragging the formula across multiple columns however, doesn't see the column index number increasing.



It must be easy to do - at the moment I'm editing the formula in each column manually....


Cheers for any help!

an easy way is to replace the '12' with a cell reference (eg c5). Put 12 in C5, 13 in d5, 14 in e5 etc etc). Copy the formula across and bobs yer.

=VLOOKUP($B4,[Workbook1.xls]Sheet1!$A$1:$BA$100,C$5,FALSE), where C5 = 12, D5 = 13 etc

Brilliant unc - thanks!


I've been trying to get

=VLOOKUP($A4,Workbook1!$A$1:$F$200,COLUMNS($B4:B4)+1,FALSE)

to work without success.

Your method I understand though. Nice one

It's not a particularly technical or clever solution, but its quick, easy to explain and it works

Yours should work as well though, although surely it should be +11, not +1?

edit: i.e. =VLOOKUP($A4,Workbook1!$A$1:$F$200,COLUMNS($B4:B4)+11,FALSE), assuming your data starts in column 12

You're right - I understand that formula as well now.
I didn't realise why it was +1......

Thanks again!

The best way is probably to have the number replaced by a match function and the relevant column heading on the database replicated above the answer sheet. The match looks for how many columns along the database the answer sheet title is Has the benefit that if you add columns into your database it still picks the right one up. On iPhone so cannot be more explicit - exercise left to reader!

I'm going to have to read up on this index/match function as it seems it would be ideal in much of what I struggle to do with vlookup?

I used it as suggested by Surprised above but didn't fully understand how it works...

It's like using vlookup or hlookup but when you don't know which column or row the item you want is located.
But that doesn't really explain

I'll have a play tomorrow. I think the named ranges confused me a bit as I don't often use them, but you don't have to name ranges do you - as with vlookup, it just makes things a bit more tidy?

There is no need to name ranges.
I never used to bother but it does make it a lot easier to keep track of things.

Ie if you see $A$1:$D$20 in a formula it means nothing but if you see "donuts' in a formula you will know what cells it refers to and why.

But named ranges don't copy; when you copy the formula it is still donuts, which is tedious. Avoid them.

It will work if the named range is referenced in a cell rather than hard coded in the formula.

It's really just an alternative to using absolute cell references

If named ranges are referenced in a cell rather than hard coded, don't you just have to reference those named ramges in the way you would the range you wnated anyway, so you've gone to some trouble but for no advantage?

Not always. Say you have a range called 'donuts' then you reference the cell with the name 'donuts' in it. Whenever you add rows or columns to the named range it then changes.

Question 3:

I can remember doing something like this during my training around 20 years ago.....
Bit of a block now though!



I want to know when to order each item.
Assuming current stock is the same as lot size in this instance, therefore I will run out of item A in wk3 and therefore need to order more of it.

It's a SUM function isn't it - If the sum of C3 to E3 is greater than or equal to lot size, place an order.
It's calculating the next order point that is really testing me, I need to add the surplus from wk3 to wk 4 etc for the next order point
ie. In the example at the end of wk 3 I will have used 120 of Item A therefore:

(100-(20+50+50))+100 = 80 remaining; => 80+150=230, so need to order 2xlot size in wk4 and so on....

Hope it's not too difficult?!

It's not just a Sum or SUMIF statement as the reorder can come in any week. It's probably a combination of SUM, IF and OR.
So with product A it will check is week 1 more than 100 then reorder. If not then is week 1+week 2 more than 100. And so on.

I will have a look over the weekend.

I like to simplify things as much as I can, rather than have complex formulas that a colleague can't understand if something goes wrong; I break things down by hiding rows/columns use seperate sheet for workings out etc.

In this example you would have to change the week number at the top (or use auto dates), fill in the estimated usage for a forward period then fill in the actual usage on a weekly basis. The Hlookup finds the right week to work out the maths and the conditional format turns the cell red for you.

Maybe too simplistic a solution for you because you are simpifying the problem for post purposes.