How do I keep 3-D reference the same when inserting one row on one

  • Thread starter Thread starter jcrowe
  • Start date Start date
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
 
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,
J Crowe
 
Bernard Liengme said:
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
Click to expand...
....

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.
 
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
 
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,sheet1!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?
 
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
 
Me again,

What do I do with the following example, Employee name on company sheet is
in column E row 14, the amount is in column I row 14, the sheet it needs to
go to the employee name is in Column E Row 11 the amount is in Column I Row
11. So When ever I add a new employee in the Company sheet, the other sheet
is off because it pushes all down. Does this make sense?

Thanks Jayne crowe
 
jcrowe said:
I appreciate your response, but am a bit lost with your info, I need
something a littler easier to understand.
Click to expand...

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. . . .
Click to expand...
....

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.
 
jcrowe said:
What do I do with the following example, Employee name on company sheet is
in column E row 14, the amount is in column I row 14, the sheet it needs to
go to the employee name is in Column E Row 11 the amount is in Column I Row
11. So When ever I add a new employee in the Company sheet, the other sheet
is off because it pushes all down. Does this make sense?
Click to expand...
....

Is there some reason you can't add new employee records in the row
immediately below that current bottommost record? Are you inserting
then entering records in order to maintain alphabetical order? If so,
you'd be better off (in terms of greater simplicity) using 2 tables
rather than one: add all new records at the bottom of the first table;
then use a macro to copy the first table and past on top of the second
table and sort the second table as needed.
 
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 said:
jcrowe said:
I appreciate your response, but am a bit lost with your info, I need
something a littler easier to understand.
Click to expand...

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. . . .
Click to expand...
....

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.
Click to expand...
 
jcrowe said:
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.
Click to expand...

If you're pulling records into the 'sort' worksheet 1st from the
'Alpha order HDPC' worksheet then from the 'Alpha Order Management'
worksheet, you'd be better off using formulas like the following in
the 'sort' worksheet. I'm going to assume row 1 in all 3 of these
worksheets contain column headings with records beginning in row 2.
I'm also going to assume there are no blank fields in any record in
either of the 1st 2 worksheets. Finally, I'm going to assume the first
field of these records is in column A.

sort!A2:
=IF(ROWS(A$2:A2)<COUNTA('Alpha order HDPC'!A:A),INDEX('Alpha order
HDPC'!A:A,ROWS(A$1:A2)),
INDEX('Alpha Order Management'!A:A,ROWS(A$1:A2)-COUNTA('Alpha order
HDPC'!A:A)+1))

Fill A2 right as far as needed. I'll assume that would be through
column Z. Then select sort!A2:Z2 and fill down as far as needed.

No matter how you insert or delete rows in the 1st 2 worksheets, these
formulas in the 3rd worksheet will pull in records in order from the
1st 2 worksheets.
 
Back
Top