Hi all @ Ozgrid,
I've a spreadsheet I'm expanding and need to help with formulas that I can't quite wrap my head around.
In the attached example, sheet 3 there are 3 adjacent cells where I want to pull information from sheet 1 using an Index/Match formula to return values based on the intersecting cells. I have added a description to each of the cells in the attached that fully explains what I am trying to achieve, but in summary it is this:
Sheet 1:
Has a table array, with dates 1st Jan through 31st Dec in row 1:1. Beneath this row cells may or may not contain data.
Sheet 3:
Column 1 has a list of values, these are consistent between sheet 1 & sheet 3 so can be used to index the results.
The user will specify a date range in sheet 3 in cells C2 & C3 respectively for a "From" and "To" date range. I.e. the user may want to specify a range from 01/01/2018 to 31/01/2018.
I want to calculate the following based on that specified date range:
- In column C, check whether any cell in the range of intersecting cells on sheet 1 are blank or not, and return a Yes/No answer. (i.e. if date range 5th Jan to 15th Jan contains even one cell that is not blank, a positive result will return)
- Conditionally format the active cell based on this answer (so a similar formula as point 1 to be applied to a conditional format)
- In the 1st adjacent cell (column D in the attached example), count the number of non blank cells in the specified range and return the result
- In the next adjacent cell to that (column E in the attached example), display the date, or the dates where the cells are non blank
I've a concept floating in my mind about combining the following formulas, except I can't see how to nest them with an index search to yield the results I'm after;
COUNTIFS(, ">="&$C$2,Sheet1!F$1:AJ$1,"<="&$C$3)
to calculate the result of point 1 & 2.
COUNTIF(Sheet15:5,"<>"&"")
To count the non blanks in the same range of dates for point 3
... and I have absolutely no clue how to achieve point 4 on the list!
Can anyone help me with the above? I have a feeling I'm on the right track but I can't figure out how to match the date ranges specified to the array of row 1:1 in sheet 1.
Hope this all makes sense, if not let me know and I will clarify as best I can.
As always, thanks to all for looking! :thanx: