Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reminder notifications in a column | Excel Discussion (Misc queries) | |||
how do i get mutiple values using vlookup in excel, lookup value . | Excel Discussion (Misc queries) | |||
Using a Vlookup to return values in a data list? | Excel Worksheet Functions | |||
Copy values from Sheet1 to Sheet2 | Excel Discussion (Misc queries) | |||
VLOOKUP not working | Excel Worksheet Functions |