INDIRECT with multiple rows

[dluhut]

Hello Excel Experts,

Given the table below, is it possible to achieve the result as shown in the 3rd column.

I need it so that I can use the INDIRECT formula for it.

Row Number	Column Text	Desired Formula String
19:20,22:24,34	AA	AA19:AA20,AA22:AA24,AA34
19,20,34	BC	BC19,BC20,BC34
33,37:41	D	D33,D37:D41

[FDibbins]

Other than doing the "conversion", what exactly are you trying do do here?

[dluhut]

I need to use SUM(INDIRECT(...)).

[Jonmo1]

I don't see that working.

If your end resulting sum is this
=SUM(AA19:AA20,AA22:AA24,AA34)

Even if you do successfully concatenate the string (you can, see below)
=B2&SUBSTITUTE(SUBSTITUTE(A2,",",","&B2),":",":"&B2)
And that resulting string would in fact work in a SUM function...
However it will not work in an INDIRECT function.
INDIRECT can only create a single range reference at a time.

You would need 3 separate indirect functions in the SUM.
1 for AA19:AA20
1 for AA22:AA24
1 for AA34

SUM(INDIRECT(..AA19:AA20),INDIRECT(..AA22:AA24),INDIRECT(..AA34))

[Jonmo1]

Is there anything in another column that can be used in a SUMIF function that flags which rows to sum?
In your head, how did you choose 19:20 22:24 and 34 ?
Was it completely absolutely random? Or was there some factor about those rows that made you choose them? THAT logic can probably be used in a sumif.

[dluhut]

The reason why I'm using the INDIRECT function is because I'm trying to do a SUM of a range of worksheets. And the reason why the row numbers are like the above is because, sometimes if user added/deleted rows, they'd then be able to just change the rows and the SUM(INDIRECT)) formula will automatically works.

I tried to do SUMIF in a 3D, but then again, users like to move in/out some sheets from the sheet range, and thus SUMIF 3D format won't work.

As well, because the data is listed of the General Ledger, and for each general ledger can appear in multiple 'categories', thus the reason why I just go with the user criteria and that is by inputting the row numbers in the cell, with comma delimited, to produce a ref_text in which then I was thinking of then uses the INDIRECT function to feed into the SUM function

[dluhut]

If your end resulting sum is this
=SUM(AA19:AA20,AA22:AA24,AA34)

Even if you do successfully concatenate the string (you can, see below)
=B2&SUBSTITUTE(SUBSTITUTE(A2,",",","&B2),":",":"&B2)
And that resulting string would in fact work in a SUM function...

I tried using the function above and then use the SUM, but it didn't give me any result.

What did I do wrong here?

[Jonmo1]

I said it wouldn't work.

I posted the SUM formula as a 'guessed' example of the ultimate sum you you wanted.
Even if you did create a string to represent your ranges, it wouldn't work with indirect.
When I said that exact string would work in a SUM, I meant that if you typed it by hand. Not from the result of a formula.
You can only do that with INDIRECT.
but again, indirect can't use that multi range string.

Hope that cleared it up (like mud right?)