Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 11 of 11 Posts

·
Registered
Joined
·
83 Posts
Discussion Starter · #1 ·
I have 3 columns, say : company, rate, time

can I assign a rate to a company so that every time I enter a particular company in the cell then the appropriate rate appears in the next cell?

I tried putting a wee table on another sheet but can't seem to make any references to it work. I could be way off on how to do it but I'm just stumbling along trying stuff.

Any ideas? cheers in advance

Stu
 

·
Registered
Joined
·
11,755 Posts
Sure.
Make yourself a table on another worksheet (you can even hide that worksheet later). So, suppose you have on Sheet 2:

A1 through A6 contain company names
B2 through B6 contain their rates

Select A1 through A6.
Hit Insert-Name-Define and type "CoName"
Select A1 through B6.
Hit Insert-Name-Define and type "RateChart"

Now, go back to your main sheet.

Suppose your company names are in column C.
Select C1 through CXXX (whatever).
Hit Data-Validation, choose LIST.
In the box below, type exactly this:
=CoName
That gives you a dropdown to choose the company name.

Now, in the D, where you want the rate to appear for the company chosen, type the following formula (for row 2):

=vlookup(c2,ratechart,2,1)

:D

See more on how to make dropdowns:
http://www.theofficeexperts.com/excel.htm#DropdownLists

See more on VLOOKUP:
http://www.theofficeexperts.com/excel.htm#TheDreadedVlookup

Hope it helps!
 

·
Registered
Joined
·
252 Posts
Hey there couldn't you can use nested IF functions in each cell? Check out help for a better description than I can give here!

Rutter
 

·
Registered
Joined
·
1,829 Posts
Sounds like you want a VLOOKUP.

Say you have your table of values set up in Sheet2 with the column titles Company, Rate and Time in cells A1, A2 and A3.
For the sake of this exampl you have 50 companies.

On Sheet1, you have a Company Column and a Rate column
In the first cell under Rate (B2) enter:

=VLOOKUP(A2, Sheet2!A1:A51, 2, FALSE)

Replicate this formula down as required.

For more info check out VLOOKUP in Excel's Help.

Gram
 

·
Registered
Joined
·
83 Posts
Discussion Starter · #6 ·
superb! thanks for all the great help

I'll go for the VLOOKUP option, because thats what I had in my mind.

Maybe I'll upgrade it to the drop downs later as it's a lot better but I'm going for easy options now lol

thanks guys (and girls)
 

·
Registered
Joined
·
331 Posts
Originally posted by Dreamboat:
Sure.

Suppose your company names are in column C.
Select C1 through CXXX (whatever).
Hit Data-Validation, choose LIST.
In the box below, type exactly this:
=CoName
That gives you a dropdown to choose the company name.

See more on how to make dropdowns:
http://www.theofficeexperts.com/excel.htm#DropdownLists

See more on VLOOKUP:
http://www.theofficeexperts.com/excel.htm#TheDreadedVlookup

Hope it helps!
Anne,

Actually you can just use "rate chart" for the data validation part. No need to name two ranges. Use List and in the Source window enter,

=INDEX(ratechart,0,1)

:D
 

·
Registered
Joined
·
83 Posts
Discussion Starter · #9 ·
just put it into my excel and it's done exactly what i wanted.
Thanks again

It's for the World Food Program so you can all say you helped starving people in Zimbabwe :)
 
1 - 11 of 11 Posts
Status
Not open for further replies.
Top