CODEWITHSUNDEEP
exceldynamicrangenon-volatile
cryptic989
cryptic989

Reputation: 1

Excel - Dynamic ranges without volatile formulas or VBA

I am trying to redo a particular sheet that tracks the status of certain things for each of our clients. I had this built using OFFSET but I want to know if there's a way to do it without volatile formulas or VB script.

The challenging part are the dynamic ranges. They need to be dynamic so that I can enter additional items within a category, or remove them, as need be. The cells in these dynamic ranges will be text. I am checking the cells for keywords indicating severity (ex. good, so-so, bad, very bad), and then displaying the most severe result in a Status header row for that category (which I was doing with nested IF's).

See the image below.

example

Thus, for Company A, Category Y, the range within which I check for keywords in column D will be D8:D10, and the cell displaying the most severe result is D7, and so forth. If I add Item A4 the range should adjust.

Is this possible without OFFSET or INDIRECT?

As stated earlier, I had the dynamic ranges using OFFSET, then tried with ADDRESS & INDIRECT, but realized INDIRECT is also volatile. Looked at various topics but couldn't find anything specific enough to apply here.

Any help is appreciated.

Upvotes: 0

Views: 745

Answers (1)

skepticologist
skepticologist

Reputation: 11

The INDEX function can replace OFFSET. I'm not sure how you structured your OFFSET or IF functions, but the following will work subject to tweaking:

Company A 2022-01-01
--------------------
=$D$8:INDEX($D$8:$D$11,COUNTA($D$8:$D$11),1)

Upvotes: 1

Related Questions