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

[emaildon]

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

 

[Fluff]

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]

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]

Try
=COUNTIFS(INDIRECT("'[EnquiriesDataExport.xlsx]"&A1&"'!"&B1)

 

[emaildon]

I'm currently looking at nested indirects Am I on the right path?

 

[emaildon]

thanks. we overlapped. I'll try now. Much appreciated. Don

Try
=COUNTIFS(INDIRECT("'[EnquiriesDataExport.xlsx]"&A1&"'!"&B1)

 

[emaildon]

Thanks. I had my " in the wrong places.

 

[Fluff]

You're welcome & thanks for the feedback