• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel table and offset

L

Lolo

Member
Hello,

I have transformed a range in a table. So in a vlookup formula, i refer to a cell in the Line :
Vlookup([@[mycol]],...

M'y question is : is it possible to easily refer to the cell of the previous line, without the use of offset function ? Like
Vlookup([@[mycol]-1],...

Thanks you
 
Hi, Lolo!
AFAIK it isn't possible. You'd have to use OFFSET:
=VLOOKUP(OFFSET([@mycol],-1,0),...
Regards!
 
Hi, Lolo!
Glad to help you even if it didn't solve our issue, but at least it got clarified. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
@Kyle McGhee
Hi!
I now remember that link :)
But the actual formula shouldn't it be like this, since the previous one gives an error?:
=INDEX(Table1[#All],ROW(Table1[@])-1,COLUMN([mycol]))
Regards!
 
Hi SirJB7
The formula I posted works on my laptop and I can't get yours to work on mine, which I believe could be due to the "@" referencing that is not a part of XL 2007 tables. I also assumed "mycol" was the first column of the table.

try this one...can't test it with the @ referencing in the table though...
=INDEX(Table1[#All],ROW(@[mycol])-1,MATCH("mycol",Table1[#Headers],0))

or =INDEX(A:A,ROW(@[mycol])-1), which would eliminate the need to adjust the -1 offset if the table wasn't in row 1, (and assuming "mycol" is column A)
 
Last edited:
Hi, Kyle McGhee!

I'm running 2010 version now, not anymore 2007, I don't know what I was using on Feb 2012 but I assume it was 2007 since I remember that I got that formula at your link working fine...

One of these days if I happen to stump into a machine with 2007 version I'll give it a try and come back here.
In the meanwhile I used this 2010 form here and it seems to be working:
http://chandoo.org/forum/threads/sumif-sumifs-in-a-dynamic-table.13971/#post-82634

Regards!
 
You must log in or register to reply here.
Back
Top