Excel function required that's not quite offset ?

[jamesbird]

I've two worksheets in a workbook, sheet2 is stuffed full of data, sheet 1 allows me to view selections of the data.
When I make e.g. sheet1 D4 =Sheet2!G15, I'd like D5 to show the value in sheet2!H16.
Now the cleaver bit - when I change the reference in D4 to any cell in sheet2, I'd like D5 to automatically pick up the value 'one down and one to the left'.
I hope that's clear.
I feel it ought to be possible with offset, but my poor brain can't get it together.
Any ideas please
James

 

[Markus4]

Hi jamesbird,

my poor brain cannot get it together either. I have another feeling: there is an

activecell.ShowDependents

So Excel knows about the cells the ActiveCell gets its value from (it should, shouldn't it).
But I cannot think of how to get to the results of this method, neither in VBA nor on the sheet. Hope it helps anyway.

Regards,

Markus

 

[GlennUK]

When you say that you want cell D5 to "automatically pick up the value 'one down and one to the left'", what do you mean exactly by "automatically"? ( and, surely you mean "one down and one to the right"? )

If you put this in D4:
=OFFSET(Sheet2!$G$15,ROW()-4,ROW()-4,1,1)
it will fetch from G15, and if you drag the formula down into cell D5, it will fetch from H16 ( but, I don't know if that's what you mean by automatically, as asked above ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Loomah]

not sure this is possible without VBA so here's a udf to play with. this can be extended to take params for number of rows and cols to offset by etc etc

i also haven't included any error handling to check that the offset value will stil actually fall within a worksheet (ie if you ref a cell in col a using this function it should fail)

Function OffsetFormula(rng As Range) As Variant
Dim sForm As String
Dim sSheet As String, sRef As String
Dim i As Integer, res

    sForm = Right(rng.Formula, Len(rng.Formula) - 1)
    i = InStr(1, sForm, "!")
    sRef = Right(sForm, Len(sForm) - i)
    
    If i <> 0 Then
        sSheet = Left(sForm, i - 1)
        OffsetFormula = Worksheets(sSheet).Range(sRef).Offset(1, -1)
    Else
        OffsetFormula = Range(sRef).Offset(1, -1)
    End If
    
End Function

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

This is simply...
[tt]
=OFFSET(Sheet2!G15,1,1,1,1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...or shorthand...
[tt]
=OFFSET(Sheet2!G1,1,1)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbird]

Thanks guys, funny how inadequate words are to describe a problem, if you looked over my shoulder I could show you in 30secs what I’m trying to do.
I'll need to be a bit fresher to see if the VB scrip kindly added by Loomah does the biz - thanks for doing that.
The problem with the simple offset Glenn and SkipVought, is that the 1st argument is fixed.
Let me try again to describe the problem.
In the 1st cell I’d like to enter a reference to the other sheet at random by clicking in the cell and typing for instance =sheet2!g12, I’d like the data in the 2nd cell to automatically reflect the data in sheet2 ‘one down and one to the right’ of the cell referred to in cell1, so a sort of =the location referred to in cell 1 plus an offset. Cell 1 can refer to any cell in sheet2, cell 2 dynamically (perhaps that’s a better word than automatically) refers to a cell in sheet 2 that is a fixed offset from that referred to in cell1.
Is that any clearer?
I think the 'at random' bit means the 1st argument in the offset function would have to be picked up from the reference in the 1st cell - and I don't think that can be done.
Thanks for looking and thanks for trying to understand what I'm trying to do

 

[SkipVought]

The problem with the simple offset Glenn and SkipVought, is that the 1st argument is fixed.

That is no problem at all...
[tt]
=OFFSET(Sheet2![red]$[/red]G[red]$[/red]1,1,1)
[/tt]
or whatever reference you want to ANCHOR the offset function to.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbird]

Sorry Skip, I've still not made myself clear - I think the 1st argument of offset can only be fixed, I need it to be dynamically assigned - may be G1 might be F7, could be zz233 - depends on what goes in cell1. And the point is when cell1 is changed, the '1st argument' needs to change.
Thanks

 

[SkipVought]

That's what we previously had, is that not so?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jamesbird]

Hi Skip, then I don't understand how the function works. I need:

In sheet1 contents of cellA1 are literally =sheet2!G15 cell A2 contains =(offset"the cell referred to" in A1,1,1)
So that when I make A1 e.g. za321 I don't have to update the equation in A2, the magic automatically picks up the content of zb322.

I think your use of the offset function means that I have to manually update two cells.
Thanks for persisting with this.

 

[Loomah]

hi jamesbird
a couple of things with my suggestion
there is a remnant of me testing this left in the code - res does not need to be declared anywhere as it isn't used! no biggy if you leave it in tho.

secondly, in your original query you stated you needed to offset 1 column to the left. Glenn questioned this but i stuck with your actual requirement. you more recently state it should be 1 col to the right. if that is the case then the vb offset will need to be changed (2nd argument from -1 to 1)

as for getting your solution without vba the only way i can think of is to not enter your main formula as a formula but rather enter the 2 parts into different cells and use indirect
eg
in a1 enter ''sheet2'!
note that this is led by 2 apostrophies (?spelling??) ie ' & ' and not double quotes ("). drop them altogether if there are no spaces in your sheet names.
in a2 enter f4 (or whatever)
these are where you would change your lookup range
in cell a3 enter =indirect(a1&a2)
this is your first lookup value
in cell a4 enter =offset(indirect(a1&a2),1,1)
this is your offset value

this is probably less fiddly than editing the formula too!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[jamesbird]

Loomah, I like it! =indirect that's the baby.
I think that is what I was trying to get to - I'll get my Excel pencil out later and give it a go.
I realise if I knew my left from my right it'd make it easier! I'll infact have a group of 'secondry cells' picking up data from 'around' the movable cell, so it was an example only. But not helpfull as an axample I realise.
Thanks, I'll post when I've tried