Excel 2007 populating a cell based on values in other cells

[pbsj]

I am working on an expense report and each type of the expense has a different code. I would like to have the Code cell populate with a value based on which cell they enter the amount in. For example, if a value was entered in cell B1 for Hotel, then the value in the Code cell A1 would be "001". If a value was entered in cell C1 for "Mileage", then the value in cell A1 would be "002". There will only be one value in either B1 or C1.

Is it possible for A1 to change based on the value in more than one cell? I have tried the embedded "IF" statements but cannot seem to make it work.

Thanks

[SteveG]

Hi,

How are you determining the code? Are you using a vlookup or is it hard coded in the formula?

Steve

[pbsj]

Hi Steve,

It is hardcoded in the formula. There are seven of them:

B1 = 001
C1 = 002
D1 = 003
E1 = 004
F1 = 005
G1 = 006
H1 = 007

The values in B1 through H1 can change but the value in A1 is hard coded based on the fact that there is a value in the cell B1 through H1. The IF statement that covers one cell is "=IF(B1836,"001","")". I need this type of formula to work for multiple cells.

Thanks!

[SteveG]

Hi,

Can you post a worksheet with dummy data showing the before and after?

Thanks,
Steve

[pbsj]

Please see the attached dummy data spreadsheet with the before and after. Note that when data is entered into the "Hotel (001)" column, the value in the "Code" column is 001.

[Pauleyb]

In cell A1, does =B1&C1&D1&E1&F1&G1&H1 work?

Pauley

[Pauleyb]

In cell B1:
=OFFSET($A$1,MATCH(SUM(B2:B5),B2:B5),0)

Then copy across to E1.

Pauley

[pbsj]

Unfortunately, this is not what I need. I need the Code column to be populated only if there is a value in that row. If you do not have a value in cell C3, 002 will not appear in cell A3. Similarly, if there is no value in cell E4, there will not be a value 003 in A4. Rows 3 and 4 would not have anything in the Code column (column A), since there is nothing entered in the other columns on that row. If you entered a value in E3, A3 would be 004 but if a value was entered in D3 instead, A3 would be 003.

Thanks for your help!

[Pauleyb]

Put this in cell B1 and then copy down.

="00"&MATCH(SUM(B2:E2),B2:E2)

Pauley