razorsharp192
New Member
- Joined
- Aug 24, 2022
- Messages
- 4
- Office Version
- 2010
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)
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)