CODEWITHSUNDEEP
exceltestingexcel-formulametrics
dvniel
dvniel

Reputation: 142

Referencing another worksheet within a formula using the worksheet name

I'm using Excel for reporting/tracking test metrics. My sheet has several references to a range in another worksheet called '20th February', for example:

=COUNTIFS('20th February'!B:B, "Customer Care", '20th February'!E:E,"Pass")

...and so on. There are about 35 cells like this.

Rather than having to change the date in each cell manually, I want the date to take reference from another cell in the same worksheet so I only have to change the date in one place.

So, if C1 contains the text 20th February which is the name of another sheet, how can I incorporate that within the formulas above?

I think the issue is that the cell I'm referencing isn't being interpreted as a worksheet, but I thought INDIRECT does exactly that. I've looked into the VLOOKUP function as well, but I just can't figure it out.

Any help would be appreciated!

Upvotes: 1

Views: 80

Answers (1)

Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Yes using INDIRECT is the correct approach, just make sure you get the syntax correct

=COUNTIFS(INDIRECT("'"&$C$1&"'"&"!B:B"), "Customer Care", INDIRECT("'"&$C$1&"'"&"!E:E"),"Pass")

So INDIRECT("'"&$C$1&"'"&"!B:B") is sticking together ' + February 20th + '!B:B

Upvotes: 2

Related Questions