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
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.
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