Ideally, I'd like to not delve into VBA with this, cuz I already am not the greatest with EXCEL. Here's my situation, and thank you in advance to anyone who can help!...
Each cell in Row 1 of a sheet called "workload" is an ascending date. So, A1 = 1/1/2016 - B1 = 1/2/2016 - C1 = 1/3/2016, etc...
Elsewhere in another sheet called "legend", I have this formula:
=COUNTIF(workload!$A$2:$AE$66,"some text string")
There are 65 rows of actual non-header data in "workload" (rows 2-66). This formula looks for all instances of "some text string" in any of the cells from A2-AE66 (all of my non-header data). This, in effect, gives me a "date range" (based on the headers in Row 1 described above) of 1/1/2016-1/31/2016 (31 columns traversed...31 days in that date range).
What I want to do is have two other cells somewhere that I can enter a START DATE and an END DATE. So, what I am looking for is a way to make my COUNTIF formula read like:
=COUNTIF(workload!$START_DATE_COLUMN$2:$END_DATE_COLUMN$66,"some text string")
If that is wholly confusing, a practical example would yield the effect of the following, if I entered "1/2/2016" into my START DATE cell and "1/15/2016" into my END DATE CELL:
=COUNTIF(workload!$B$2:$O$66,"some text string")
(because Column B has a value of "1/2/2016" in its header row [B2] and Column O has a value of "1/15/2016" in its header row [O2])
I hope that all made sense, and is possible! Thanks!
This ended up working for me, FYI:
=COUNTIF(INDIRECT("'workload'!"&ADDRESS(1,MATCH(legend!A1,workload!1:1,0))&":"&ADDRESS(66,MATCH(legend!A2,workload!1:1,0))),"Red")
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments