Excel spread sheet formula

Closed
Omar - Apr 2, 2010 at 07:34 PM
 Omar - Apr 3, 2010 at 09:01 AM
Help with excel spread sheet please!
I have a table with about 50 different numerical values listed on them.
Examples
i T 1000 = 56.02
s V 1000 = 56.00
T 1000 = 54.96
i T, 1000 and 56.02 each appear in a single cell.

On another spread sheet I have i T 1000, S V 1000 and T 1000 and I need to type in the 56.02, 56.00, 54.96 etc. by looking up the data on the table.
Is it possible to input a formula so that I don't have to type and look up the answers?
I have thousands of these and I make mistakes when I get tired looking at the table while typing.

1 reply

sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Apr 3, 2010 at 06:02 AM
some more clarification.

is the iT, sV and T always 1000?

if so, you can do the following as all we need to do is concentrate on the Letters and not the 1000.

try the following

For this example.

=IF(A8="iT",56.02,IF(A8="sV",56,IF(A8="T",54.96)))

A8 will be the cell where you put the Letters (iT,sV or T)

The second cell ie B8 is irrelevant as this number stays the same ( from what i understand)

the formula should be placed in C8., Now what ever you put in A8 (iT,sV,T) the correct figure will appear in C8.

You can have a nest of up to 8 items.

Then copy the formula to all the cells below in the C column.

Hope that helps, Please get back if it does not do what it says on the tin and I will endeavor to help.
0
Hi Sharpman,
Thanks a million for the help so far. My apology for sending ample info. Below a bit more detail

I have a table in spread sheet 1 (a reference sheet providing all the answers).
A B C
1 I T 1200 68.65
2 I T 1450 85.88
3 T 1000 54.96
4 T 1160 65.12
5 I T 1600 94.38
6 V 1600 98.01
7 N T 1160 65.13
8 T 2000 120.13
9 S V 1800 109.32
10 C 2000 117.32

Then I have a second spread sheet see below, where I have to insert the correct answer into column three alongside the appropriate cell.


A B C
1 T 1000 ?
2 I T 1600 ?
3 I T 1200
4 I T 1000
5 T 1160
6 I T 1450
7 IT 1000
8 T 1000
9 S V 1800
10 T 1160
11 I T 1450
12 V 1600
13 C 2000
14 C 2000
15 T 1160
16 T 1000
17 S V 1800
18 I T 1450
19 V 1600
20 T 1160
21 C 2000
22 N T 1160

I need a formula that can transfer the answer from worksheet one to the appropriate cell in worksheet 2.
Alternatively I can copy the table onto worksheet 2 then I don't have to have hyperlinks, if it would simplify the formula?
Lastly please excuse my ignorance I have only a very understanding of excel.

Thanx
0
Thanks a million for your help so far,
My apology for not providing adequate info first the time.
I have a table in spread sheet 1 (a reference sheet providing all the answers).

A B C
1 I T 1200 68.65
2 I T 1450 85.88
3 T 1000 54.96
4 T 1160 65.12
5 I T 1600 94.38
6 V 1600 98.01
7 N T 1160 65.13
8 T 2000 120.13
9 S V 1800 109.32
10 C 2000 117.32

Then I have a second spread sheet see below, where I have to insert the correct answer into the appropriate cell under column 3

A B C
1 T 1000
2 I T 1600
3 I T 1200
4 I T 1000
5 T 1160
6 I T 1450
7 IT 1000
8 T 1000
9 S V 1800
10 T 1160
11 I T 1450
12 V 1600
13 C 2000
14 C 2000
15 T 1160
16 T 1000
17 S V 1800
18 I T 1450
19 V 1600
20 T 1160

I need a formula that can transfer the answer from worksheet one to the appropriate cell in worksheet 2. Alternatively I can copy the table from spread sheet 1 onto a vacant section on spread sheet 2 to eliminate the hyperlink of the formula ?

Thanx in advance.
0