Dynamic formulae - similar to lotus 123 for excel

[JohnD]

Hello Excel disciples,
=A0
=A0
I wonder if I can run this past you?
=A0
=A0
Lotus 123 had a facility that you were able to build "Dynamic" formulae ie;
=A0to construct using text and ranges to create valid formulae.
=A0
I have several worksheets name Team 1, Team 2, Team 3, each identical etc
I have a consolidation worksheet that takes numeric values from a cell from=
each
and sums the total.
=A0
In the Totals sheet columns above the values column is the team name in=
text.
=A0
=A0
=A0Team 13 =09=09Team 14=09=09Team 15 etc
=A0Grd1 =A0Grd2
=A0100 =A0=09375
=A0100=09=09375
43.5=09=09163.0
=A0
I want to use the formula to pick up the text "Team 13" and use it in a=
"sumif" function as shown below
instead of having to hard code it into the formulae for each cell entry for=
which there is a lot!!!
=A0
=3DSUMIF(' Team 13'!$G$6:$P$6,AC$6,'Team 13'!$G24:$P24)
=A0
The reason for this is, the position of the teams are liable to change=
therefore
=A0by picking the team number from the text entry would mean the formulae=
would depend
on the text in that cell, so if the team name changes the formulae would=
change as well keeping the
the maintenance level vastly reduced.
=A0
Any help would be gratefully received
=A0
JohnD

[Bob Phillips]

=SUMIF(INDIRECT("'"&A1&"'!$G$6:$P$6"),AC$6,INDIRECT("'"&A1&"'!$G24:$P24"))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnD" <[email protected]> wrote in message
news:20057523856.167794@TABLETOP...
Hello Excel disciples,


I wonder if I can run this past you?


Lotus 123 had a facility that you were able to build "Dynamic" formulae ie;
to construct using text and ranges to create valid formulae.

I have several worksheets name Team 1, Team 2, Team 3, each identical etc
I have a consolidation worksheet that takes numeric values from a cell from
each
and sums the total.

In the Totals sheet columns above the values column is the team name in
text.


Team 13 Team 14 Team 15 etc
Grd1 Grd2
100 375
100 375
43.5 163.0

I want to use the formula to pick up the text "Team 13" and use it in a
"sumif" function as shown below
instead of having to hard code it into the formulae for each cell entry for
which there is a lot!!!

=SUMIF(' Team 13'!$G$6:$P$6,AC$6,'Team 13'!$G24:$P24)

The reason for this is, the position of the teams are liable to change
therefore
by picking the team number from the text entry would mean the formulae would
depend
on the text in that cell, so if the team name changes the formulae would
change as well keeping the
the maintenance level vastly reduced.

Any help would be gratefully received

JohnD