| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping & Formula
Here is a good one...I've been stuck on this one for a while now...
I currently have two worksheets open. On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial # per cell). On Sheet2 in cells A1:A4 I have the following formulas, =INDIRECT("SHEET1!A1) through A4. SHEET 1 A1 = 100 A2 = 200 A3 = 300 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =INDIRECT("SHEET1!A1) the value is 100 A2 =INDIRECT("SHEET1!A2) the value is 200 A3 =INDIRECT("SHEET1!A3) the value is 300 A4 =INDIRECT("SHEET1!A4) the value is 400 Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on Sheet2 to read correctly? Or group with sheet1? SHEET1 A1 = 100 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =NOCLUE(!?!?) the value is 100 A2 =NOCLUE(!?!?) the value is 400 A3 =NOCLUE(!?!?) the value is 500 A4 =NOCLUE(!?!?) the value is 600 Any help would be greatly appriciated. Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping & Formula
I can't duplicate your problem.
Of course, I also can't duplicate your posted formulas. I assume you have a typo, and left out the second set of quotes: =INDIRECT("SHEET1!A1) =INDIRECT("SHEET1!A1") With the correct formula: =INDIRECT("SHEET1!A1") And grouping the rows using <Data <Group&Outline <Group My Sheet2 *doesn't* change at all! The display is identical ... before and after grouping rows 2 and 3. What exact formulas are you using? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Gabe" wrote in message ... Here is a good one...I've been stuck on this one for a while now... I currently have two worksheets open. On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial # per cell). On Sheet2 in cells A1:A4 I have the following formulas, =INDIRECT("SHEET1!A1) through A4. SHEET 1 A1 = 100 A2 = 200 A3 = 300 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =INDIRECT("SHEET1!A1) the value is 100 A2 =INDIRECT("SHEET1!A2) the value is 200 A3 =INDIRECT("SHEET1!A3) the value is 300 A4 =INDIRECT("SHEET1!A4) the value is 400 Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on Sheet2 to read correctly? Or group with sheet1? SHEET1 A1 = 100 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =NOCLUE(!?!?) the value is 100 A2 =NOCLUE(!?!?) the value is 400 A3 =NOCLUE(!?!?) the value is 500 A4 =NOCLUE(!?!?) the value is 600 Any help would be greatly appriciated. Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping & Formula
Sorry about that little typo, you are correct the formula is
=INDIRECT("SHEET1!A1"). See when I group rows 2 and 3 on sheet1, I actually need sheet2 to reflect the changes. For instance: SHEET 1 (Notice how A2 & A3 are grouped) A1 = 100 A4 = 400 (Now the value of 400 is next, this value should equal A2 on sheet2) A5 = 500 (This value should equal A3 on sheet2) A6 = 600 (This value should equal A4 on sheet2) SHEET 2 A1 = 100 (A1 on sheet1) A2 = 400 (A4 on sheet1) A3 = 500 (A5 on sheet1) A4 = 600 (A6 on sheet1) What formula(s) could I use instead of =INDIRECT("SHEET1!A1") to make that happen? Or is there another way I could have excel do that? Any help is greatly appriciated. Thanks. "RagDyer" wrote: I can't duplicate your problem. Of course, I also can't duplicate your posted formulas. I assume you have a typo, and left out the second set of quotes: =INDIRECT("SHEET1!A1) =INDIRECT("SHEET1!A1") With the correct formula: =INDIRECT("SHEET1!A1") And grouping the rows using <Data <Group&Outline <Group My Sheet2 *doesn't* change at all! The display is identical ... before and after grouping rows 2 and 3. What exact formulas are you using? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Gabe" wrote in message ... Here is a good one...I've been stuck on this one for a while now... I currently have two worksheets open. On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial # per cell). On Sheet2 in cells A1:A4 I have the following formulas, =INDIRECT("SHEET1!A1) through A4. SHEET 1 A1 = 100 A2 = 200 A3 = 300 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =INDIRECT("SHEET1!A1) the value is 100 A2 =INDIRECT("SHEET1!A2) the value is 200 A3 =INDIRECT("SHEET1!A3) the value is 300 A4 =INDIRECT("SHEET1!A4) the value is 400 Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on Sheet2 to read correctly? Or group with sheet1? SHEET1 A1 = 100 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =NOCLUE(!?!?) the value is 100 A2 =NOCLUE(!?!?) the value is 400 A3 =NOCLUE(!?!?) the value is 500 A4 =NOCLUE(!?!?) the value is 600 Any help would be greatly appriciated. Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping & Formula
AFAIK, it can't be done!
Try posting to the programming group to see if it can be done with code. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Gabe" wrote in message ... Sorry about that little typo, you are correct the formula is =INDIRECT("SHEET1!A1"). See when I group rows 2 and 3 on sheet1, I actually need sheet2 to reflect the changes. For instance: SHEET 1 (Notice how A2 & A3 are grouped) A1 = 100 A4 = 400 (Now the value of 400 is next, this value should equal A2 on sheet2) A5 = 500 (This value should equal A3 on sheet2) A6 = 600 (This value should equal A4 on sheet2) SHEET 2 A1 = 100 (A1 on sheet1) A2 = 400 (A4 on sheet1) A3 = 500 (A5 on sheet1) A4 = 600 (A6 on sheet1) What formula(s) could I use instead of =INDIRECT("SHEET1!A1") to make that happen? Or is there another way I could have excel do that? Any help is greatly appriciated. Thanks. "RagDyer" wrote: I can't duplicate your problem. Of course, I also can't duplicate your posted formulas. I assume you have a typo, and left out the second set of quotes: =INDIRECT("SHEET1!A1) =INDIRECT("SHEET1!A1") With the correct formula: =INDIRECT("SHEET1!A1") And grouping the rows using <Data <Group&Outline <Group My Sheet2 *doesn't* change at all! The display is identical ... before and after grouping rows 2 and 3. What exact formulas are you using? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Gabe" wrote in message ... Here is a good one...I've been stuck on this one for a while now... I currently have two worksheets open. On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial # per cell). On Sheet2 in cells A1:A4 I have the following formulas, =INDIRECT("SHEET1!A1) through A4. SHEET 1 A1 = 100 A2 = 200 A3 = 300 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =INDIRECT("SHEET1!A1) the value is 100 A2 =INDIRECT("SHEET1!A2) the value is 200 A3 =INDIRECT("SHEET1!A3) the value is 300 A4 =INDIRECT("SHEET1!A4) the value is 400 Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on Sheet2 to read correctly? Or group with sheet1? SHEET1 A1 = 100 A4 = 400 A5 = 500 A6 = 600 SHEET2 A1 =NOCLUE(!?!?) the value is 100 A2 =NOCLUE(!?!?) the value is 400 A3 =NOCLUE(!?!?) the value is 500 A4 =NOCLUE(!?!?) the value is 600 Any help would be greatly appriciated. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
| Help with macro formula and variable | Excel Worksheet Functions | |||
Hybrid Mode
