I am trying to use paste-special\formula option to copy the formula:
=VLOOKUP(A3,'valid PN'!1:65536,2,FALSE) and paste into other cells in the same column, but I am getting an error and the pasted output is like this:
=VLOOKUP(A4,'valid PN'!#REF!,2,FALSE).Please advise how to copy this type of formula and not lose the table array in the formula
1 Answer
What is happening is you are trying to reference the entire sheet by doing 'valid PN'!1:65536 (which means "rows 1 to 65536 of sheet 'valid PN'). But, when you paste the formula, the locations are changed based on the offset of you pasting (notice how A3 becomes A4?). The equivalent offset of 1:65536 would be 2:65537. But 65536 is not a valid row - so you get the error.
You need to lock the formula so the locations are not changed relative to the offset of the pasting location, you do this using the $ symbol, like below, this means the rows given will always be used, and not changed due to paste offset.
=VLOOKUP(A3,'valid PN'!$1:$65536,2,FALSE)I'm assuming you do want the A3 to move relative to the fomula location, but you can $ that as well if you don't.
