Copy VLookup Values
 

Excel 2003
Someone set up a Lookup formula for me in one row of my spreadsheet. Now I
have to apply that formula to approximately 100 rows in each tab and 30 sheet
tabs. How do I do this? You can't just copy and paste. When I do I get the
infamous error #N/A. How else can this be done?

Thank you

Hi Karen,

I think you perfectly CAN copy/paste the formula - just pay attention to the
relative vs absolute references in it. The #N/A error indicates that the
value you are looking for has not been found in the lookup table and that
may be for a thousand of different reasons. Could you please post the
formula you need to copy and give examples of data (the searched value and
the lookup table). Provided that you need to copy the formula to the same
location and same refernces on all pages you should be able to do it on all
sheets at once by selecting all sheet tabs (holding Shift key) and then
pasting the formula on a sheet.

Regards,
KL


"Karen" wrote in message
...
Excel 2003
Someone set up a Lookup formula for me in one row of my spreadsheet. Now
I
have to apply that formula to approximately 100 rows in each tab and 30
sheet
tabs. How do I do this? You can't just copy and paste. When I do I get
the
infamous error #N/A. How else can this be done?

Thank you

Thank you for your help
Here you go:

=IF(AND(J54.7,J5<9.5),IF(I5=VLOOKUP(J5,SPLIT1,2, 0),1,""),"")
=IF(AND(J59.4,J5<14.3),IF(I5=VLOOKUP(J5,SPLIT2,2 ,0),1,""),"")
=IF(AND(J52.4,J5<3),IF(I5=VLOOKUP(J5,VAR,2,0),1, ""),"")


"KL" wrote:

Hi Karen,

I think you perfectly CAN copy/paste the formula - just pay attention to the
relative vs absolute references in it. The #N/A error indicates that the
value you are looking for has not been found in the lookup table and that
may be for a thousand of different reasons. Could you please post the
formula you need to copy and give examples of data (the searched value and
the lookup table). Provided that you need to copy the formula to the same
location and same refernces on all pages you should be able to do it on all
sheets at once by selecting all sheet tabs (holding Shift key) and then
pasting the formula on a sheet.

Regards,
KL


"Karen" wrote in message
...
Excel 2003
Someone set up a Lookup formula for me in one row of my spreadsheet. Now
I
have to apply that formula to approximately 100 rows in each tab and 30
sheet
tabs. How do I do this? You can't just copy and paste. When I do I get
the
infamous error #N/A. How else can this be done?

Thank you

Hi Karen

These formulae look like they can be copied no problem. I guess what you
maight want to check is:

1) if the named ranges are absolute or relative both sheet- and cell-wise
2) if the cell J5 in other sheets contains values that are available in your
lookup table
3) actually, you are telling VLOOKUP to scan for exact matches of the J5 (by
having the 4th argument set to 0) is that what you really want?
4) when you have copied the formulae and if they still give errors, you can
debug them yourself by selecting the cell with formula, going to the formula
bar, selecting each reference or formula block and press F9 (this will
return the values in specific range or results of specific blocks).

Regards,
KL


"Karen" wrote in message
...
Thank you for your help
Here you go:

=IF(AND(J54.7,J5<9.5),IF(I5=VLOOKUP(J5,SPLIT1,2, 0),1,""),"")
=IF(AND(J59.4,J5<14.3),IF(I5=VLOOKUP(J5,SPLIT2,2 ,0),1,""),"")
=IF(AND(J52.4,J5<3),IF(I5=VLOOKUP(J5,VAR,2,0),1, ""),"")


"KL" wrote:

Hi Karen,

I think you perfectly CAN copy/paste the formula - just pay attention to
the
relative vs absolute references in it. The #N/A error indicates that the
value you are looking for has not been found in the lookup table and that
may be for a thousand of different reasons. Could you please post the
formula you need to copy and give examples of data (the searched value
and
the lookup table). Provided that you need to copy the formula to the same
location and same refernces on all pages you should be able to do it on
all
sheets at once by selecting all sheet tabs (holding Shift key) and then
pasting the formula on a sheet.

Regards,
KL


"Karen" wrote in message
...
Excel 2003
Someone set up a Lookup formula for me in one row of my spreadsheet.
Now
I
have to apply that formula to approximately 100 rows in each tab and 30
sheet
tabs. How do I do this? You can't just copy and paste. When I do I
get
the
infamous error #N/A. How else can this be done?

Thank you