Auto Populating a series of tabs within a spreadsheet

[kli123]

Hi,

so I'm trying to populate a series of tabs in one spreadsheet based on manually inputted data on a main tab.

The main tab has customer problems with different columns for assembly line, problem #, customer, date, etc.

the derivative tabs has columns for month problem ocurred, and rows for each problem.

each tab is dedicated to an individual assembly line.

I'm trying to make an algorithm that automatically populates the rows (the columns are easier to figure out) with, say the problem number. From there, I can use hlookups to populate the other columns. I'm still trying to figure out how to make the initial population happen.

This is the area I'm trying to populate

[Pete_UK]

It would help if you attached a sample Excel workbook, so we can see exactly what you are trying to achieve.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.


Hope this helps.

Pete

[kli123]

Alright,

I uploaded a redacted version. I am trying to populate both the supplier and internal section of the "Line 14 Paynter Chart" Tab using the "Internal 8D..." column on the "Zero mile Tracking changes" tab.

[Pete_UK]

No file attached.

Pete

[kli123]

Re-uploaded

[Pete_UK]

Okay, now that I can see how your data is laid out, can you please explain with reference to this file what you want to achieve, e.g. which is the "main tab" that you referred to in your first post, which columns are used to identify the supplier (there are a few coloured yellow), which sheet(s) do you want to extract data to, what naming convention do you use for you supplier sheets, and so on.

Pete

[kli123]

The highlighted columns are just additions to the template I am proposing.

This tab is where the data is input. I want to isolate the information in the rows where the problem occurs at Line 14 (Last column) and then extract the complaint to be input into the "Line 14 Paynter Chart"Tab

Also, I don't need to identify supplier, just if the issue is internal or a supplier issuse (Column X).

As you can see, the Paynter Chart has a section with rows for internal issues and supplier issues. I need to populate them ONLY with complaints ocurring on that line.

[kli123]

There will be other tabs for the different lines, just need to figure out how to populate it for now.

[JeteMc]

If I understand correctly you would like the Internal Prism 8D / CPM numbers from column Y on the Zero-mile tracking changes sheet to populate either B5:B16 or B18:B23 of the Line 14 Paynter Chart sheet. You want this to be based on there being a 14 in column AL and whether or not the term "Supplier Issue" appears in column X. If that is the case then:
1) Select cell B5 on the Line 14 Paynter Chart sheet, paste the following into the formula bar, then drag down to B16:

Please Login or Register  to view this content.

2) Select cell B18 on the Line 14 Paynter Chart sheet, paste the following into the formula bar, then drag down to B23:

Please Login or Register  to view this content.

Using the file attached to post #1 the results should be 2, 10, 14 in B5:B7 (B8:B16 return blanks) and 7 in B18 (B19:B23 return blanks)
Let us know if you have any questions.

[kli123]

Hi,

Unfortunately the formula returned no values. I'm going to recheck it since I sent you a redacted file and see what I deleted.

[JeteMc]

Here are the formulas modeled in an abbreviated version of the file.
Let us know if you have any questions.

[kli123]

Thank you! Could you possibly walk me through how you generated that line of code so I could better understand?

[JeteMc]

Starting with the AGGREGATE function.
The first argument (15) means that it will return the Kth smallest result, determined by ROW(n:n).
The second argument (6) means that it will ignore results that are #DIV/0! errors.
In the array argument of the function,
(ROW($2:$17)-1) produces an array of row numbers corresponding to the rows on the 'Zero-mile tracking changes' sheet from which data is to be extracted.
('Zero-mile tracking changes'!AL$2:AL$17=14) produces a T/F array based on whether or not the value in column AL of the 'Zero-mile tracking changes' sheet is 14.
('Zero-mile tracking changes'!X$2:X$17<>"Supplier Issue") produces a T/F array based on whether or not the value in column X of the 'Zero-mile tracking changes' sheet is "Supplier Issue".
The AGGREGATE function then passes the row number to the INDEX function which extracts that information from column Y, until it triggers the IFERROR function which at that point will display a blank.
I suggest that you select cell B5 and utilize the Evaluate Formula feature (Formulas tab) to see this working. After watching the feature applied to B5 repeat by selecting B7 to see how the ROW(n:n) extracts the 3rd smallest value, then B8 to see how the IFERROR function takes over.
Let us know if you have any questions.

[kli123]

So, I've edited the spreadsheet more so that the months on the line tab are in a YTD format using an EDATE fxn then editing the display options. How would I incorporate it so that only complaints that fall within the prescribed MONTHS are populated? Is there a way to check to see if the months match? I'm trying to make this spreadsheet as real-time as possible.

[JeteMc]

I'd suggest that upload a sample of the edited spreadsheet and manually put in the expected results, so that we may attempt to match those results using either formulas and/or code.
Let us know if you have any questions.

[kli123]

Will do, having said that, how could I adjust the above function if the information inputted is not in the same table. I can't upload a document for this one, as the quality management spreadsheets are proprietary (Different, incredibly **** company) and I'd rather not get into trouble for that.

[JeteMc]

