How can I add specific text to build cell references to an external workbook?

emaildon

New Member
Joined
Jun 1, 2019
Messages
5
Hi

I have a workbook ("Analysis") that has multiple sheets querying another workbook ("EnquiriesDataExport"). "EnquiriesDataExport" is automatically exported from our system every 24 hours so it needs to be separate from my "Analysis".

Each sheet in "Analysis" will be the same except that the cells in each sheet will query different worksheets in "EnquiriesDataExport"

A typical cell in one "Analysis" worksheet might be:

=COUNTIFS([EnquiriesDataExport.xlsx]Interviews!$A:$A,">="&C$1,[EnquiriesDataExport.xlsx]Interviews!$A:$A,"<="&D$1,[EnquiriesDataExport.xlsx]Interviews!$C:$C,$A2)

in a second worksheet the formula will be:

=COUNTIFS([EnquiriesDataExport.xlsx]Telcons!$A:$A,">="&C$1,[EnquiriesDataExport.xlsx]Telcons$A:$A,"<="&D$1,[EnquiriesDataExport.xlsx]Telcons!$C:$C,$A2)

in a third worksheet the formula will be:

=COUNTIFS([EnquiriesDataExport.xlsx]Meetings!$A:$A,">="&C$1,[EnquiriesDataExport.xlsx]Meetings!$A:$A,"<="&D$1,[EnquiriesDataExport.xlsx]Meetings!$C:$C,$A2)

etc etc

I'd like to set up a standard sheet for the "Analysis" workbook whereby with a drop down menu which i could get all of the cells to reference the required sheet. e.g. Interviews, Telcons, Meetings.

Is there a way to do this so that my formula could "build" itself with the chosen value inserted into the string?

If i choose "Interviews" in Cell A1 dropdown then Cell A2 would change from:

=COUNTIFS([EnquiriesDataExport.xlsx]Meetings!$A:$A,..

to

=COUNTIFS([EnquiriesDataExport.xlsx]Interviews!$A:$A,..

Sorry for the long winded explanation. I've looked at CONCATENATE and INSERT but can't find a way to do what I want to do. Perhaps there is a better way altogether. Any ideas or prompts gratefully received.

Thanks
Don
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,261
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
You can use INDIRECT like
=COUNTIFS(INDIRECT("'[EnquiriesDataExport.xlsx]"&A1&"'!$A:$A"),">="&C$1,INDIRECT("'[EnquiriesDataExport.xlsx]"&A1&"'!$A:$A"),"<="&D$1,INDIRECT("'[EnquiriesDataExport.xlsx]"&A1&"'!$C:$C"),$A2)
 

emaildon

New Member
Joined
Jun 1, 2019
Messages
5
Aye that works very well. Thank you. I've been trying to add in an extra INDIRECT to vary the column selection too but can't get it to work. Would you be able to suggest the format? Just as you did with the cell "A1" I'd like to also make "B1" refer to the column "A:A". Thanks for any tips.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,261
Office Version
365
Platform
Windows
Try
=COUNTIFS(INDIRECT("'[EnquiriesDataExport.xlsx]"&A1&"'!"&B1)
 

emaildon

New Member
Joined
Jun 1, 2019
Messages
5
I'm currently looking at nested indirects Am I on the right path?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,261
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,100,138
Messages
5,472,741
Members
406,834
Latest member
RahafKh

This Week's Hot Topics

Top