Countifs linking to another workbook, but with link to other workbook in another cell?

[razorsharp192]

hi all

I'm working on a spreadsheet that's using a countifs formula to count instances of 2 variables. The formula is looking at a seperate workbook and a specific sheet within that workbook. The workbook has seperate tabs for each month, my formula looks at figures on a monthly basis.

I've set up a version of my spreadsheet as a template (as this is for a monthly thing), but currently the formula has the full file path of the other workbook and sheet in it (e.g. AUG tab of workbook "figures"), however for use as a template this is no good, as when I need to set up September's figures my formulas will contain AUG.

What I could do with is having the file path of the other workbook in a different cell (e.g. cell A1) and then have my formula point to that cell rather than having the file path in the formula itself.

my current formula is

=COUNTIFS('C:\Desktop\EXCEL EXAMPLE\[DATA.xlsx]AUG'!$A$3:$A$9, A3, 'C:\Desktop\EXCEL EXAMPLE\[DATA.xlsx]AUG'!$B$3:$B$9, $B$1)

however I'd like to have the "C:\Desktop\EXCEL EXAMPLE\[DATA.xlsx]AUG" bit in a different cell, with the formula pointing to that cell.

is that possible?


(FYI i'm using Excel 2010)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(INDIRECT("'"&a1&"'!$A$3:$A$9"), A3, INDIRECT("'"&A1&"'!$B$3:$B$9"), $B$1)

 

[razorsharp192]

Hi & welcome to MrExcel.
How about

=COUNTIFS(INDIRECT("'"&a1&"'!$A$3:$A$9"), A3, INDIRECT("'"&A1&"'!$B$3:$B$9"), $B$1)

Hi, thanks for getting back to me, i'm getting a REF! error when i input that formula, how exactly should I have the file path stated in cell A1?

 

[Fluff]

Is the other workbook open?

 

[razorsharp192]

Is the other workbook open?

yes it's open, how should I format the file path in cell A1?

 

[Fluff]

You can use [DATA.xlsx]AUG in A1

 

[razorsharp192]

You can use [DATA.xlsx]AUG in A1

BINGO! thanks a lot!

 

[Fluff]

Glad to help & thanks for the feedback.