Need Help Using the outcome of the Address Function in the Sum Function

[PleaseHelp2]

I've used the following formula to get the column location of a given piece of data (in cell B1) in another sheet.

=ADDRESS(35,MATCH(B1,'Sheet2'!A4:AE4,0),4)

So L35 is displayed in the cell (lets say it's cell B3)

Now I want to use L35 in the sum function, and I'm stuck.

I need the result of =SUM(Sheet2!B35:L35), but I need to pull the L35 parameter from cell B3 where it is displayed, or using the formula above. The sum will always start with B35, but where the sum function ends is dependent on the Address formula above.

Can anyone help me. I feel like this should be easy, but I can't get it to work.

 

[JackDanIce]

Welcome to the board.

Try:

=SUM(Sheet2!B35:(INDIRECT(B3)))

 

[barry houdini]

Rather than get the address as text and then convert that with INDIRECT you could do the whole thing in one go using INDEX function, i.e.

=SUM(Sheet2!B35:INDEX('Sheet2'!A35:AE35,MATCH(B1,'Sheet2'!A4:AE4,0)))