As to a file that shows how the EDATE function is being used and what is meant by "so that only complaints that fall within the prescribed MONTHS are populated?" I don't see a new file, only the one attached to post #1 (May 9th).
As to "information inputted is not in the same table" change the sheet name and the range references to match the table from which the information is being extracted.
For example if the Internal Prism 8D / CPM numbers now come from Sheet1 and the range A2:A100 then change INDEX('Zero-mile tracking changes'!Y$2:Y$17... to read INDEX('Sheet1'!A$2:A$100...
Change ...AGGREGATE(15,6,(ROW($2:$17)-1)... to read AGGREGATE(15,6,(ROW($2:$100)-1)
If the line number is still in column AL but on sheet1 then change .../('Zero-mile tracking changes'!AL$2:AL$17=14)/... to read .../('Sheet1'!AL$2:AL$100=14)/...
Same for 'Supplier Issue' or not.
Let us know if you have any questions.

[kli123]

Here are the files.

The instructions are written into them

[JeteMc]

As to the first file: If I understand you would like only those numbers that correspond to dates between those in cells D3 (June 27, 2017) and Q3 (June 27, 2018). If that is the case then modify the formulas as follows:
In B5 (and down):

Please Login or Register  to view this content.

In B18 (and down):

Please Login or Register  to view this content.

I'll try and get back to you later concerning the second file, providing someone else hasn't given an solution in the mean time.
Let us know if you have any questions.

[kli123]

Is there a way to check everything that happened in the listed months and not exclude any days that still fall within that month. If this makes things ridiculously complicated, I could always ask my supervisor how specific he needs that YTD to be.

[JeteMc]

Try these:
B5 and down:

Please Login or Register  to view this content.

B18 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[JeteMc]

Here is my interpretation of part of the second file.
1) The drop down in cell BG1 references a list on Sheet 2
2) BG4:BG15 are populated using:

Please Login or Register  to view this content.

3) BH4:BI15 are populated using:

Please Login or Register  to view this content.

4) BQ1 is populated using:

Please Login or Register  to view this content.

Note that BQ1 is a helper cell that may be moved and/or hidden for aesthetic purposes.
Note that BH4:BH15 are custom formatted 'mmmm'
Note that BI4:BI5 are formatted 'General'
Let us know if you have any questions.

[kli123]

Wait, so a few things:
Q3 is not a BU, Q3 has BUs in it, but it is a complaint level. I would want the Q3 option to display the information in the leftmost table. Also, the complaints would be inputted to the separate tables, will this translate well without all o the complaint inputs on sheet 3 the way you have it? Then, when I display all of this data using a bar graph, will it be able to list the x-axis using Month and year? The problem I run into with the formatting, is that the graph will list the individual dates as the different points on the x-axis. The Q3 population should not be an issue, since I could just have a nested IF statement that just copies the Q3 data. It's then combining all the complaint levels or by Business Unit that's the challenge for me.

[JeteMc]

I let this sit for a while in the hope that someone else would have a solution however at this point I suggest that you upload another copy of the Instructions Upload file. Put in some fictional input data then manually show us what output you want to see when:
1) Q3 is selected from the drop down
2) BU1 is selected from the drop down
3) ALL is selected from the drop down
Let us know if you have any questions.

[kli123]

Note: the drop down menu will go into J2. I might adjust this so that the level and BU can be both selected or all selected.

Each tab is supposed to reflect the different option.

[JeteMc]

This proposal employs three helper columns which may be moved and/or modeled for aesthetic purposes. It also employs the two drop down (Level and Area) option as I feel this will provide you the more flexibility and ability to meaningfully analyze the data.
J2 is the drop down for Level and K2 is the drop down for Area.
The range W28:W43 is populated using: =IF(OR(J$2="ALL",J$2=D28),1,0)
The range X28:X43 is populated using: =IF(OR(K$2="ALL",K$2=G28),1,0)
The range Y28:Y43 sums the corresponding values in columns W:X
The ranges W4:Y22 are populated using formulas similar to:

Please Login or Register  to view this content.

Let us know if you have any questions.

[kli123]

Thank you!

Is there a way so that the date only reads month and year? The individual days tend to throw off the x-axis of the charts I try to generate,

[JeteMc]

Here is an option and a simple example.
1) Add year and month columns to the data,
2) Produce a pivot table based on the data utilizing the year and month columns,
3) Produce a pivot chart.
Let us know if you have any questions.

[kli123]

I just reduced the dates to the first of each month then formatted for the sake of output. I nested the formula you gave me into an IF statement to filter out any events that happen before my YTD range. However, is there a way to just filter out those dates so that I'm not left with a million blanks? This is my formula:


IF((DATE(YEAR(IFERROR(INDEX(N$28:N$43,AGGREGATE(15,6,(ROW($28:$43)-27)/(AF$28:AF$43=2),ROW(1:1))),"")),MONTH(IFERROR(INDEX(N$28:N$43,AGGREGATE(15,6,(ROW($28:$43)-27)/(AF$28:AF$43=2),ROW(1:1))),"")),1)<$K$7),"",DATE(YEAR(IFERROR(INDEX(N$28:N$43,AGGREGATE(15,6,(ROW($28:$43)-27)/(AF$28:AF$43=2),ROW(1:1))),"")),MONTH(IFERROR(INDEX(N$28:N$43,AGGREGATE(15,6,(ROW($28:$43)-27)/(AF$28:AF$43=2),ROW(1:1))),"")),1))


I could probably use it if there is no simple solution. The output data will be hidden anyways.

[JeteMc]

I'd say that making a second table that only pulls the YTD data would be simpler. Look at that attached and see if it looks like what you want.
Let us know if you have any questions.

[kli123]

Also, your function does not seem to work when, say, Q2 and BU2 are selected

[JeteMc]

Here is a sample showing Q2 and BU2
Let us know if you have any questions.

[kli123]

Would it be possible to just use advanced filters on the chart where data will be inputted to get the same result? Would this method not work if graphed?