Need a custom excel formula?

April 30, 2011 at 15:55:20
Specs: Windows Vista
I have created a spreadsheet in excel. In column B I have a drop-down list of clients. Column C is for a percentage associated with a particular client. (percentage varies by client) I would like to have the percentage associated with each client in the drop-down menu be entered automatically when that client is selected in Column B. Column C is also referenced in a formula ie. Col. C*Col E=Col F. Is there a way I can do this?
Thank you in advance for any assistance you can give me.

See More: Need a custom excel formula?

Report •

April 30, 2011 at 16:35:46
I assume you have a table someplace that has the percentage that is associated with each client.

If so, use a VLOOKUP function in Column C to return the percentage associated with each client.

e.g. =VLOOKUP(B1, SHEET2!$A$1:$B$10, 2, 0)

Read up on the VLOOKUP function in the Excel Help files.

If you need some help getting it to work, come on back. You'll probably have to supply a little more detail if you want us to actually construct the formula for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 2, 2011 at 20:31:43
Thank you for the answer, that was what I was looking for. I have one problem with it thought, when I try to copy it through the rest of the worksheet the table range numbers change ie I am trying to copy-

=VLOOKUP(B2,'service management'!B2:C101,2)
=VLOOKUP(B3,'service management'!B3:C102,2)
=VLOOKUP(B4,'service management'!B4:C103,2)

but in the following cells the range changes as well and I need it to stay the same with only the destination cell changing.

Report •

May 2, 2011 at 20:51:45
You really should read the Help files on Relative and Absolute addressing.

It's next to impossible to use Excel efficiently without understanding that concept.

=VLOOKUP(B2,'service management'!$B$2:$C$101,2)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

May 3, 2011 at 06:26:01
Thank You ,I will read those files.I have used the help section quite a bit but didn't know that was relevant to what I was doing. Thanks so much for the info and direction, it will be a great benefit.

Report •

Ask Question