I have a Excel formula question for this forum.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have 3 workbooks: They are ANALYSIS (that has all the variance calculations, PG-PRD-SETUP that has tabs M1-M9)(tabular data) & PG-DEV-SETUP (that has tabs M1-M9)(tabular data). I'm trying to get the variance across two workbooks PG-PRD-SETUP and PG-DEV-SETUP calculate in the ANALYSIS workbook. Is there a way to have a user enter in a predetermined cell on the ANALYSIS workbook either M1, M2, M3...M9 and have the formula in the ANALYSIS workbook calculate the variance for each of the data workbooks TAB M1, M2...M9. I was trying to use INDIRECT, but I'm not sure how to use it in the formula. Any help would be greatly appreciated.<o></o>
<o> </o>
Here is the formula that I started with: =SUM('[PG-PRD-SETUP.xlsx]M1'!$D$4-('[PG-DEV-SETUP.xlsx]M1'!$D$4)) M1 needs to be a variable!
I also tried CONCATENATE and used this approach:
=SUM(INDIRECT(E5,E4) - INDIRECT(F5,E4))
All I get is a #REF error on the ANALYSIS workbook.
The finance department wants this report as soon a possible so I am trying all avenues to get help with this. M1-M9 represents Month 1 to Month 9.
Any assstance would be greatly appreciated.
<o> </o>
<o> </o>
Thank you in advance on this matter.<o></o>
<o> </o>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have 3 workbooks: They are ANALYSIS (that has all the variance calculations, PG-PRD-SETUP that has tabs M1-M9)(tabular data) & PG-DEV-SETUP (that has tabs M1-M9)(tabular data). I'm trying to get the variance across two workbooks PG-PRD-SETUP and PG-DEV-SETUP calculate in the ANALYSIS workbook. Is there a way to have a user enter in a predetermined cell on the ANALYSIS workbook either M1, M2, M3...M9 and have the formula in the ANALYSIS workbook calculate the variance for each of the data workbooks TAB M1, M2...M9. I was trying to use INDIRECT, but I'm not sure how to use it in the formula. Any help would be greatly appreciated.<o></o>
<o> </o>
Here is the formula that I started with: =SUM('[PG-PRD-SETUP.xlsx]M1'!$D$4-('[PG-DEV-SETUP.xlsx]M1'!$D$4)) M1 needs to be a variable!
I also tried CONCATENATE and used this approach:
=SUM(INDIRECT(E5,E4) - INDIRECT(F5,E4))
All I get is a #REF error on the ANALYSIS workbook.
The finance department wants this report as soon a possible so I am trying all avenues to get help with this. M1-M9 represents Month 1 to Month 9.
Any assstance would be greatly appreciated.
<o> </o>
<o> </o>
Thank you in advance on this matter.<o></o>
<o> </o>