Get the 'row' number from a text in a given cell

[dluhut]

Hello Excel Gurus,

Given a 2 sheets below, how do I incorporate to INDIRECT formula or any alternatives that can capture the 'sheet name' as well as the column and row.

As for the row, the formula needs to have the intelligence to do a SUM based on the text on column F on 'Output Sheet'.

For examples:
row 1: cell F1 input cell is 1. Thus, for each A1 to E1, take the value/sum the value that's in 'DataSheet' of the respective cells. i.e. 'OutputSheet'!A1 = 'DataSheet'!A1 … 'OutputSheet'!E1 = 'DataSheet'!E1
row 2: cell F2 input cell is 1,3. Thus, for each A2 to E2, sum the value that's in 'DataSheet' of the respective cells. i.e. 'OutputSheet'!A2 = SUM('DataSheet'!A1, 'DataSheet'!A3) … 'OutputSheet'!E2 = SUM('DataSheet'!E1, 'DataSheet'!E3)
row 3: cell F3 input cell is 1:3,5. Thus, for each A3 to E3, sum the value that's in 'DataSheet' of the respective cells. i.e. 'OutputSheet'!A3 = SUM('DataSheet'!A1:A3, 'DataSheet'!A5) … 'OutputSheet'!E3 = SUM('DataSheet'!E1:E3, 'DataSheet'!E5)
row 4: cell F4 input cell is 1:2,4:5. Thus, for each A4 to E4, sum the value that's in 'DataSheet' of the respective cells. i.e. 'OutputSheet'!A4 = SUM('DataSheet'!A1:A2, 'DataSheet'!A4:A5) … 'OutputSheet'!E4 = SUM('DataSheet'!E1:E2, 'DataSheet'!E4:E5)

DataSheet

Row\Column	A	B	C	D	E
1	10	20	30	40	50
2	15	25	35	45	55
3	5	4	3	2	1
4	20	40	55	30	10
5	10	0	10	0	10

OutputSheet

Row\Column	A	B	C	D	E	F (user input)
1	10	20	30	40	50	1
2	15	24	33	42	51	1,3
3	40	49	78	87	116	1:3,5
4	55	85	130	115	125	1:2,4:5

[tim201110]

how many rows can be in general?
how many "ranges" can be in column F?

[dluhut]

how many rows can be in general?
Are you referring to how many rows in total? If that's what you're referring to, I'd say maximum 1000 rows

how many "ranges" can be in column F?
It can varies. for example, the 2nd example where I only had 1,3 in reality, it can be 1,3,5,10 etc. Or the last example, where I only had 1:2,4:5 it can be 1:2,4:5,8,10,13:45 etc. It all depends on how the user wants to add.

I know that INDIRECT formula works, BUT only to 1 single digit. When it becomes a text, I'm really out of clue.

Macro/VBA is out of option because higher up isn't a technical person. Even a simple 'enable content' does not know how to click.

[tim201110]

it can be done with quite complicated logic and a row with all of your some 1000 rownumbers, or an UDF
in this case why not just mark needed rows?

[dluhut]

I don't understand what you meant by 'mark needed rows'?

But isn't 1,3 or 1:2,4:5 the same as mark needed rows. The only difference is that in that particular row, what does it needs to add the row to.

The purpose here is that, sometimes, taking example 2 as an example, they'd like to be 1,3. But at the same time, they'd like it to be 1,3,5,10 for example.

So what they want is the flexibility of having the rows that they can manipulate.

And the above is just an example, if the formula works, I can then 'roll' it over to others as well...notice that right now is 'DataSheet', when in actuality, there's about 20 DataSheets.

[tim201110]

How do you pick up needed rows?

[dluhut]

Text that's being keyed in column F (i.e.1,3 or 1:3,4:5) are what I need the rows to be in the formula (i.e. INDIRECT).

As to the 'HOW', I don't know. That's why I ask in this forum.

All I'm asking is, given the example of the 1st thread, what function(s) do I need in order to pick up the rows that are being input by the user and then SUM them up.

[tim201110]

I don't know

at random?

[dluhut]

yes at random. Because it's a user input.

However, the way how user will input is how the SUM function arguments will be.

In a sense, it'll always be ':' and/or ','

A good example is 1:2,4:5,8,10,12:14.

So assume if a user input the above string in cell F100, if I'm going to put into a sum formula, it'll be

'OutputSheet'!A100 = SUM('DataSheet'!A1:A2, 'DataSheet'!A4:A5, 'DataSheet'!A8, 'DataSheet'!A10, 'DataSheet'!A12:A15)

'OutputSheet'!B100 = SUM('DataSheet'!B1:B2, 'DataSheet'!B4:B5, 'DataSheet'!B8, 'DataSheet'!B10, 'DataSheet'!B12:B15)

'OutputSheet'!C100 = SUM('DataSheet'!C1:C2, 'DataSheet'!C4:C5, 'DataSheet'!C8, 'DataSheet'!C10, 'DataSheet'!C12:C15)

'OutputSheet'!D100 = SUM('DataSheet'!D1:D2, 'DataSheet'!D4:D5, 'DataSheet'!D8, 'DataSheet'!D10, 'DataSheet'!D12:D15)

'OutputSheet'!E100 = SUM('DataSheet'!E1:E2, 'DataSheet'!E4:E5, 'DataSheet'!E8, 'DataSheet'!E10, 'DataSheet'!E12:E15)

[tim201110]

=SUM(IFERROR(N(OFFSET(INDIRECT($F$1&A$1),IF($G2:$DB2>0,$G$1:$DB$1-1,""),)),))
for 100 rows
it can be easely expanded up to 4000

[dluhut]

Wow tim201110, although it's not exactly what I want, but it's very close to what I need.

I'll try to see if I can make some adjustment to it.

Reps up to you!

[dluhut]

Hi Tim,

I realized that the 'FIND' functions will make it as a true for when a user input 15 and 115 or 1015.

What would be the solution for this?

[JeteMc]

Please provide an example of the issue, showing both the results Tim's formulas yield and the results that you expect.
Let us know if you have any questions.

[tim201110]

just add a space before numbers and after separators
add a spaces to formulas