Add variable row number into formula without VBA

[bukimi]

Hello!

I'm trying to create a table, where one sheet has references to values from other table. The trick is that sometimes one table is longer and sometimes shorter.
Let's say that now my formula in a cell looks like that:

=B3+Values!E16

I want it to be linked to a row that has cell with text "Summary" in the first column, not always to row 16. So, if "Summary" happens to be in row 31, formula should be equal to

=B3+Values!E31

I don't want to add built-in macros into my file, because it is sent to many people who complain about their security alerts then. Recipients can change "Values" table, but if they do that, the second table will have wrong numbers.

Is it even possible to do that with formulas only?

 

[BarryL]

Hi, can you try the below?

=SUM(B3,INDEX(Values!E:E,MATCH("Summary",Values!A:A,0)))

 

[Ramballah]

its done easy with a lookup kinda formula thing however im not experienced with those as i never need cell references. but it will look something like this: =B3+(thelookupformulathing)

 

[bukimi]

Hi, can you try the below?

=SUM(B3,INDEX(Values!E:E,MATCH("Summary",Values!A:A,0)))

That works, I had to experiment a little to make it work accross many columns, but it seems to do what it needs to.
Thank you!