Clear a "moving" range offset from Target

  • Thread starter Thread starter L. Howard
  • Start date Start date
May be back with a couple more what-if's, the code looks pretty
intense! But at least I know what it is supposed to do, which will
be helpful.
Click to expand...

I was already thinking how to handle if the lists get edited after
they're stored in the array. If shortened the resize to ClearContents
will leave populated cells. My thought is to store a delimited list...

name:rows

...and use that instead of current size!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

I was having a problem with adding the third drop down and columns in a earlier post, where you added it for me and reposted to the link.

I was looking for what I was missing in the process, wanting to learn how to do
it myself.

So I am trying to do a fourth drop down and I believe I have everything in place except the DD_4_16 name (for column 19, which is "S") where the Refers To: window should have the formula like this. At least I think so as I look at the others for an example.

=OFFSET(Sheet2!Hdr4_19,1,0,COUNTA(Sheet2!Col4_19)-1,1)

Whenever I type (not copy) that formula in the window I get this:

=OFFSET(Sheet2!Hrd4_19,1,0,COUNTA('Drop Down List Copied Garry_v2 (1).xlsm'!Col4_19)-1,1)

And the drop down does not produce the list on sheet 1.

If you get some time could you A, B, C me through the steps to do column S to work in the fourth drop down. I assume you have to do each column separate..?

I have the drop down and it is included in the DD_Lists, as cell H2 and DD4_Lists includes DD_4_16. If I click on DD4_Lists in the formula window, cell S1 on sheet 2 has the marching-ants on it.

So in plain third grade Canadian/English can you list the steps please. Probably should include the entire process even though I may have some already done a few of the correct steps.

Methinks I got all my DD_ this and that's correct, typos can be very misleading.<g>

Thanks.
Howard
 
I have no idea why you're getting that ref after entering the formula!

First thing I should mention is the list numbering scheme you're using
doesn't sit well with me! I prefer to persist the same logic in terms
of refs to each list...

For DD2_List I'd go with..
DD_2_1. DD_2_2, DD_2_3, DD_2_4, DD_2_5
..so they clearly ref list 2, and their respective index.

...but I didn't bring this up due to the other concerns being prcessed.
That means I'd rather see your DD_4_16 named DD_4_1, and its column
isn't relevent to anything other than sheet layout. I revised my copy
to reflect this change so have another look!

