C
CAT
Hi everyone, hope someone out there can help me with this one, a bit long!
I'm a fairly new user and using Excell 2007.
Outside the main body of my worksheet I have set a LookUp table, with 2
columns starting with cell AM1 and finishing with cell AN29, with precise
values that will be needed in the spreadsheet:
Col AM Col An
20 10
18 9
16 8.50
14 7.50
12 6.50
11 6.00
10 5.50
..... .... etc. etc., to
2.00 1.25
1.88 1.20
1.75 1.00
1.63 0.91
1.50 0,83 in cell An29, this being the last value of the series.
(A total of 29 values that are not all listed here).
In my worksheet, the two columns involved are:
Col G - Manual data entry
Entering randomly one value per cell out of the list
in col AM of the LookUp table (or none)
Col R - Calculated data
Returning the equivalent value in col AN of the LookUp table (or none)
*What I do - Row 7 : First entry
In col R, cell R7, I enter the following function:
=VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary)
and copy it right down the column (up to 400 entries)
Then, for each new entry that I enter (manually) in each cell of col G, I
modify the formula, in the same row in col R, to match my entry, in other
words I delete the "8" and replace it with the figure I have just typed in:
Example:
Cell G7 : type in 16,
Cell R7 : delete the 8 and replace by 16 to read:
=VLOOKUP(16,$AM$1:$AN$29,2,FALSE)
(which will give me the value 8.50 - col N of LookUp table)
and so on (for up to 400 cells) until I have completed the entries; in the
case of any blank cell in col G, I delete the formula in col R.
The whole process is really time consuming and I make quite a few errors.
*What I would like to achieve:
Have a function (in column R?) that would automatically return, in the
equivalent cell in col R, the relevant value that is listed in the column AN
of the LookUp table, everytime a new entry is inserted in any of the cells of
column G, but giving a blank when there are no equivalents.
Sorry this is so longwinded, but I wanted to present this as clearly as
possible so as not to confuse the issue in order to get an answer!
Thank you for any help, as it will be much appreciated
I'm a fairly new user and using Excell 2007.
Outside the main body of my worksheet I have set a LookUp table, with 2
columns starting with cell AM1 and finishing with cell AN29, with precise
values that will be needed in the spreadsheet:
Col AM Col An
20 10
18 9
16 8.50
14 7.50
12 6.50
11 6.00
10 5.50
..... .... etc. etc., to
2.00 1.25
1.88 1.20
1.75 1.00
1.63 0.91
1.50 0,83 in cell An29, this being the last value of the series.
(A total of 29 values that are not all listed here).
In my worksheet, the two columns involved are:
Col G - Manual data entry
Entering randomly one value per cell out of the list
in col AM of the LookUp table (or none)
Col R - Calculated data
Returning the equivalent value in col AN of the LookUp table (or none)
*What I do - Row 7 : First entry
In col R, cell R7, I enter the following function:
=VLOOKUP(8,$AM$1:$AN$29,2,FALSE) (the figure "8" being temporary)
and copy it right down the column (up to 400 entries)
Then, for each new entry that I enter (manually) in each cell of col G, I
modify the formula, in the same row in col R, to match my entry, in other
words I delete the "8" and replace it with the figure I have just typed in:
Example:
Cell G7 : type in 16,
Cell R7 : delete the 8 and replace by 16 to read:
=VLOOKUP(16,$AM$1:$AN$29,2,FALSE)
(which will give me the value 8.50 - col N of LookUp table)
and so on (for up to 400 cells) until I have completed the entries; in the
case of any blank cell in col G, I delete the formula in col R.
The whole process is really time consuming and I make quite a few errors.
*What I would like to achieve:
Have a function (in column R?) that would automatically return, in the
equivalent cell in col R, the relevant value that is listed in the column AN
of the LookUp table, everytime a new entry is inserted in any of the cells of
column G, but giving a blank when there are no equivalents.
Sorry this is so longwinded, but I wanted to present this as clearly as
possible so as not to confuse the issue in order to get an answer!
Thank you for any help, as it will be much appreciated
