INDIRECT function within VLOOKUP to avoid #REF error???

**Hozcat** · 04-03-2012, 09:32 AM

Hi,

Any help with this would be much appreciated.

I have a workbook that contains the formula below:
=IF(A2<>"",VLOOKUP(A2,'All Steps'!A:AH,34,FALSE),"")

The "All Steps" worksheet data needs to be updated regularly and is currently copied and pasted from another workbook. However, the amount of data contained in this worksheet is huge so to reduce the file size I'm using a macro to copy/paste special (values) the returned VLOOKUP results into another worksheet and then clearing the All Steps data.

The amount of data now contained with the All Steps worksheet is now so great that it is causing my PC to freeze for an age at a time whilst I copy / paste it from the original document into my workbook (even when pasting values only) and sometimes it's causing the application to crash depending on what other applications I have open at the time.

I noticed that if I moved the worksheet tab from the original document into my workbook it transfers significantly quicker but once I delete the sheet afterwards it causes all the VLOOKUP formulas to contain the #REF error rather than the worksheet lookup reference.

Is it possible to retain the worksheet reference using the INDIRECT function nested within the VLOOKUP and force excel to lookup the data when the worksheet is moved across and not if the worksheet is deleted; and if so, could someone show amend the function above as an example (I've no experience of using INDIRECT before)?

Alternatively, if I'm over complicating this and there is a quick way to copy the huge amount of data over (it's about 13Meg without formatting) Could someone explain me how to do this?

Thanks,
Matt

**Richard Buttrey** · 04-03-2012, 09:54 AM

Hi,

Have you tried putting the system into manual calculation mode and switching off screen updating as the first step in your macro, and then putting automatic calc back on at the end?

If this is no better then upload a representative sample of your workbook and the data you're copying. We don't need the all singing all dancing version with your thousands of rows, just a hundred or so will suffice.

**Decoderman** · 04-03-2012, 10:02 AM

The Indirect function to edit your code would be.

Please Login or Register  to view this content.

As long as the sheet is in the current workbook this will work. Indrect functions will only work on workbooks that are open unless you get the addin to allow otherwise. I was a little confused on exactly what you were asking but figured I would try and help you out atleast a little bit.

**Hozcat** · 04-03-2012, 10:30 AM

Cheers for your replies people but realised I was over complicating things for myself.

By removing the formatting from the source document before Copying, rather than Copy / Paste Special. The amount of time it takes to transfer the data is dramatically reduced so I'll live with it as is rather than trying to re-invent the wheel!

Thanks again,
Matt