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

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.

Stu

Anne Troy

Anne
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)

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

See more on VLOOKUP:

Hope it helps!

Rutter

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

Gram123

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.

Gram

Anne Troy

Anne
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.

Gram: You have to be quicker. LOL

arrrgh2003

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)

maxflia10

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:

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)

Anne Troy

Anne
Very nice, Max.
I didn't know that, and never use the INDEX function, LOL!

arrrgh2003

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

Anne Troy

Anne
Cool beans. Let us know if we can help more.

arrrgh2003

haha, i might hold you to that! lol

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.

As Seen On