Return cell value if workbook sheet name matches cell value

[Aaronawl]

Hi,

I have 2 workbooks. 1. Commission and 2. Overview.

1. Commission has 12 tabs, one for each month in the year. On each tab there is a list of commissions and a total figure. Column B contains the headings and column D contains the amounts.

2. Overview needs to be able to return the cell value of total figure based on cell reference: B8

For example:

If cell B8 of overview workbook = "August", I need the formula to search the Commissions workbook for sheet named 'August' and then return the value next to the total figure heading.

Changing the month in cell B8 to update the results for whatever month is entered.

Kind regards,

 

[Roderick_E]

If the totals are always in the same cell on each sheet, you could use indirect:

=INDIRECT("[Book2]"&B8&"!$B$1")

Explanation:

1. [Book2] is the name of the other workbook, Commissions
2. B1 is that static place on each of the month tabs where the total is located
   
3. B8 is the place on the Overview tab where you input the month you want returned.

 

[Aaronawl]

If the totals are always in the same cell on each sheet, you could use indirect:

=INDIRECT("[Book2]"&B8&"!$B$1")

Explanation:

1. [Book2] is the name of the other workbook, Commissions
2. B1 is that static place on each of the month tabs where the total is located
   
3. B8 is the place on the Overview tab where you input the month you want returned.

Thank you for your reply. That seems to be what i'm looking for.

The B1 cell is static on each page. I currently have the formula returning a #REF ! :

=INDIRECT("[Commissions Spreadsheet - Adviser 1 - 2018.xlsx]"&B8&"!$D$41")

Is there someone else that needs changing?

 

[Roderick_E]

Thank you for your reply. That seems to be what i'm looking for.

The B1 cell is static on each page. I currently have the formula returning a #REF ! :

=INDIRECT("[Commissions Spreadsheet - Adviser 1 - 2018.xlsx]"&B8&"!$D$41")

Is there someone else that needs changing?

Ahhh your workbook has a longer name with spaces than your first indicated
INDIRECT("'[Commissions Spreadsheet - Adviser 1 - 2018.xlsx]"&B8&"'!$D$41")

Needed to wrap with single quotations ' '

 

[Aaronawl]

That worked perfect! Thank you very much. It is much appreciated.