Associated Values in Excel

[matpj]

Hi,
I need help in completeing a spreadsheet.

Firstly I need to know if I can use a written field to reflect a numeric field - ie can I type in a word and it will automatically recognise this and attach a predefined number to another part of the spreadsheet? If so how?

Assuming this can be done, is it possible to go one stage further by associating a list of values with a given text string?

e.g.

when a text string is entered, four different numeric fields are populated

Can this be done using a table of the values and 'looking-up' the values associated to the given text?

Any help would be gratefully received,

thanks,

Matt

 

[bandit600]

How many possible variations are there to the text string?

Is there any logic that defines how the 4 cells would be populated?

bandit600

 

[matpj]

There could be a number of variations.

Basically they are prices that go with different packages we offer.

Lets say if I enter Package1 in cell A1
I want Excel to look up the value £39.95 from somewhere and populate cell A2 with that amount.


as for my second point, if possible I want Excel to look up 4 prices which make up the cost mentioned above.
does this make sense?

 

[striker83716]

You can do what you describe with the "lookup" formula, on worksheet 2 enter your package names and prices ie.

Sheet2
Cell A1="Package 1", B1="30"
Cell A2="Package 2", B2="60"

Then on Sheet 1

Cell B1 would be "=LOOKUP(A1,Sheet2!A:A,Sheet2!B:B)".

When you enter the package name in cell A1, Cell B1 will then display the price listed to the right of the package name on Sheet 2

Goodluck

 

[Dreamboat]

Hi matpj.

In both MyVlookup and MyInvoicing files, you'll find ways to do exactly what you want. You can download them for free at

http://www.theofficeexperts.com/downloads.htm

Anne Troy

http://www.TheOfficeExperts.com