Forum Discussion
Auto updating ranges based on different cell values
Dear Help Board,
I have tried unsuccessfully to figure this out and am wondering if it can only be done with VBA or a macro?
I have the following formula and want to dynamically update the values AA9 and AA29 to either expand or contract my range in column AA.
=COUNTIFS(AA9:AA29,">0", AA9:AA29,"<1")
I also tried defining the ranges and named them SD_21, SD_13, etc. However, I do not know how to automatically insert either "SD_21, SD_13, etc" into the below.
=COUNTIFS(SD_21,">0", SD_21,"<1")
Could really use your help...both the above work however, I want an easier way to change the range size from another cell value.....thank you!
=COUNTIFS(INDIRECT(T23&":"&T24),">0",INDIRECT(T23&":"&T24),"<1")
Is this what you want to do? You can manually change the range by entering e.g. AA14 and AA29 in cells T23 and T24 as shown in the attached file.
4 Replies
=COUNTIFS(INDIRECT(T23&":"&T24),">0",INDIRECT(T23&":"&T24),"<1")
Is this what you want to do? You can manually change the range by entering e.g. AA14 and AA29 in cells T23 and T24 as shown in the attached file.
- Thank you so much. I could not wrap my thoughts around the proper syntax. This is exactly what I was looking for!
All the best to you.
- Where are the "cell values" based on which the range AA9:AA29 should be modified?
Thanks for the reply. The cell values are currency values. I simply want to make a formula where I can adjust the range from ex. 1 to 10 from another cell that contains the values 1 and 10. Is this possible?
Then by altering the cell values let's say from 1 to 10 I can adjust the range for example to 4 to 6 in the same row.
Thanks for any help you can offer as this has me stumped.
