Populating one sheet with a separate sheets information based on criteria

**madmoojuice** · 11-12-2014, 04:54 PM

I have several projects I track, typically 15-20 at any given time. Each project has their own parts list. The spreadsheet I use to track everything on has the following sheets:

1. Parts List - listing all parts still on order
2. Project List - listing the projects in work
3. Info Sheet 1 - Project 1's information
4. Info Sheet 2 - Project 2's information
5. Etc...

The individual project sheets (Info Sheet 1, 2...) populate from the project listing sheet and have a section for all parts out against that particular project. Right now, I hand write this information after printing. I have not figured out how to populate the parts list on the info sheets based on the parts listing sheet. The issue I have is that the individual project sheets change based on the data on the project listing page.

The projects change every few days as they are completed and new ones added, and the info sheets are printed to go on the front of that projects folder. So if I change line 2 on the project listing, it changes the information on info sheet 2. Likewise, I would like it to update to reflect the parts out for that particular project.

I've attached my sanitized doc with bogus information and I've pulled the sheets that are irrelevant. I know my existing pages could use some cleanup but that will have to wait for time that I don't have right now

Any advice or help?!

Thank you in advance!!
Josephtest_status.xlsx

**daffodil11** · 11-12-2014, 07:42 PM

Challenge Accepted! Time for a spreadsheet makeover!

Delete tabs 2-4! I HAD A LOT OF SUGAR TODAY.

First let's clean up for other references. I'd recommend starting with:

=RIGHT(CELL("FILENAME"),LEN(CELL("FILENAME"))-FIND("]",CELL("FILENAME")

This references the sheet name that the formula is on, which will match up to your table in the Unit Listing.

Nest this in the MATCH section of an INDEX'ing formula, and now each sheet will autofill the various fields.

C2:

=IFERROR(INDEX('Unit listing'!B2:U11,MATCH(1*RIGHT(CELL("FILENAME"),LEN(CELL("FILENAME"))-FIND("]",CELL("FILENAME"))),'Unit listing'!$A$2:$A$11,0),3),"")

That 3 at the end indicates which column in B2:U11 it's pulling from. We alter this number for A7, K7, V7, AK7, etc and let it do its magic.

Next, we apply an INDEX(SMALL(IF array formula to extract only rows that match the current sheet's stuff. Warning, arrays do no play kindly with merged cells.
Merged cells have no friends. Bad merged, cells. Bad!

But, I guess it happens.

Unmerge A11:E11, put in in A11:

=IFERROR(INDEX('Parts Out'!$A$4:$K$76,SMALL(IF('Parts Out'!$B$4:$B$76=$C$1,ROW('Parts Out'!$A$4:$K$76)-ROW($A$3)),ROW(A1)),8),"")

This is an array formula, which needs to be confirmed with Ctrl+Shift+Enter. This will put braces around the formula, and doesn't work if you type them out.
Next, remerge A11:E11, and then drag the formula down.

Repeat same steps across, changing that column reference of 8 for PO to 6 for QTY, 4 for Part Number, etc.

Now copy your brand new tab 4 times, and rename them 1-5.

From here out, they fill themselves out!

One last bit:
We need to add a little bit of programming on the back-end to make CELL("filename") update itself as you jump from tab to tab.
Hit Alt+F11 to enter Visual Basic Editor, and on the left side of the new window you'll see a VBA project for the workbook.
Double-click "ThisWorkbook" and add the following code:

Please Login or Register  to view this content.

This does nothing more than hit F9 for you each time you change tabs, so that the CELL("filename") formula updates correctly.
You can just close that window and you're done!

Excel Magic!

**madmoojuice** · 11-13-2014, 10:05 AM

Thank you so much! I've been messing with Excel for a few years now and slowly (albeit TOO slowly) learning it, but this was beyond me. I definitely appreciate your insight and the way you educated me on what things do! I'll have to read it a dozen times and play with using this information in various docs, but I've definitely got some new information that I can already see uses for in other areas.

I had thought 'array formulas' were just multiple formulas nested inside each other in a single cell. What makes a formula an array, versus a nested(??) formula and how do I know when to use the Ctrl-Shift-Enter command? Is there somewhere you could suggest I go to learn about array formula's? I tried reading about it on microsoft.com, but for some reason I always ADHD out when I read their pages.

Again, thank you much for the great help and insight!

Joseph

**daffodil11** · 11-13-2014, 12:02 PM

Chip explains it pretty well here:

http://www.cpearson.com/excel/arrayformulas.aspx

Nesting IF with a range of more than one VALUE is pretty good way to turn something into an array expression.

=SUM(IF({1;2}>0,1) evaluates both the 1 and 2, outputs a 1 for each passed evaluation, and sums 1 and 1 to a total of 2.

In other words, any expression that has multiple outputs can be an array.

On a new sheet put 1 in A1, 2 in A2
Select B1:B2 and type: =(A1:A2>1) and then Ctrl+Shift+Enter
B1 and B2 are now a single formula outputting 2 answers

In our more clever expressions, we are 'floating' the multiple outputs, and then evaluating them further.

Using the A1:A2 from above we illustrate this
=IF(A1:A2=1,ROW(A99:A100))

Which evaluates to
=IF({TRUE;FALSE},{99;100})

The TRUEs pass through, the FALSEs remain
=99;FALSE

Technically, for an indexing operation we want to always begin the ROW() statement at 1, so if your range is A21:A50, your ROW is A1:A30 (or you can just do ROW(A21:A50)-ROW(A20)))

We still have two answers hanging in the air..

=SMALL(99;FALSE,ROW(A1)) (The Nth smallest variable is always left as a relative reference so that it increments as the formula is copied down)
=SMALL(99;FALSE,1) What is the smallest number of 99 and FALSE?
=99

Putting it all together:

Imagine I have names in C1:C2, daffodil11 and madmoojuice. I want to pull C where A1:A2 = 2

=INDEX($C$1:$C$2,SMALL(IF($A$1:$A$2=2,ROW($A$1:$A$2)),ROW(A1))
=INDEX($C$1:$C$2,SMALL(IF({1;2}=2,{1;2}),ROW(A1))
=INDEX($C$1:$C$2,SMALL(IF({FALSE;TRUE},{1;2}),ROW(A1))
=INDEX($C$1:$C$2,SMALL({FALSE;2},ROW(A1))
=INDEX($C$1:$C$2,SMALL({FALSE;2},1)
=INDEX($C$1:$C$2,2)
=madmoojuice

Imagine you had 50 rows (I've modified the ranges below), and the next 2 didn't appear until the 50th row. When copied from the first cell to the second the formula would appear as:
=INDEX($C$1:$C$50,SMALL(IF($A$1:$A$50=2,ROW($A$1:$A$50)),ROW(A2))
=INDEX($C$1:$C$50,SMALL(IF({FALSE;TRUE;FALSE x 46;TRUE},{1;2;3;4...50}),ROW(A2))
=INDEX($C$1:$C$50,SMALL({FALSE;2;FALSE x 46;50}),2)
What is the second smallest value in False;2;False;False;False;False;False;False;False;False;False;False;...50}
It finds 50, the INDEX pulls the 50th row skipping everything else.

I messed around for years until I finally broke through the array wall. SUMPRODUCT uses array logic behind the scenes, without the need for the braces so it's a good place to start. Later, LOOKUP(2,1/(range=criteria),outputrange) will take you further down the hole. And who knows, in a few months maybe VB?