Can you tell me what's going on here?
 

Hi,
I'm studying a sheet that someone else has made with the prospect of
creating a new sheet in the same format but with different data. Can anyone
explain to me what is going on in these vlookups, specifically the ROW
references? How does this work? Thanks!

=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(18:18)-ROW($4:$4),FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(31:31)-ROW($4:$4)+1,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(62:62)-ROW($4:$4)-2,FALSE)

Can you tell me what's going on here?
 

ROW(18:18) returns 18
ROW($4:$4) returns 4

The ROW function is covered by Excel help, as are all other Excel functions
except DATEDIF.
--
David Biddulph

"J.Mart" wrote in message
...
Hi,
I'm studying a sheet that someone else has made with the prospect of
creating a new sheet in the same format but with different data. Can
anyone
explain to me what is going on in these vlookups, specifically the ROW
references? How does this work? Thanks!

=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(18:18)-ROW($4:$4),FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(31:31)-ROW($4:$4)+1,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(62:62)-ROW($4:$4)-2,FALSE)

Can you tell me what's going on here?
 

ROW($4:$4) just returns 4, so the equivalent would be:

=VLOOKUP(F$1,L.1_d!$1:$65536,14,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,28,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,56,FALSE)

The only difference will be that if a row is inserted in sheet L.1_d,
the cells with the ROW() functions will adjust - so if a row was
inserted at row 35, the first two functions will remain the same, but
the last would become

=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(63:63)-ROW($4:$4)-2,FALSE)

That said, with the info you've provided, it's impossible to tell if
that would be logical, or whether it's just arcane and prone to error.

In article ,
J.Mart wrote:

Hi,
I'm studying a sheet that someone else has made with the prospect of
creating a new sheet in the same format but with different data. Can anyone
explain to me what is going on in these vlookups, specifically the ROW
references? How does this work? Thanks!

=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(18:18)-ROW($4:$4),FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(31:31)-ROW($4:$4)+1,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(62:62)-ROW($4:$4)-2,FALSE)

Can you tell me what's going on here?
 

Replace: ROW(18:18)-ROW($4:$4)
with: 14*2^(ROWS($1:1)-1)
and copy down


"J.Mart" wrote:

Hi,
I'm studying a sheet that someone else has made with the prospect of
creating a new sheet in the same format but with different data. Can anyone
explain to me what is going on in these vlookups, specifically the ROW
references? How does this work? Thanks!

=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(18:18)-ROW($4:$4),FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(31:31)-ROW($4:$4)+1,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(62:62)-ROW($4:$4)-2,FALSE)