Question Locked

working with 2 excel workbooks with changing worksheets?

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

2 replies

by pttonka

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.

This discussion is closed to new replies.