=offset and dynamic range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

H
Looking at Chip Pearson's site on dymaic ranges has left me somewhat confused (But thats usual for me). I downloaded his sample spreadsheet and it looks like this is what I need to use. However, Im not sure where to actually use the =offset and how the range A1:A25 is named. Thanks for any help you may offe
Steve
 
steveh wrote...
Hi
Looking at Chip Pearson's site on dymaic ranges has left me somewhat
Click to expand...
confused (But thats usual for me). I downloaded his sample spreadsheet and
it looks like this is what I need to use. However, Im not sure where to
actually use the =offset and how the range A1:A25 is named. Thanks for any
help you may offer

Steve,

Have a look at Insert | Name | Define....and that is where Chip defined the
names. What he is doing is "anchoring" the name on the first cell (A1), and
then simply using the CountA function within the offset function to
determine the number of rows to be included in DynaRange. So A2:A25 is not
part of the range per se. A2:A25 just specifies WHERE CountA should look
for count the non-blank cells. Look at his "Last Cell" and you will see
that it is 10, indicating that there are only 10 rows of data, not 24 rows.

Hope that helps. Post back with further questions.

Best regards,
Kevin
 
steveh wrote...
Thanks Kevin....
I would have thought the name would show up in the name block if I
Click to expand...
highlight A2:A25. Where does the offset formula go?
Thanks again
Click to expand...

No, it should NOT show up with A2:A25 highlighted because of two reasons:

1) dynamic ranges do not show up as they are not fixed.

2) A2:A25 is not the range. DynaRange is A2:A11 as shown in Chip's
spreadsheet.

The offset formula is typed into the equation in Insert | Name | Define.

For DynaRange...
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$25),1)

The magic of the dynamic range is that it "senses" the amount of data
present, and adjusts itself accordingly. So in our case, it uses A2 as the
anchor, and then counts the number of non-blank cells in A2:A25, and extends
itself downward from A2 accordingly. It uses the CountA function to count
the non-blank cells, and it uses Offset to extend the range from A2 to A11
in our case.

Does this help? Again, post back with more questions, and I or someone else
will jump in and answer.

Best regards,
Kevin
 
Steveh wrote...
OK Got the basics working...another related question. How can I prevent
Click to expand...
duplicate entries of values already in the list?

Steveh,

How can you remove duplicate entries in the list?

Here's one way.

Steps:

1) Select the list.

2) Data | Filter | Advanced Filter...select unique records only

3) Copy unique records

4) Paste unique records in an empty spreadsheet or range

5) Delete prior "duplicate" list

6) If desired, move unique list to where the duplicate list once was OR
leave original data in tact (my usual choice) and just work with "clean"
list.

Hope that helps.

Best regards,
Kevin
 
Back
Top