IF function

[darkerskies]

Hi

I'm currently working on a spreadsheet to make stationery ordering in the office easier to track. I've been using a validated drop-down list for the items in order to keep consistency. However each cell in the item column needs to generate a designated nominal code depending on what category the item is under. I thought the best way to do this was to use an IF statement. I tried the following =IF(B6=N3:N15,"8350","NC") but it didn't work. Is this because N3:N15 means it is trying to equal B6 to all cells in the range? And if so, how could I make it so that the cell (B6) is equal to any in that range of cells? (N3:N15)

I should add that Column B is where the validated list is and Column N is where it is accessing the data from. The formula is being entered into a third column, Column E.

Can anyone help me out on this?

Cheers

[Andy Pope]

Sounds like you should be using a Lookup function rather than IF.

You would lookup the value returned from the dropdown list in a table.
The table would have the items available in the dropdown as 1 column and the Category to which it belongs.

[darkerskies]

Thanks Andy.

I tried your suggestion but I couldn't make it work. I'm really quite perplexed. I am, admittedly in over my head, but I need to make this work.

I'm not sure how well i explained it, so i'm going to try again. There is a drop down list in column C which allows users to select one from a range of items in column N. What needs to happen next, but which I seem incapable of doing is that when someone selects an item from N3:N17 a value of "8150" is automatically input into cell D. N18:N37 should receive a value of "7050" and N38:N95 should receive a value of "1981". These values are fixed and will not change.

Basically it's important that the value of each item is automatically generated somewhere within the sheet, otherwise the information collected by the sheet will be incorrect and thus a bit pointless. We're trying to override human error and laziness!

Um, if it's still about the LOOKUP function, then if you could explain it to me a bit more clearly I would be really, really grateful. I couldnt even make the data table.

Sorry to be a nuisance.

Thanks

Erin.

[Andy Pope]

The lookup formula would use the value in column C to locate the same value in N3:N17 and then return a value to the right of column N.

So in this example change the contents for C1 and D1 will display the information from column O