Trying to refer to a dynamic cell within a fornmula

[sinasdf]

Hi guys,

I have the following formula:
=SUM(OFFSET(B2,0,0,1,-4))

This formula returns the summed value of all 3 columns to the left of B2 as well as B2.

I'm having an issue whenever I want to change the reference to something else dynamically rather than hardcoding it. So instead of B2, it could be CA99, X15, e.t.c. This cell reference changes based on a location of a certain unique number, so it changes frequently.

Is there any way to do this?

I'm thinking that I have to designate another cell (lets say A1) that will contain the dynamic cell reference.

I've been playing around with CELL and INDIRECT but I can't get anything to work.

 

[James006]

Hello,

all 3 columns to the left of B2

...

What are the 3 Columns to the Left of B2 ...???

Just to confirm ... you can use indirect()

=SUM(OFFSET(INDIRECT(X15),5,0,1,4))

HTH

 

[sinasdf]

Hello,

...

What are the 3 Columns to the Left of B2 ...???

Just to confirm ... you can use indirect()

=SUM(OFFSET(INDIRECT(X15),5,0,1,4))

HTH

Sorry, that was a bad example. Lets use your example...

In regards to the cell reference of X15, what if instead of X15, I want another cell? It's going to be dynamic and changing based on my input. For example, I may have 5 dates, and if put a marker of "x" in a cell, I want that cell to be returned. So it could be X15, Y15, e.t.c.

Shall we use cell X15 to contain that dynamic cell reference?

 

[James006]

Well ...

Thanks to the Indirect() function ...

in your cell X15 ... you can type B4 or C12 ...

HTH