Sorry to say I did not understand your information in the first message. I
have been trying alot of different things. I want to give you a little more
info on the workbook, I don't know if it will help or not.
The workbook has 5 sheets.
Alpha order HDPC
Alpha Order Management
Sort
Depart Breakdown
Summary

the first 2 have employees for the 2 different companies, with location,
hours deadepartment etc., these are the sheets I input the hours etc. each
payperiod. the sort sheet has the employees for both companies combined, I
have a simple formula in the sort sheet to pull the information from the
first 2 sheets example
=('Alpha order HDPC'!I14), this will pull the hours for that perticuliar
employee. The problem is when I add additional row into one of the first 2
sheets, the formula in the sort sheet changes or course. I need the sort
sheet to stay the same if I add new rows the the first two sheets.
Does that help? So do I use the formula you sent for this?
by they way I do appreciate the time you have spent to help.
I will be gone for 3 days, but I was wondering if I can't get this if there
is a way to e-mail you a sample of the workbook so you can look at it?
Thank you,
Jayne

"Harlan Grove" wrote:

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.