Cell location, offset

[MartinKI]

I am looking for a formula which gives me the location of the cell in the current worksheet, in combination with an offset formula.
Anyone an idea how to solve this?

example:
I have 3 identical tables underneath each other, exactly similar.
in the left corner of the table is the name of that portfolio.

10 rows under that name and 1 column to the right is the total, which I am interested in.

now I am looking for a formula like this:

=offset(cell reference,9,1)
The problem (and my question) is to come up with a flexibel formula which gives me the cell reference (say A1) based on the lookup function on the name of the portfolio, which is located in the left corner.

Tx.

 

[Peter_SSs]

Martin

Does this do what you want?
=INDEX(B:B,MATCH("portfolio name",A:A,0)+9)

 

[MartinKI]

I still need to make a step (or actually 3 steps) to the right.....

sorry....i didn't look carefully enough....I see you reffering to B:B

let me have a look

 

[Peter_SSs]

I still need to make a step (or actually 3 steps) to the right.....

sorry....i didn't look carefully enough....I see you reffering to B:B

let me have a look

In my formula, change B:B to the column the totals are in and change A:A to the column the portfolio names are in.

 

[MartinKI]

Yes It worked! thanks....although the column B:B needs to be a table to function properly:

my final formula:
=INDEX(B1:G45;(MATCH(A1;G1:G45;0)+8);6)

second question though.....is there a formula which simply gives me the cell location as result?? so A12 instead of the value in A12??

 

[Peter_SSs]

Since we appear to know what column it is in, would something like this do what you want?
="G" & MATCH(A1,G1:G45,0)+8

 

[MartinKI]

Yes, that will work if you know what column it is in...this was more meant as a general question....but tx anyway.

I thought something like =ADDRESS(...

 

[Jonmo1]

Perhaps then,

=ADDRESS(MATCH(A1,G1:G45,0)+8,7)

 

[MartinKI]

Tx...that will be helpfull in future formulas..