Dynamic Range???

[hce]

Dear All

Can anyone explain to me in details and in simple layman terms on how
this formula =OFFSET('1'!$A$1,0,0,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1))
works?

I asked previously how I can have a dynamic range and one kind soul
provided me with this formula but I have no idea how it works.

For example, I have a worksheet named TOTAL and another worksheet named
APN. The data in APN is extracted from a CSV file and has links with it
so the data will be refreshed everytime I refresh it. The purpose of
TOTAL is for me to copy a column of data (Phone No) from APN to TOTAL.
I will paste this column of data in Column A of TOTAL. And after
pasting, I will insert a IF formula in Column B and apply it to all the
rows because I need to see whether there's any duplicates.

I am using a macro to do this for me but because I have no idea how
many records there will be everytime, I put the range as B:B for the IF
formula so that I don't miss out on any records. However with this
method, it would take a long time for the macro to finish calculating
as it has to calculate 65000+ rows... Hence, if someone can teach me on
how to tell the macro to apply the IF formula only until the last
record in Column A, that would be fantastic. Note that the no of
records in Column A will always change with the refresh ie. it could
have 10 records now but 20 records twenty minutes later.

Cheers
kelvin

 

[Bob Phillips]

It's quite straightforward. There are 5 parts to the formula starting after
OFFSET(

'1'!$A$1,
this simply says start at A1 on sheet '1'

0,
the number of rows to offset the start point by

0,
the number of columns to offset the start point

so at this point we are still looking at A1

COUNTA('1'!$A:$A),
COUNTA counts the number of non-empty cells in column A, and this gives us
the row size of our range

COUNTA('1'!$1:$1))
similarly this gives us the number of non-blank cells in row 1, so it
returns our column size

put this all together, and we get the contiguous range starting at A1.

However, I don 't think this is just what you want. You probably need
VLOOKUP to get the number. This would be along the lines of

=VLOOKUP(value, lookup_table,2,FALSE)

where value is the key or id form TOTAL but that appears on TOTAL and APN to
check across with, lookup_table is a dynamic table (as discussed) on APN, 2
is the column within APN that holds the phone number (this is relative to
the key/id so adjust as appropriate), and FALSE ensures an exact match.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Only thing I'd add to Bob's post is that I would always start with the reference
written out in English at the top of the explanation, eg:-

=OFFSET(reference,rows,cols,[height],[width])

Simply type =OFFSET( then hit CTRL+SHIFT+A and it will put it up in English
for you - Then put an apostrophe at the start and hit enter and it will stand
there as a text string, and make a good reference to look at as you work through
creating the formula. I just find it helps to be able to see what the arguments
are and where.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all
----------------------------------------------------------------------------

It's quite straightforward. There are 5 parts to the formula starting after
OFFSET(

'1'!$A$1,
this simply says start at A1 on sheet '1'

0,
the number of rows to offset the start point by

0,
the number of columns to offset the start point

so at this point we are still looking at A1

COUNTA('1'!$A:$A),
COUNTA counts the number of non-empty cells in column A, and this gives us
the row size of our range

COUNTA('1'!$1:$1))
similarly this gives us the number of non-blank cells in row 1, so it
returns our column size

put this all together, and we get the contiguous range starting at A1.

However, I don 't think this is just what you want. You probably need
VLOOKUP to get the number. This would be along the lines of

=VLOOKUP(value, lookup_table,2,FALSE)

where value is the key or id form TOTAL but that appears on TOTAL and APN to
check across with, lookup_table is a dynamic table (as discussed) on APN, 2
is the column within APN that holds the phone number (this is relative to
the key/id so adjust as appropriate), and FALSE ensures an exact match.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ken,

That's a handy shortcut! I would have said it if I had known about it<vbg>

Bob

Only thing I'd add to Bob's post is that I would always start with the reference
written out in English at the top of the explanation, eg:-

=OFFSET(reference,rows,cols,[height],[width])

Simply type =OFFSET( then hit CTRL+SHIFT+A and it will put it up in English
for you - Then put an apostrophe at the start and hit enter and it will stand
there as a text string, and make a good reference to look at as you work through
creating the formula. I just find it helps to be able to see what the arguments
are and where.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Seasons Greetings and Very Best wishes to all
-------------------------------------------------------------------------- --

 

[Ken Wright]

Another little nugget I gleaned from one of John's books

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all
----------------------------------------------------------------------------

Ken,

That's a handy shortcut! I would have said it if I had known about it<vbg>

Bob

Only thing I'd add to Bob's post is that I would always start with the reference
written out in English at the top of the explanation, eg:-

=OFFSET(reference,rows,cols,[height],[width])

Simply type =OFFSET( then hit CTRL+SHIFT+A and it will put it up in English
for you - Then put an apostrophe at the start and hit enter and it will stand
there as a text string, and make a good reference to look at as you work through
creating the formula. I just find it helps to be able to see what the arguments
are and where.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Seasons Greetings and Very Best wishes to all
-------------------------------------------------------------------------- --

It's quite straightforward. There are 5 parts to the formula starting after
OFFSET(

'1'!$A$1,
this simply says start at A1 on sheet '1'

0,
the number of rows to offset the start point by

0,
the number of columns to offset the start point

so at this point we are still looking at A1

COUNTA('1'!$A:$A),
COUNTA counts the number of non-empty cells in column A, and this gives us
the row size of our range

COUNTA('1'!$1:$1))
similarly this gives us the number of non-blank cells in row 1, so it
returns our column size

put this all together, and we get the contiguous range starting at A1.

However, I don 't think this is just what you want. You probably need
VLOOKUP to get the number. This would be along the lines of

=VLOOKUP(value, lookup_table,2,FALSE)

where value is the key or id form TOTAL but that appears on TOTAL and APN to
check across with, lookup_table is a dynamic table (as discussed) on APN, 2
is the column within APN that holds the phone number (this is relative to
the key/id so adjust as appropriate), and FALSE ensures an exact match.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dear All

Can anyone explain to me in details and in simple layman terms on how
this formula =OFFSET('1'!$A$1,0,0,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1))
works?

I asked previously how I can have a dynamic range and one kind soul
provided me with this formula but I have no idea how it works.

For example, I have a worksheet named TOTAL and another worksheet named
APN. The data in APN is extracted from a CSV file and has links with it
so the data will be refreshed everytime I refresh it. The purpose of
TOTAL is for me to copy a column of data (Phone No) from APN to TOTAL.
I will paste this column of data in Column A of TOTAL. And after
pasting, I will insert a IF formula in Column B and apply it to all the
rows because I need to see whether there's any duplicates.

I am using a macro to do this for me but because I have no idea how
many records there will be everytime, I put the range as B:B for the IF
formula so that I don't miss out on any records. However with this
method, it would take a long time for the macro to finish calculating
as it has to calculate 65000+ rows... Hence, if someone can teach me on
how to tell the macro to apply the IF formula only until the last
record in Column A, that would be fantastic. Note that the no of
records in Column A will always change with the refresh ie. it could
have 10 records now but 20 records twenty minutes later.

Cheers
kelvin