referencing a sheet with a cell

i want to write a formula where the cell returns the sum of a range on another sheet. the other sheet is identified by matching identical text in another cell to the name of the sheet. the range will be a row. the row is identified by the number in another cell on the same row being less than or equal to a reference cell on our original sheet. Can someone help me write this formula..?

Please attach a sample workbook.

file attached. please see highlighted tabs and numbered comments.

Like this?View attachment Copy of farmer.xlsx

Option, another formula in E3, copy to right and down

both of those are great, but the limitation is that you are writing in "HQ!". I need the sheet to be identified in all instances by the cell in B. I guess the problem is that there is 2 parts to one reference. "HQ!B$2:B$22" means "this" sheet and "that" row, but this and that are referenced in 2 different cells. Any suggestions?

Try =SUM(INDEX(INDIRECT($B3&"!$A$2:$E$22");1;MATCH('base templates'!E$2;INDIRECT($B3&"!$A$1:$E$1");0)):INDEX(INDIRECT($B3&"!$A$2:$E$22");$C3;MATCH('base templates'!E$2;INDIRECT($B3&"!$A$1:$E$1");0))) in E3

Take a moment to understand what the INDIRECT function does and how this formula works.

Thanks Peco, I'll check it out and reply when I get home from weekday prison. I've never used INDEX or INDIRECT and I look forward to learning about them. I have a feeling they will help me with many more of my spreadsheets.

If you have difficulties with INDEX, just ask, it's a function that is not used to it's full potential by lots of people

Peco, are those semicolons supposed to be commas?

Ooops, you're right ! My mistake. Sorry about that

(using XL on Linux, there's no way to use commas)

this worked

Pecoflyer said:

Try =SUM(INDEX(INDIRECT($B3&"!$A$2:$E$22");1;MATCH('base templates'!E$2;INDIRECT($B3&"!$A$1:$E$1");0)):INDEX(INDIRECT($B3&"!$A$2:$E$22");$C3;MATCH('base templates'!E$2;INDIRECT($B3&"!$A$1:$E$1");0))) in E3

Take a moment to understand what the INDIRECT function does and how this formula works.

Click to expand...

So this worked, and I'd like to figure out why a little bit better. I'm not sure what the INDEX function does, but I know INDIRECT summons data from another sheet. That data matches the header label on the 2 pages, and the second page is identified by INDIRECT($B3&"!$A$2:$E$22"). Now, I see the quotations, and according to my memory, the quotations return the text in the reference cell. If we were to fill in the variables it would say INDIRECT(HQ!$A$2:$22), but you have the quotes around the second part. Am I mistaken here? I don't really have a full grasp on the syntax here.

I think this link explains it better than I would

How to work Excel INDIRECT and INDEX functions

proto said:

I'm not sure what the INDEX function does, but I know INDIRECT summons data from another sheet.

Click to expand...

I'll try to you explain my formula.
This formula (with nested INDIRECT function) return SUM first values from respective range on the Sheet HQ

=SUMIF(INDIRECT($B3&"!$B2:B"&$C3+1,TRUE),">0",HQ!B$2:B$22)

Click to expand...

If you look 'Evaluate formula' then you can see the calculation formula step by step.
SUMIF function has the syntax

=SUMIF(range,criteria,sum_range)

Click to expand...

INDIRECT function has the syntax

=INDIRECT(ref_text,a1)

Click to expand...

You can omit the second argument, "a1" ie. 'TRUE' if you want to
Example:
If you putting it in cell A1 of data B3 (ie. address B3 of the cell in the worksheet "base templates"), =INDIRECT(A1) formula will return as a result value or text in cell B3 and this is finally the text 'HQ'

Thus the above mentioned first formula works as follows
If you click the "formula bar 'or press 'F2' you can select the argument (function) and press 'F9' key.
Excel will automatically calculate the result of the respective highlighted function/formula. (See end of this tutorial "Nested function")

So if you highlight a function INDIRECT (argument "range" inside SUMIF function) and press F9 key Excel return next result

by press F9
INDIRECT($B3&"!$B2:B"&$C3+1,TRUE)
will be result
{100,180,324,584}

Click to expand...

The result is the first four values of the sheet ranges from HQ!$B2:B5

'Criteria' is second argument from SUMIF function, This is equivalent to our condition ">0" greater than zero

The last argument of SUMIF function is 'sum_range' and that is the equivalent of 'HQ!B$2:B$22'.
If you again highlight the last argument inside SUMIF function and you press F9 Excel will return as follows.

{100,180,324,584,1052,2104,4208,7574,16832,27773,34085,56808,93733,115036,191727,316350,388248,647081,1067684,1310340,2183900}

Click to expand...

Finally Excel will compare the first and last argument for SUMIF function (of course using the criterion) and sum up values that match.
Then our SUMIF formula looks like this

=SUMIF({100,180,324,584},">0",{100,180,324,584,1052,2104,4208,7574,16832,27773,34085,56808,93733,115036,191727,316350,388248,647081,1067684,1310340,2183900})

Click to expand...

So, you could notice if you look 'Evaluate formula' nested INDIRECT function return a range of cells, depending on the title sheet in the B3 and level value in C3.

INDIRECT($B3&"!$B2:B"&$C3+1) return to HQ!$B2:B5

Click to expand...

When you copy formula down, this data will be changed.

As for the INDEX function, its syntax is

=INDEX(array,row_num,column_num)

Click to expand...

Example:

INDEX($A$2:$B$4,3,2)

Click to expand...

Therefore, the formula will return as a result the data or the information from intersection of the third row of the second column in a respective range.
With this INDEX function is often used MATCH function (as second argument 'row_num') that returns a number of row. You could also use the COLUMN function as the third argument.
I hope that I have succeeded to some extent explain (of course with the link that gave colleague @pecoflyer)

@navic Your explanation about the INDEX function is but a fraction of what this powerful function can do. As you can see in post #7, INDEX is not used to return any cell content.

Strangely enough it is rarely used at it's full potential (for example to return an entire range in a column/row without array formulas ( which I personally try to avoid))

Pecoflyer said:

Your explanation about the INDEX function is but a fraction of what this powerful function can do.

Click to expand...

You're right