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

migrated from stackoverflow.com Sep 13 '10 at 10:11

This question came from our site for professional and enthusiast programmers.


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.

  • hey thanks for ur valuable advice. My data source "'valid PN'" will never max over 15000 rows. Would the app run faster if i change the formula to =VLOOKUP(A3,'valid PN'!$1:$15000,2,FALSE) or does that not make a difference? I am running around 18 different lookup formulas on this worksheet and need this app towork as quickly as possible bec it would be used all day by many users. Any related advice would be greatly apreciated – Nathaniel_613 Sep 13 '10 at 19:18
  • @Nathaniel If you can reduce the size of the lookup you probably should. It might not help performance much though, since VLOOKUP requires the data to be sorted I would assume it stops at the first bit of data that isn't (hence why you get errors for unsorted data) - so having extra white space or not is unlikely to have much effect. Remember than a formula only needs recalculating when something it references changes - so for a VLOOKUP that's the lookup value or table - if neither change the formula doesn't waste time. – DMA57361 Sep 13 '10 at 20:34
  • Looks as though the assumption is that workbook Valid PN was created in an earlier version than xl2007 (ie max 65536 rows) and that in "But 65536 is not a valid row - so you get the error." 65536 should be 65537. – pnuts Mar 29 '13 at 19:26
  • {Oh dear - getting too picky.} "only needs recalculating ..." TRUE but volatile formulae (not that VLOOKUP is one) are recalculated regardless of 'need'. – pnuts Apr 4 '13 at 10:22

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.