#REF! shown after Excel Sheet 'Move or Copy' operation [deleting and inserting sheets]

Greetings all.

I have an Excel (attached) where one of the Sheets has reference to another Sheet cells values.

#REF!.png




When Sheet1 is removed, Sheet2 cells' values show #REF!

Again as per my requirement, the same Sheet1 is inserted by 'Move or Copy' operation. Now too, Sheet2 cell values show #REF! again (expecting Sheet1 values updated in Sheet2).

Clue: Found OFFSET function, as solution, for cell values operations. Can we have OFFSET function (for Sheets) as one of the solutions?.

Help appreciated, thanks in advance.

Similar as previous answer, I just kept sheet name in A1 & used row()

Formula:

Please Login or Register  to view this content.

Indirect.JPG

Thanks @kasan.

How to get values starting from B6 (Volume column) until the end (not from B2 to B20).

Image FYR and assistance:
Small change.png

Can anyone help, please.

Originally Posted by rorya

Why not copy and paste data into the existing sheet instead of deleting it?

Can you please share 'Copy-Paste VBA code' (whole sheet's content in a specific Sheet)..

Originally Posted by kasan

Just correct cell reference on Sheet2, see attached file.

'Added Reputation' to you, @kasan. You are appreciated much.

Thanks so much for your kind help.