Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on one
How do I keep 3-D reference the same when inserting one row on one sheet.
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on one
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "jcrowe" wrote in message ... How do I keep 3-D reference the same when inserting one row on one sheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on one
use indirect()
so.. if you want the value in cell B3, type =INDIRECT("B3"). "jcrowe" wrote: How do I keep 3-D reference the same when inserting one row on one sheet. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
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" 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "jcrowe" wrote in message ... How do I keep 3-D reference the same when inserting one row on one sheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row onone
"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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
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 "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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
jcrowe wrote...
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? .... 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep 3-D reference the same when inserting one row on
jcrowe wrote...
.... 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference the same row from another sheet after inserting a new ro | Excel Worksheet Functions | |||
freeze cell reference when inserting a row | Excel Discussion (Misc queries) | |||
Reference Problem w/ inserting rows | Excel Discussion (Misc queries) | |||
Maintain Relative Reference After Inserting a Column | Excel Worksheet Functions | |||
Conditional Formatting Reference / Inserting Rows | Excel Worksheet Functions |