Using the if command

**ravihotwok** · 05-01-2009, 05:00 AM

Hello ther excel guru's

I have a job monitoring spreadsheet im creating, I have a cell titled Technician and another with rate. There are 4 technicians with different hourly rates. Haw can I have the rate cell autonatically poulate the rate value which corresponds with the Technician? I have another sheet called data which I have got a table with the values on for reference.

Any help would be great

**sweep** · 05-01-2009, 05:02 AM

Bore da!

You can populate the rate cell using lookup or index and match. Would you be able to post a sample of data?

**ravihotwok** · 05-01-2009, 05:40 AM

bore da lol

havnt a clue how to do that, hope fully this will help.

On the data sheel there is a column titled technician with a list of four people. On the next column it is titled rate and then the correct values are illustrated adjacent the relevent technician. On another sheet called project sheet it is here I want one cell which has a drop down list for the techicians to determine what value goes into the rate cell

im crap at explaining stuff like this.

please explain if you can in laymans terms lol

**sweep** · 05-01-2009, 05:51 AM

OK,

The following presumes that the technicians are named in the range A1:A4 on Sheet 1, and that your dropdown list is in A1 on Sheet 2.

Enter this in B1 of Sheet2

=INDEX(Sheet1!B1:B4,MATCH(Sheet2!A1,Sheet1!A1:A4,0))

The Match part looks for Sheet2!A1 in Sheet1!A1:A4 and tells the Index part which row in Sheet1!B1:B4 to return.

**ravihotwok** · 05-01-2009, 06:09 AM

tried to paste that into the cell but it prompts a dialog boxing asking to save values to a sheet??

**sweep** · 05-01-2009, 06:12 AM

You'll have to modify it to your own sheet names and ranges.

**ravihotwok** · 05-01-2009, 06:29 AM

note sure what ive done, here is my ammendments
=INDEX(Data!C2:C5,MATCH(Blank Project Sheet!F24,Data!B2:B5,0))

it is saying #name?

**sweep** · 05-01-2009, 07:09 AM

Try this:

=INDEX(Data!C2:C5,MATCH('Blank Project Sheet'!F24,Data!B2:B5,0))

If that doesn't work, can you upload your file?

Press the "Go Advanced" button, and then "manage attachments" on the next page

**ravihotwok** · 05-01-2009, 07:19 AM

you bang on the money dave, spot on!!
is there a way to leave the cell blank when not in use instead of displaying #n/a

**sweep** · 05-01-2009, 07:47 AM

Yes,

You can either use conditional formatting to colour errors white, or modify the formula

=IF(ISNUMBER(MATCH('Blank Project Sheet'!F24,Data!B2:B5,0)),INDEX(Data!C2:C5,MATCH('Blank Project Sheet'!F24,Data!B2:B5,0)),"")