Dynamic Formula

[imback2nite]

This is probably an easy one; I’m just not seeing it.

I’m using this formula. It works but I want to make it so the “Emp1” can be changed from “Emp1” thru “Emp30”.
I have a drop down where the selection will be from Emp1, Emp2 etc. to Emp30. The entire formulas parameters would stay the same as seen below. Just the Emp page reference would change.
If this can happen, it will drop the number of formulas’ on the page from 210 to 7 and greatly speed the workbook.
I’m using Windows and Excel v.2003

=IF('Personal Graphs'!$P$2='Personal Graphs'!$Q$9,COUNTIF(Emp1!$B$12:$C$85,'Personal Graphs'!$P$2)+COUNTIF(Emp1!$Q$12:$R$85,'Personal Graphs'!$P$2)+COUNTIF(Emp1!$AF$12:$AG$85,'Personal Graphs'!$P$2)+COUNTIF(Emp1!$B$12:$C$85,'Personal Graphs'!$Q$10)+COUNTIF(Emp1!$Q$12:$R$85,'Personal Graphs'!$Q$10)+COUNTIF(Emp1!$AF$12:$AG$85,'Personal Graphs'!$Q$10),COUNTIF(Emp1!$B$12:$C$85,'Personal Graphs'!$P$2)+COUNTIF(Emp1!$Q$12:$R$85,'Personal Graphs'!$P$2)+COUNTIF(Emp1!$AF$12:$AG$85,'Personal Graphs'!$P$2))



=IF('Personal Graphs'!$P$2='Personal Graphs'!$Q$9,COUNTIF(Emp2!$B$12:$C$85,'Personal Graphs'!$P$2)+COUNTIF(Emp2!$Q$12:$R$85,'Personal Graphs'!$P$2)+COUNTIF(Emp2!$AF$12:$AG$85,'Personal Graphs'!$P$2)+COUNTIF(Emp2!$B$12:$C$85,'Personal Graphs'!$Q$10)+COUNTIF(Emp2!$Q$12:$R$85,'Personal Graphs'!$Q$10)+COUNTIF(Emp2!$AF$12:$AG$85,'Personal Graphs'!$Q$10),COUNTIF(Emp2!$B$12:$C$85,'Personal Graphs'!$P$2)+COUNTIF(Emp2!$Q$12:$R$85,'Personal Graphs'!$P$2)+COUNTIF(Emp2!$AF$12:$AG$85,'Personal Graphs'!$P$2))

 

[Peter_SSs]

Firstly, your original formula ..

=IF('Personal Graphs'!$P$2='Personal Graphs'!$Q$9,COUNTIF('Emp1'!$B$12:$C$85,'Personal Graphs'!$P$2)+COUNTIF('Emp1'!$Q$12:$R$85,'Personal Graphs'!$P$2)+COUNTIF('Emp1'!$AF$12:$AG$85,'Personal Graphs'!$P$2)+COUNTIF('Emp1'!$B$12:$C$85,'Personal Graphs'!$Q$10)+COUNTIF('Emp1'!$Q$12:$R$85,'Personal Graphs'!$Q$10)+COUNTIF('Emp1'!$AF$12:$AG$85,'Personal Graphs'!$Q$10),COUNTIF('Emp1'!$B$12:$C$85,'Personal Graphs'!$P$2)+COUNTIF('Emp1'!$Q$12:$R$85,'Personal Graphs'!$P$2)+COUNTIF('Emp1'!$AF$12:$AG$85,'Personal Graphs'!$P$2))

.. can be written a bit shorter like this:

=COUNTIF('Emp1'!$B$12:$C$85,'Personal Graphs'!$P$2)+COUNTIF('Emp1'!$Q$12:$R$85,'Personal Graphs'!$P$2)+COUNTIF('Emp1'!$AF$12:$AG$85,'Personal Graphs'!$P$2)+IF('Personal Graphs'!$P$2='Personal Graphs'!$Q$9,COUNTIF('Emp1'!$B$12:$C$85,'Personal Graphs'!$Q$10)+COUNTIF('Emp1'!$Q$12:$R$85,'Personal Graphs'!$Q$10)+COUNTIF('Emp1'!$AF$12:$AG$85,'Personal Graphs'!$Q$10),0)

I have then modified the second formula above to meet your drop-down choices. You did not say where your drop-down was. My formula assumes that the drop-down is in cell E6.

=COUNTIF(INDIRECT("'"&E6&"'!$B$12:$C$85"),'Personal Graphs'!$P$2)+COUNTIF(INDIRECT("'"&E6&"'!$Q$12:$R$85"),'Personal Graphs'!$P$2)+COUNTIF(INDIRECT("'"&E6&"'!$AF$12:$AG$85"),'Personal Graphs'!$P$2)+IF('Personal Graphs'!$P$2='Personal Graphs'!$Q$9,COUNTIF(INDIRECT("'"&E6&"'!$B$12:$C$85"),'Personal Graphs'!$Q$10)+COUNTIF(INDIRECT("'"&E6&"'!$Q$12:$R$85"),'Personal Graphs'!$Q$10)+COUNTIF(INDIRECT("'"&E6&"'!$AF$12:$AG$85"),'Personal Graphs'!$Q$10),0)

If this can happen, it will drop the number of formulas’ on the page from 210 to 7 and greatly speed the workbook.

Whilst the change may drop the number of formulas, it may not speed the workbook.
To achieve the result you wanted, the INDIRECT function had to be employed. That function is a volatile function which, in general, has the opposite effect to speeding up a worksheet.

How it affects your workbook will depend on the particular circumstances and use of your workbook so you will just have to test to see what happens in relation to speed. ?

 

[imback2nite]

That works GREAT! I had tried using CONCATENATE. Incorrectly. Thank you so much! As for speed, it didn't really speed the process much but it DID make it easier to read! Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the feedback.