working with 2 excel workbooks with changing worksheets?

By scontiu ·
I have a workbook that contains a column that gets its information from cells from a different workbook. I am currenntly using the following to get the data.
=IF('[Revenue_Charged_to_3PD.xls]WE 2.25'!$A$15="Hugh Gordon",SUM('[Revenue_Charged_to_3PD.xls]WE 2.25'!$W$15),"Incorrect Driver Cell")

The problem is that both workbooks will have another sheet copied and added at the end using the week ending date as its name in the following format WE 2.25 for feb 25.
I would like to automate the formula to look for that new sheet name.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

working with 2 excel workbooks with changing worksheets?

by pttonka In reply to working with 2 excel work ...

I???m not sure how complicated making this automated would be, what I would do is make a reference cell for the week. For example B2 would contain WE 2.25
Then I would use INDIRECT and make the formula .

=IF(INDIRECT("'[Revenue_Charged_to_3PD.xls]"&$B$2&"'!"&"$A$15")="Hugh Gordon",SUM(INDIRECT("'[Revenue_Charged_to_3PD.xls]"&$B$2&"'!"&"W$15")),"Incorrect Driver Cell")

Then you only have to care about the sheet name in cell B2 to get the correct data and not change lots of formulas each week.
Important, Both workbooks have to be open to get the data, INDIRECT doesn't work with closed workbooks.

Related Discussions

Related Forums