Grouping & Formula

[Guest]

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.

 

[RagDyer]

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?

 

[Guest]

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]

AFAIK, it can't be done!

Try posting to the programming group to see if it can be done with code.