jcrowe wrote...
I appreciate your response, but am a bit lost with your info, I need
something a littler easier to understand.

There's a trade-off between easy to understand and easy to use.
INDIRECT with only one argument may be easy to understand, but if you
need many formulas calling it for different cells, it's a PITA to use.
Also, those formulas recalculate whenever anything anywhere in Excel
triggers recalculation. The INDEX-based formulas I showed would only
recalculate when something in the source worksheet changed.

The INDEX function isn't so hard to understand. It's 1st argument, A
in INDEX(A,B,C), is the range of cells in which you want a particular
cell's value. The range Sheet2!$1:$65536 refers to ALL cells in
Sheet2, so INDEX(Sheet2!$1:$65536,B,C) can be used to get any cell
from Sheet2.

The 2nd and 3rd arguments, B and C, are less obvious but not
completely mysterious. If you're writing formulas in SheetN and you
want the value of Sheet2!E7 in SheetN!P13, the value of Sheet2!G8 in
SheetN!P14, the value of Sheet2!H7 in SheetN!Q13, etc., then in SheetN!
P13 you want the value in Sheet2 in the 5th columns and the 7th row.
The way I showed to do this in my previous response was

B [row index]: ROWS($P$13:P13)+6
C [column index]: COLUMNS($P$13:P13)+4

The B expression becomes 1+6 = 7 and the C expression 1+4 = 5, so the
INDEX call

INDEX(Sheet2!$1:$65536,ROWS($P$13:P13)+6,COLUMNS($ P$13:P13)+4)

is equivalent to

INDEX(Sheet2!$1:$65536,7,5)

which is the cell at the 7th row and 5th column in Sheet2, so Sheet2!
E7. Having said that, it would have been simpler for me to have shown
the alternative equivalent formula

=INDEX(Sheet2!$1:$65536,ROWS($A$1:E7),COLUMNS($A$1 :E7))

As long as you'd never be inserting or deleting rows or columns in
SheetN, this formula would always return the value of Sheet2!E7 no
matter how many rows/columns were inserted/deleted in Sheet2, AND
it'll copy and paste or drag and fill the SAME as the simple formula
=Sheet2!E7.

If you want to bypass Excel's normal behavior (skipping automatic
adjustments to range references when rows or columns are inserted of
deleted), you have to be prepared to use somewhat nonobvious formulas.

I tried this and it comes back with #REF. . . .
....

Did you try MY formula or give up on it because you didn't understand
it? If the former, you need to provide more details, such as the EXACT
formula you have tried that returned #REF! as well as a simple formula
referring to the single cell you want and the address of the cell in
which you want this formula. If the latter, I've provided a solution
that I know works under the layout I explicitly stated. If your actual
layout differs, you need to provide more details, by which I mean
actual 3D range or cell references, not an approximate outline of what
you're doing.