Hello Sean,

I tried this, its makes sense, can you let me know if I did it correct, when
I do it, it comes up with a message that says I have to many arguments for
this function and highlights the 0 at the end, this is what I have,
=index('Alpha order HDPC'!I6:I86,MATCH(Hiza, Natlie,'Alpha order
HDPC'!I:I,0),11)

Really appreciat the help,
Jayne

"Sean Timmons" wrote:

Are you saying you only want them to populate once you sort them?

When it comes to INDEX(), you are typically bst off using the Fx to build
it.

Typically, you'll do an INDEX with MATCH()

INDEX() starts with the entire range you are looking at, then needs to know
which row number and column number you would like to return the value from.

Match will search for a value (for instance, name of employee) in a list,
and return where in the list they are. So, if your employee is in A5, and
your match is looking in column A for employee name, it would return the
value 5.

=INDEX(Sheet1!A1:X200,MATCH(youremployeename,sheet 1!A:A,0),2)

would return the value in column B where your employee is in column A.

Does this get you closer to where you need to be?

"jcrowe" wrote:

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.