Adding a variable to a formula

bclor3591

New Member
Joined
Oct 7, 2010
Messages
33
I have a Excel formula question for this forum.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
<o:p> </o:p>
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:p> </o:p>
<o:p> </o:p>
Thank you in advance on this matter.<o:p></o:p>
<o:p> </o:p>
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

Indirect is the way to go..

Say E5 = "M1" <--sheet name...
Also the SUM is not necessary...

Try

=INDIRECT("'[PG-PRD-SETUP.xlsx]" & E5 & "'!$D$4")-INDIRECT("'[PG-DEV-SETUP.xlsx]" & E5 & "'!$D$4")


Note, that the 2 books must be open.
Indirect will not work on closed books.
 

bclor3591

New Member
Joined
Oct 7, 2010
Messages
33
Hello jonmo1,

Thank you for the quick response. I setup the formula just the way you sent it to me. I substituted your E5 with C3 (that is the user input cell where they will put in M1-M9. I'm still getting a #REF error. Did I code it wrong?
=INDIRECT(" '[pg-prd-setup.xlsx]" & C3 & " '!$D$4")-INDIRECT(" '[pg-dev-setup.xlsx]" & C3 & " '!$D$4") I can send a cutaway view of the 3 xlsx files if you want? It might make it easier for you? Just let me know how to send to you? $D$4 should be the cell in the other xlsx files where the data resides.
Maybe don't need the $ in there?


Thanks,
Bob
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Too many spaces

=INDIRECT(" '[pg-prd-setup.xlsx]" & C3 & " '!$D$4")-INDIRECT(" '[pg-dev-setup.xlsx]" & C3 & " '!$D$4")
should be
=INDIRECT("'[pg-prd-setup.xlsx]" & C3 & "'!$D$4")-INDIRECT("'[pg-dev-setup.xlsx]" & C3 & "'!$D$4")
 

bclor3591

New Member
Joined
Oct 7, 2010
Messages
33
That worked! It is amazing how a space here and there can whack a formula like that. Thank you very much for your assistance.
Bob
 

Watch MrExcel Video

Forum statistics

Threads
1,122,691
Messages
5,597,562
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top