assistance with array or advanced filter please

[Lmgtfy]

Hi - I was up most of last night (not related to this project ) and my brain isn't comprehending anymore.
I need help with an array or advanced filter please.

Sample spreadsheet attached: Sample1.xlsx

Sheet one [members] is a list of names, birthdays, and an assigned group based on criteria in sheet two.
Sheet two [group counts] is the range of birthdays for the assigned group and a group name, which will need to change multiple times.
I'd like to set up sheets 2-10 as the group name with a list of names that belong to the group. Although I realize I could sort sheet 1 by group name and copy/paste the members - but the criteria for assigned groups will change over time and an automated function would be preferable.

Any assistance would be much appreciated!

[Lmgtfy]

The group lists don't have to be in separate sheets. They could be all on the same sheet in different columns if it would be easier.

[dredwolf]

See if this will work for you:Lmgtfy.sol1.xlsx

Note - I made several changes, (Highlighted in Yellow) and added several named ranges

Hope this helps

Edit-
Carried the formulas on Sheets 'Unassigned' - 'Group 7' down to row 107,if more needed, just drag the formulas down as far as necessary, and I limited the named ranges to 10000 rows, you can adjust these in the name manager

[Lmgtfy]

That looks great. Thank you!

But, I'm having a little difficulty translating it to the live data.
The live data has other info on sheet 1 columns A-M. So I inserted columns in your spreadsheet to move "Name" to column N, "birthday" to column O, and "group" to column Q. Everything still works in your spreadsheet with the new cell references.

Then I copied your columns to my live sheet and I get error: "unknown name range" for everything you changed.
I tried typing the formula from the formula bar in your sheet manually in to the formula bar in my sheet, and it's still a no go.

I could potentially copy all my data to your sheet and save as - call it a day.
I do need to add to the info that's being generated in sheets 3 through 10 if possible. I thought I could translate your syntax into a new column, but I'm in over my head. I would like to also pull the info from sheet 1 column A into the group names sheets. Name is now in column N, "birthday" is column O, and "group" is column Q. If it could list the info from column A as well, that would be over the top. Column A is personally identifying information, so I can't send you my live data unfortunately.

Total bonus would be able to copy/paste into a google spreadsheet, but I don't think google recognizes tab names as references for formulas - hence my complicated previous setup.

Thanks for any insight!

[dredwolf]

