Derive New Lists from Existing List (Excel 2000)

Topic

New Reply

Hello y’all,

From the list below, I’m trying to derive a separate list for each department (A,B,C,& D) that returns the expert’s name, and the application. Anyone have suggestions how to approach this that does not include sorting? I was playing with IF and VLOOKUP. Blank lines are unacceptable.

Dept	Expert Name	Application
A	Pluto	Excel
B	Xerxes	Word
C	Polaris	Access
D	Mars	PowerPoint
C	Jupiter	MS Project
A	Saturn	Acrobat Pro
D	Arcturus	Dreamweaver

Thanks so much,
Rich

Reply | Quote

Replies

Select the Dept column. Then select filter from the Data Menu, then click on Autofilter in the flyout menu. You Should now have a drop down list arrow in the Dept label cell. Drop down the list and select the department you want the list for.

Reply | Quote

Thank you two for your suggestions about using AutoFilter.

I am seeking a way to do this without using AutoFilter follwed by copy and paste. I envision that I could edit or add to my “master” list and the various dependent lists would dynamically reflect the data in the “master” list without further action.

That’s my challenge! 🙂

Thanks,
Rich

Reply | Quote

What do you plan to use the derived lists for? Are they going to be datasources for something else (e.g. listboxes) or is it for display purposes?

Reply | Quote

Dear Rory,

They’ll be for display purposes.

Regards,
Rich

Reply | Quote

Would it be OK to have the lists with lots of error rows at the end, which you can then hide using conditional formatting?

Reply | Quote

Well, let’s see where we go with your ideas, before we say no! 🙂

Thanks,
Rich

Reply | Quote

See attached – you can hide the numeric columns and format the error cells with a white font. Does that work?

Reply | Quote

Attached is a spreadsheet where I am using Vlookup. It is somewhat right but it isn’t right. I am using Conditional Formatting to cover the error (=ISNA(A1) for formula and choosing white for the font) on each of the department spreadsheets. If someone can look at it and revise the formulas so they don’t get duplicate entries maybe it would work. Let me know if I am completely wrong.

Reply | Quote

So-o-o close, yet so far away! 🙂

Thanks so much

Reply | Quote

OK, so what would need altering?

Reply | Quote

The attached spreadsheet takes Rory’s spreadsheet and puts the results in different spreadsheets. I used ISERROR to hide the errors and the range I used for the master was A2:C1000 so if you want to add more than a 1000 on the master sheet then this has to be changed in all formulas. I hid column A on each sheet.

Reply | Quote

Dear LindaR, and all,

Thank you for your very kind help. LindaR’s solution will, I think, meet our needs.

From a learner’s point of view, though, I’m wondering if you could help me understand what’s going on in the formula?
[indent]

---

=INDEX(Master!$B$2:$B$1000,SUM($A$2:A2))

---

[/indent]
Particularly, your use of SUM as the second INDEX argument.

Again, thanks for your help,
Rich

Reply | Quote

Since this is Rory’s solution (all I did was adapt the formulas to separate sheets), he is better able to explain what the formulas are doing. If you unhide column A on each sheet, there is a formula there that may explain the SUM($A$2:A2)) part of the =INDEX(Master!$B$2:$B$1000,SUM($A$2:A2)) formula.

Reply | Quote

Thanks LindaR,

Yes, immediately after clicking the ole “post” button, I re-examined column A forumulas. Thanks LindaR.

Okay, Rory, you’re on, to help us out understand your approach.

Blessings,
Rich

Reply | Quote

OK, the numeric columns are basically using a simple MATCH formula but using a rolling range. In effect, each row says “match the type (A, B, C etc) in the range following the previous match. So the first formula says, for example, find “A” in the range B2:B10. Let’s say it’s found at row 3. The next formula then says find “A” in the range (3 rows down from the first A, and 3 rows shorter than the previous range). This is then repeated but adding up the indices of the rows found. Because the search range is offset each time, you have to add up the previous indices to get the new index – e.g. if the first match is found in row 3, the next match starts from 3 rows down. If that is found at row 2, that is now 5 rows into the original range.
Does that make sense?

I probably should have specified that I had left the original in raw format so you could see what I was doing!

Reply | Quote

Do an AutoFilter.
Select the Dept from the Drop down list
Highlight and copy
Go to a new spreadsheet or where you want your list
Paste

Do this for each of the Departments.

Reply | Quote