Using Text and "&" function to link together spreadsheet paths in a VLOOKUP lookup formula, Using =LEFT formula within COUNTIFS?

[Marmot329]

Hi All,

Hoping for some help in making a VLOOKUP a bit more dynamic. Below is a screenshot of my spreadsheet (apologies for the grainy-ness - the picture seems to lose a bit of quality when I upload it)

In columns AB, AC, AD etc, I am going to use the COUNTIFS function, to count the number of breaches that have been recorded in a separate spreadsheet. Instead of hard-coding the VLOOKUP as per what I have done below however in cell AC4, I want to link the path to the text in Row 1 "Jan16, "Feb16" etc etc - these are the tab names in the sheet which I am counting the data from.

[/URL][/IMG]

I tried to use a simple version of the formula to test this (as per the below screenshot, cell AC5), but i am getting a message saying that the formula i typed contains an error...



Is there a particular way to build paths up using the & symbol that works?

Here is an example of the data i am referencing in the COUNTIF:



This brings me to the second part of my problem. The criteria for the COUNTIFS is currently based on column E in this spreadsheet (amongst other straight forward criteria) which is a simple =LEFT function, referencing column D. Column D contains a message that has basically been input into a system, so will always be unique, however the message with always begin either "OK", "SOFT" or "BREACH". I want to COUNTIF, if the left two letters are either OK, SO or BR - is there a way do do this in COUNTIFS without having to seperately maintain column E?

Any wisdom with these issues is much appreciated as always

 

[Asala42]

I think what you're going for requires the INDIRECT function.

EG:

=COUNTIF(INDIRECT("'VMGR (Latest).xlsx"&AC1&"'!$C$C"),$B$5)

My understanding is the target file needs to be open unfortunately, though I'm not sure if that's still true with the latest versions of excel. There is also an INDIRECT.EXT function as well though (in the MOREFUNC add in) that was designed to work on closed workbooks.

 

[Marmot329]

Thank you. It allows me to put the formula in now, but it's pulling back a #REF! error.

I dont know too much about this formula, so i will go and do some research and see if i can get it to work.

 

[Marmot329]

Also just to add, the target file will be opened automatically using a sub auto_open() macro when i have finished the sheet, so that shouldn't cause me any issues.

 

[Marmot329]

*Deleted*

 

[Marmot329]

I just wanted to follow up on this thread with my solutions, in case it should help anyone in future

For the problem with utilising a text string in a seperate cell, to create a path with my CountIf formula, i did indeed use the Indirect formula as was suggested above. I found some useful tips on the YouTube channel 'ExcelIsFun', Excel Magic Trick number 913 to give me a basic example on how to approach the indirect formula, as it was giving me a headache with all the quotation marks and where they needed to go.

A really helpful tip I picked up, was to create the formula manually, as i have above, and then once you have done this, to wrap the part of the formula which contains the text string you want to link with the indirect formula, including a " at both the beginning/end of the text. THEN highlight the bit you want to replace with the cell reference (in my case the text Feb16), delete, and type "&&" - between the two &'s, this is where you link your cell reference (in cell AC1 for me). Watching a simple video really helped me get my head around using this formula.

https://www.youtube.com/watch?v=cjBEBn6YMi0

This is the formula I ended up with:
=IF($Y5="","",COUNTIFS(INDIRECT("'[VMGR (Latest).xlsx]"&AF$2&"'!$C:$C"),$B$6,INDIRECT("'[VMGR (Latest).xlsx]"&AF$2&"'!$B:$B"),$Y5))

 

[Marmot329]

With regards to amending my countif to look at the first two letters of a cell using the left function, i found the following examples another user had posted useful for this. There are wildcards available for searching cells that begin with, or contain.

Use =Countif(A1:A100,"Br*" for 'Begins with'
Use =Countif(A1:A100, "*Br*" for 'Contains'

Unfortunately I can't find the thread, but it was another on here which I took a screenshot of. Thank you to Mark W. if you are reading, for the useful tip !