Populating a cell based on a drop down box in a different cell

I have a drop down list (list of flyers: i.e. A4 handout) B3 and I want this to populate cell C3 with the number of stock (i.e 100). e.g. flyer 1 (in b3) once selected should result in 100 appearing in C3. I've created a table of data to read from but I also need C3 to contain "0" if B3 is left blank.

I have tried using the formula =if(Isnumber(match,A1,$X$1:$X$100,0)),Vlookup(A1,$X$1:$Y$100,2,False),"") as I saw this fixed a similar problem for someone else, but this is returning as invalid.

I've used the info from the below link and I've tried using nested IF statements. I've attempted to use VBA but I am a novice to it and I couldn't get it to correctly input my IF-THEN-ELSE statement.

http://www.excelforum.com/excel-gene...35#post3149135

Any help would be much appreciated.

x

Oh Sorry I edited those values to contain my data so I actually entered:

=if(Isnumber(match,B3,$L$33:$L$35,0)),Vlookup(B3,$L$33:$M$35,2,False),"")

The drop down box in B3 is text based and I need C3 to contain a number. I then want to copy this down a column so it would end up (following selections from the list) looking like:

B3 C3
Flyer 1 100
Flyer 2 200
Flyer 1 100
Flyer 3 400

I can't upload the actual data.

Hi,

Thanks for responding

Kind of - attached is what i'm looking at (with sensitive data removed and with no drop down boxes in B) so its basically a way of checking stock.

I can do the simple formula that figures out the difference but I'm having difficulty populating the C column from the selection in the B column.

Thank you for this but this still has the same problem as earlier where if nothing is in the cell its gives out "#N/A" and I want it to read out "0"

Thank you so much!!!

In conjunction with this I don't suppose you would know how to formula so its kept updated? so in my slightly altered example (not using your code to keep it basic) the number of stock changes as a result of flyers being used up. But flyer 1 might not be used again for ages (i.e. isn't the cell below). Is there a way to use a formula to check the most up to date number of Flyer 1 and use this number as the number entered in B4 for example.

Thats kinda my question.

The most up to date value would be from the most recent event. I am making this spreadsheet for a shared drive so I will not always be the person updating it. This means I am entering the formula but leaving it blank so that when these events occur other administrators can enter the info in. This means the options chosen on each row will be blank and when someone else uses the spreadsheet they may select different options

In the example, flyer 1 is used at events 1, 4 and 5 but not at events 2, 3 or 6. To get the most up to date number of stock for Flyer 1 you would have to deduct the number used at event 1. e.g now only 90 remain. Thus I want want 90 to appear in C5 but I don't just want to automatically populate this cell from E2 as this may not be the option that was put it by someone else.

I want to tell Excel to search the sheet for the last use of Flyer one and input that number (minus the stated deduction) in C4. As these are drop down boxes in my actual sheet I want to input a formula that tells excel that to check the sheet for previous usage of this flyer and if no usage is present, populate from the table in L-M, otherwise populate from the stock remaining.

Hope that makes sense

Thanks but this isn't exactly what I need.

No Problem you solved my first issue so Thanks