Dynamic formulae - similar to lotus 123 for excel

  • Thread starter Thread starter JohnD
  • Start date Start date
J

JohnD

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 fromeach
and sums the total.
 
In the Totals sheet columns above the values column is the team name intext.
 
 
 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 forwhich 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 changetherefore
 by picking the team number from the text entry would mean the formulaewould depend
on the text in that cell, so if the team name changes the formulae wouldchange as well keeping the
the maintenance level vastly reduced.
 
Any help would be gratefully received
 
JohnD
 
=SUMIF(INDIRECT("'"&A1&"'!$G$6:$P$6"),AC$6,INDIRECT("'"&A1&"'!$G24:$P24"))


--

HTH

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


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
 
Back
Top