A
Alan
Hi. I'm having trouble using a pasted cell result in as an input to a
function. Working in SHEET1, I performed a VLOOKUP to extract the
value of the cell from SHEET2. I then filtered out rows that were not
relevant using AUTOFILTER, cut the resulting data and pasted into a
new SHEET3. I've then performed a further VLOOKUP, using the results
of the orginal lookup as the input cell reference. The 2nd LOOKUP
function is reporting an error code (#N/A) when I know the reference
is present in the lookup array, and that this array is sorted
appropriately.
The problem is solved by clicking into the equation line, or F2 and
then pressing enter which makes Excel treat the cell as a number
rather than text field. The fact that the number becomes right hand
justified when clicking in this way demonstrates this.
However, I have 15,000 plus records to correct and regularly come up
against this problem... so any ideas on how to create a macro (or
other solution) that will do this much quicker would be greatly
appreciated. When I've tried writing a macro Excel uses the data that
was present in the cell at the time I recorded the macro when I
replay.
Thanks, Alan.
function. Working in SHEET1, I performed a VLOOKUP to extract the
value of the cell from SHEET2. I then filtered out rows that were not
relevant using AUTOFILTER, cut the resulting data and pasted into a
new SHEET3. I've then performed a further VLOOKUP, using the results
of the orginal lookup as the input cell reference. The 2nd LOOKUP
function is reporting an error code (#N/A) when I know the reference
is present in the lookup array, and that this array is sorted
appropriately.
The problem is solved by clicking into the equation line, or F2 and
then pressing enter which makes Excel treat the cell as a number
rather than text field. The fact that the number becomes right hand
justified when clicking in this way demonstrates this.
However, I have 15,000 plus records to correct and regularly come up
against this problem... so any ideas on how to create a macro (or
other solution) that will do this much quicker would be greatly
appreciated. When I've tried writing a macro Excel uses the data that
was present in the cell at the time I recorded the macro when I
replay.
Thanks, Alan.