How to make dynamic cell in formula based on pointed value in excel spreadsheet

Question

I have an issue with the cell range in formula and I don't know how to change it based on a predefined value in the spreadsheet. For example, from figure I have cells B8:B12=0 (5 cells), however, if I want to change range to range B10:B12=0 (3 cells) I should delete them from formula. How can I do the reference to a specific cell in a spreadsheet where I can simply change value 5 to 3 and it will change automatically, without interfering formula each time? I'm new to VBA, any help is appreciated.

Answer 1

As it was mentioned before, you should try offset function and do something like: AND(SUM(OFFSET(D13,-1,-2,-(G6),1))=0). Then the cells in the range B8:B12 would be possible to change inserting range in cell G6.

Answer 2

Use the =INDIRECT function to define your target cells, e.g.

=TEXT(INDIRECT(A1), "")

If I entered the text B3 into cell A1, then this formula would return the text value in cell B3.

Let me know if it works for you.