SOLVED

Auto updating ranges based on different cell values

New Contributor

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!

 

4 Replies
Where are the "cell values" based on which the range AA9:AA29 should be modified?

@Doug_Robbins_Word_MVP 

 

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.

best response confirmed by wowakan (New Contributor)
Solution

@wowakan 

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