Hello Harlan,

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

not sure if you can see the reply I sent to Bernard, so I am going to cut
and paste here,

I tried this and it comes back with #REF. What I have is 2 seperate sheets
with employees total hours etc. these two sheets pull into one sheet. When I
enter data I have the 2 sheets sorted alpha, then sort them by dept. and
location. once they are by department and location they pull into the 3rd
sheet, which form there goes to 2 other sheets. anyway when I add a new
employee it throws it off. I used $ and it worked until the things above
changed.

Thanks,
Jayne Crowe


"Harlan Grove" wrote:

"Bernard Liengme" wrote...
If Sheet1 has the formula =Sheet2!A5 and then a new row in inserted above
row 5 on Sheet2 the formula automatically becomes =Sheet2!A6
The formula =INDIRECT("Sheet2!A5") is unchanged when Sheet2 is modified
best wishes
....

And if the OP needs many such formulas, the overuse of the volatile
INDIRECT function would really suck the performance out of the OP's
system. There's also the more immediate problem of entering or editing
the static references in many such formulas.

Better by far to use INDEX, e.g., if the value of Sheet2!A5 were
needed in cell X99 of the current worksheet and the value of Sheet2!B6
were needed in cell Y100, enter the following formula in X99, copy X99
and paste into Y100.

X99:
=INDEX(Sheet2!$1:$65536,ROWS($X$99:X99)+4,COLUMNS( $X$99:X99))

This particular formula for X99 will work the same as =Sheet2!A5 when
copying and pasting or dragging and filling.

INDIRECT and OFFSET functions are useful when used SPARINGLY. They're
nightmares when used promiscuously.