excel help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
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
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
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!
 
Joined
Dec 17, 2001
Messages
252
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
 
Joined
Mar 15, 2001
Messages
1,829
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
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
OH. PS: You don't have to make that dropdown, but if you make a typo on the company name, it won't find the rate. :D

Gram: You have to be quicker. LOL
 

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
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)
 
Joined
Feb 24, 2003
Messages
331
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
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Very nice, Max. :D
I didn't know that, and never use the INDEX function, LOL!
 

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
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 :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top