It sounds like you may have missed some of the named ranges... they all need to be there( and referenced right(right row/columns being addressed...this CAN get messed up in a copy paste to a new workbook...that's where I'd start looking for the problems... and with added columns, it means different offsets...another possible problem...
(WE don't mind complex problems, HONESTLY!!! They take a little longer to solve, but We would (I believe I speak for most of us) prefer to offer a complete solution rather than one where We get "Ooops...I forgot to mention.....And your solution does not work";If the sample we get matches the actual workbook as close as privacy and size allow, the better solution we can offer,AND, somtimes the solutions get easier(not always I admit, but often enough to mention it )
Upload a new Sample and I'll see what I can come up with, and may take a few hours, as it is getting near my bedtime here, so I may not actually get to do any work on it until the morning

Note- I can not!! guarantee any compatibility with google docs... it uses some different formulas, and Will Not recognize some Excel formulas..sorry again

[Lmgtfy]

I totally understand. Excel is good at renaming things when inserting/deleting row/columns, etc and I thought I could comprehend your solution well enough to translate it. I truly do appreciate your help!
I've learned so much from reading other posts here.
But I think my brain is still wishing it was asleep.

Okay. Attaching a copy of my spreadsheet. lmgtfy.xlsx I deleted all the extraneous data. Column A I renamed as "significant". I can tell you the data in column A will be maintained as text.
Background:

• This spreadsheet is being used to maintain the identifying information for up to a max of 200 children
• Sheet 1 has information in it up through column T
• Children will be assigned to various groups over time based on dob

Requirements:

• Users can retitle columns if necessary (not often but a possibility)
• Users can change the group names and birthday criteria as needed in sheet 2
• Assigned group names on sheet 1 change as group parameters on sheet 2 are updated
• Sheet 1 and 2 (at minimum) will be maintained in google docs

Strongly Desired:

• Sheets 3-11 update with a list of members and birthdays for each group (as previously worked on - this is the purpose of my post)
• Addition of significant info from column A to sheets 3-11 if the member lists work correctly
• Non-reliance on name of tabs so that formulas can be used in google docs among collaborators

I realize google docs is a beast of its own. I'm getting pretty good with it... but yeah. If necessary, collaborators within google docs can sort sheet 1 by group name to be able to quickly identify members of each group. A master copy of the identifying information in sheet 1 and 2 will be maintained in google docs. I can maintain a separate copy of excel spreadsheet if needed for group lists.
Clear as mud?

Thank you mucho!!

[dredwolf]

Okay, I've downloaded the file, but will probably be 14 hrs till I post an update, it IS bedtime here, If someone else comes up with a solution first, GREAT ! , while I would like to be the one, it is more important you GET one !

EDIT-
Unless this is some commercial product( and even then) there is NO realistic reason for them to change column names...I'll try to allow for it, but in reality, letting them change column names at will is not good practice, better to offer a fixed range of cells to change column names in, then the named ranges can work with that...just a suggestion though

EDIT 2-
AND I would use conditional formatting to display the user defined named, while the underlying("REAL") name stays the same

[dredwolf]

And, I am going to break the 'Merge and Center' cells....they create Nothing but problems..use 'center across selection' in the alignment tab of format cells instead

[Lmgtfy]

And, I am going to break the 'Merge and Center' cells....they create Nothing but problems..use 'center across selection' in the alignment tab of format cells instead

No problem. Thank you!

[Lmgtfy]

there is NO realistic reason for them to change column names...

*shrug*

Okay. We're going to be able to move some stuff around.
Column A (still significant) will be "First Name". Still text. And the sample text in the file I shared should work for sample purposes as I can c/p over it with the First Name of each child's parent. We'll be able to cross reference First Name of parent to child for contact purposes if the column A data can populate on each group tab along with Child's name and Child's dob.

I was previously using social security number saved as text, but I can move SSN to another column without affecting things on my end.
The file you have should be all good if Column A is permanently named "First Name".

THANK YOU!

[dredwolf]

Your welcome
Now...these added columns are supposed to show in the sheet associated with them?...your sample seems to be missing what you expect.....for what you have uploaded. my original solution (with range adjustments) seems to work fine....so not really sure what the problem is....please update the sample workbook with a few examples of the expected output, so WE can see what the issue is, (sorry, thought I made that clear, but I WILL re-work that reply)

EDIT-
I notice that the group sheets in particular have no expected results, definitely need to know what you WANT to see there...

[Lmgtfy]

Oh - sorry. Well past bed time here as well.

Exactly as you did earlier please.
Your file has Child's name and Child's dob in sheets 3-11. We need to add "First Name" from column A of Sheet 1 to the generated lists in sheets 3-11.

Unfortunately, I messed everything up trying to figure out how to add column A to the populated lists on the named group sheets. I deleted all my changes. There was no going back
All I have at the moment is my original and my save-as file lmgtfy with the extraneous data deleted and extra columns hidden.

[Lmgtfy]

Did a quick c/p from your file to mine with an additional row. First Name of group sheets will hopefully populate from column A of sheet 1.
lmgtfy.xlsx

[dredwolf]

Well, I'll work on it, just need to know if the other columns need to transfer as well, All blank in the upload, but if they need to be transferred its okay... I'll do it in the AM.. bed time now...

[Lmgtfy]

I uploaded a new copy in http://www.excelforum.com/showthread...=1#post3143611 with a sample in sheet 3
Thanks and sleep well

[dredwolf]

here's the new solution
The main problem was the formulas were using the named ranges from the old workbook, which were defined for the sheets there...so lots of errors when it tried to use those ranges on this data...lol

Anyways, redefined the ranges for this workbook, and added a few refinements to make the calculations a little faster, there wasn't a noticeable lag here, but could have been if the data set was larger

put some notes on what I did in the sheets themselves to help you understand what is happening (most on the added sheet 'NamedRangeData', which can be hidden if required)

Hope this helps

[Lmgtfy]

It doesn't translate to google docs at all, but it performs exactly what I need it to do.
Thanks so much!
I really do appreciate all your time and communication on this.

If anyone else browsing has a better feel for Google Docs than me, I'd love the feedback!