Offset from cell referenced in a formula

[Bengo]

Evening,

I am trying to figure out how to offset based on a cell referenced in a formula

So for example if Cell B5 had the formula: "=B20", I would want in another cell, say cell A1 to look at B5, and return the cell one column to the right, e.g B21

Hope this makes sense!

Thanks in advance,
Ben

 

[Joe4]

How is the formula in cell B5 being populated?
Is it always B20, or can it change?
Is there any logic behind which cell it pulls from?

 

[Bengo]

How is the formula in cell B5 being populated?
Is it always B20, or can it change?
Is there any logic behind which cell it pulls from?

Hi Joe,

that is a fixed reference to cell B20 and will never change. I'm basically try to get a formula I can copy and paste that says:

1. Look at the cell above
2. See where it is referencing to (e.g B20)
3. Link to the cell which is one column to the right of that cell (e.g C20)

Does that make sense?

thanks,

 

[Joe4]

What I am wondering is if it never changes, why not just enter the formula =C20 in the other cell?

 

[Bengo]

What I am wondering is if it never changes, why not just enter the formula =C20 in the other cell?

Ah I see! I can, but I need to go down a sheet and do this about 200 times, so rather than typing it all by looking at which cell the one above refers to I was hoping to use a formula to copy and paste

 

[Joe4]

So, the first cell is B20, and it changes as it goes down?
What are the next few cells?
Is there a pattern?
If so, we should be able to create a formula that would work for that as well.

 

[Bengo]

So, the first cell is B20, and it changes as it goes down?
What are the next few cells?
Is there a pattern?
If so, we should be able to create a formula that would work for that as well.

Ok great thanks

I had used these cell example to keep it simple but here are a few sets of the real data:

C89 = G4 need C90 = H4 and C91 = I4
C100 = G7 need C101 = H7 and C102 = I7
C111 = G10 need C112 = H10 and C113 = I10

so 11 rows between the sequence looping, and the reference cells drop by 3 each sequence

Hope this all makes sense!

Thanks again!

 

[Joe4]

Are you opposed to a VBA solution for populating these cells with formula?
We could whip up something to make it really easy.
Is there any logic for how far down the sheet we need to go?

 

[ndsutherland]

Joe4, could he use GET.CELL?

 

[Joe4]

Joe4, could he use GET.CELL?

Perhaps. My concern is, it would still be a somewhat manual process, having to copy and paste these formulas every 11 rows. With VBA code, he could populate everything with a single-click of the button. Here is some code that would do that:

Sub MyPopulateFormulas()

    Dim FormulaRow As Long
    Dim RefRow As Long
    Dim EndRow As Long
    
'   Set initial values
    FormulaRow = 89
    RefRow = 4
    EndRow = 200
    
    Do Until FormulaRow > EndRow
'       Populate three formulas
        Cells(FormulaRow, "C").Formula = "=G" & RefRow
        Cells(FormulaRow + 1, "C").Formula = "=H" & RefRow
        Cells(FormulaRow + 2, "C").Formula = "=I" & RefRow
'       Increment variables
        FormulaRow = FormulaRow + 11
        RefRow = RefRow + 3
    Loop
    
End Sub

Just change "EndRow" to the last row number you want to populate these formulas down to.