Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy down to all Vlookup "#N/A" cells ONLY (in the same column)
Hi,
Hope someone can help me with a code for this.. Everyday in my data worksheet I have to Vlookup to spreadsheet (contain different formats) from 6 different respondents for various values or answers with reference to my colum of spares PartNumbers. After the Vlookup, the column will have formula cells populated wit values and "#N/A". My current practice is I copy this whole column o Vlook-up results, click Edit/Paste-Special and paste them as Values. have to do this because subsequently I will use Auto-Filter in that column for only the "#N/A" and start anothe round of Vlookup the "#N/A" cells to the 2nd respondent's worksheet.. and continue with same steps above until the 6th respondent' worksheet. If I don't use Paste-Special/Paste-as-values, the steps alternatin between Auto-Filtering and Unfiltering the column will take very lon because the column is still having Vlookup formulas tie to the different worksheets. My data sheet span about 6000 rows make it eve slower.. Using an example below, the Qty-On-Hand column is where I Vlookup th results from another worksheet. I do not want to us Copy/Paste-as-values but banking on hope for a code that can allow Vlookup on ONLY the #N/A cells. ie. when I type in anothe Vlookup formula in the first "#N/A" (example here is in row 4), th code should only execute to copy the Vlookup formula down to the rest of the other "N/A" cells in the same column skipping those that are not "#N/A". How can this be done ? Any help on this is very much appreciated. PartNumbers Qty-On-Hand 1445A 2 1122F 0 R1261Z #N/A 2265B 112 1000A #N/A 1000C #N/A 1002A 9 1000C #N/A 2880A 7 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy down to all Vlookup "#N/A" cells ONLY (in the same column)
select column b and do
Edit=goto=special and select formulas and errors This will select all the cells showing #N/A. Now you can enter a new formula using relative references and entered according to the active cell. then finish with Ctrl+enter rather than just enter and this will fill all the selected cells. -- Regards, Tom Ogilvy alienscript wrote in message ... Hi, Hope someone can help me with a code for this.. Everyday in my data worksheet I have to Vlookup to spreadsheets (contain different formats) from 6 different respondents for various values or answers with reference to my column of spares PartNumbers. After the Vlookup, the column will have formula cells populated with values and "#N/A". My current practice is I copy this whole column of Vlook-up results, click Edit/Paste-Special and paste them as Values. I have to do this because subsequently I will use Auto-Filter in that column for only the "#N/A" and start another round of Vlookup the "#N/A" cells to the 2nd respondent's worksheet... and continue with same steps above until the 6th respondent's worksheet. If I don't use Paste-Special/Paste-as-values, the steps alternating between Auto-Filtering and Unfiltering the column will take very long because the column is still having Vlookup formulas tie to the 6 different worksheets. My data sheet span about 6000 rows make it even slower.. Using an example below, the Qty-On-Hand column is where I Vlookup the results from another worksheet. I do not want to use Copy/Paste-as-values but banking on hope for a code that can allow Vlookup on ONLY the #N/A cells. ie. when I type in another Vlookup formula in the first "#N/A" (example here is in row 4), the code should only execute to copy the Vlookup formula down to the rest of the other "N/A" cells in the same column, skipping those that are not "#N/A". How can this be done ? Any help on this is very much appreciated. PartNumbers Qty-On-Hand 1445A 2 1122F 0 R1261Z #N/A 2265B 112 1000A #N/A 1000C #N/A 1002A 9 1000C #N/A 2880A 72 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a vlookup formula without changing the "table_array" p | Excel Worksheet Functions | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) | |||
Can I copy the formatting of a cell in a IF Fucntion "VLOOKUP"? | Excel Worksheet Functions |