Also, as you've seen, I've been inserting a narrow column between the
list groups and using alternating fill colors for odd/even blocks. That
means the DD_4 block would be the same fill as DD_2 block. (I just
don't feel more colors are needed to visually distinguish the groups!)


The step-by-step you requested:
<Using the NameBox>
01: Name each hdr in same fashion as existing hdrs;
'Select each hdr cell before naming
<Example>
Select DD_4_1;
click in the NameBox and type Sheet2!Hdr4_1;
Press the Enter key;
Right arrow to DD_4_2 and repeat.

02: Name the hdr group in the same fashion as existing groups;
Select all the DD4 hdrs;
Click in the NameBox and type DD4_Lists
Press the Enter key;

03: Name list cols in the same fashion as existing list cols;
'Select each DD4 list col before naming
<Example>
Select DD_4_1;
click in the NameBox and type Sheet2!Col4_1;
Press the Enter key;


<In NameManager>
04: Name each DD_4 list in same fashion as existing dynamic lists;
'If 'Sheet2' is the active sheet you won't need to specify
'the sheetname while typing the formula!
<Example>
Type DD_4_1 in the Name box;
Tab into the RefersTo box and type...
=offset(hdr4_1,1,0,counta(col4_1)-1,1)
Add the name and repeat for the others in the group.

05: Update DD_Lists.RefersTo to include the absolute address of DD4;

<On Sheet1>
06: Update the DV List ref for DD4.

I didn't add anything to the revised file so you could try adding DD4
on your own.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Also, as you've seen, I've been inserting a narrow column between the
list groups and using alternating fill colors for odd/even blocks. That
means the DD_4 block would be the same fill as DD_2 block. (I just
don't feel more colors are needed to visually distinguish the groups!)


The step-by-step you requested:
<Using the NameBox>
01: Name each hdr in same fashion as existing hdrs;
'Select each hdr cell before naming
<Example>
Select DD_4_1;
click in the NameBox and type Sheet2!Hdr4_1;
Press the Enter key;
Right arrow to DD_4_2 and repeat.

02: Name the hdr group in the same fashion as existing groups;
Select all the DD4 hdrs;
Click in the NameBox and type DD4_Lists
Press the Enter key;

03: Name list cols in the same fashion as existing list cols;
'Select each DD4 list col before naming
<Example>
Select DD_4_1;
click in the NameBox and type Sheet2!Col4_1;
Press the Enter key;


<In NameManager>
04: Name each DD_4 list in same fashion as existing dynamic lists;
'If 'Sheet2' is the active sheet you won't need to specify
'the sheetname while typing the formula!
<Example>
Type DD_4_1 in the Name box;
Tab into the RefersTo box and type...
=offset(hdr4_1,1,0,counta(col4_1)-1,1)
Add the name and repeat for the others in the group.

05: Update DD_Lists.RefersTo to include the absolute address of DD4;

<On Sheet1>
06: Update the DV List ref for DD4.

I didn't add anything to the revised file so you could try adding DD4
on your own.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

Got the new sheet and these instructions.

Off to try it.

Thanks, again!

Howard
 
Got the new sheet and these instructions.
And we have lift off!!

https://www.dropbox.com/s/ozm7b21hln27qfz/Drop Down List Copied Garry_v3 My Try at it.xlsm?dl=0

Thanks for all the help.

Will keep for reference, however the code is magic stuff to me.

Howard
Click to expand...

Well not exactly! Your DD4 names are global instead of local. (I
suspect you did not prefix with the sheetname where instructed) Have a
look at my version in the NameManager window and note the 'Scope' for
each name. Then fix your file so your names are as per the
instructions...

"..same as the other..."

...and then consider it a passing grade if it all works.

Note that the names are all local scope except for the dynamic ranges
and DD#_Lists. You can fix the RefersTo only in Excel. You can change
the scope in the NameManager addin using multi-select, but to do this
in Excel you have to delete the global names 1st, then recreate them as
local. I reiterate that you start using the NameManager addin!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Well not exactly! Your DD4 names are global instead of local. (I
suspect you did not prefix with the sheetname where instructed) Have a
look at my version in the NameManager window and note the 'Scope' for
each name. Then fix your file so your names are as per the
instructions...

"..same as the other..."

..and then consider it a passing grade if it all works.

Note that the names are all local scope except for the dynamic ranges
and DD#_Lists. You can fix the RefersTo only in Excel. You can change
the scope in the NameManager addin using multi-select, but to do this
in Excel you have to delete the global names 1st, then recreate them as
local. I reiterate that you start using the NameManager addin!!!
Click to expand...


I'll work on that.

H
 
https://www.dropbox.com/s/ozm7b21hln27qfz/Drop Down List Copied Garry_v3 My Try at it.xlsm?dl=0

I think I made the corrections you mentioned.

Also added a "Clear" selection in the drop down, which was the blank columns between the Sheet2 list groups. If you enter stuff in those columns, thenit will just be another data column, otherwise the previous list is cleared as per the code, with nothing returned.

If this link in this post doesn't work, the one in my previous post does.

Thanks for the help, feel like I made some progress thanks to your demos and critique.

Howard
 
https://www.dropbox.com/s/ozm7b21hln27qfz/Drop Down List Copied Garry_v3 My Try at it.xlsm?dl=0

I think I made the corrections you mentioned.

Also added a "Clear" selection in the drop down, which was the blank
columns between the Sheet2 list groups. If you enter stuff in those
columns, then it will just be another data column, otherwise the
previous list is cleared as per the code, with nothing returned.
Click to expand...

Please explain your reasoning for adding this complexity! IMO it's
absolute overkill since clearing the DD cell normally also clears its
list!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Please explain your reasoning for adding this complexity! IMO it's
absolute overkill since clearing the DD cell normally also clears its
list!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

First was just to see if I could do it and second I did not realize clearing the dd cell would clear the list.

Howard
 
First was just to see if I could do it and second I did not realize
clearing the dd cell would clear the list.

Howard
Click to expand...

I see! Well congrats on figuring out a way to do it using code.

As for the 'second', reading the code reveals that any existing list is
*always* cleared, and a new list is only display if Target is not
empty! The code was deliberately written this way to persist the logic
of the project...

select a list in the dropdown to display its contents;
thus
clearing the dropdown should display nothing.

...which, as I see it, only makes sense in terms of user productivity.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top