1

I have a spreadsheet which contains Bill of material data and Labor costs for manufactured parts.

The first worksheet contains the master part numbers for the assemblies Each Worksheet represents the data for one part and is named by part number.

On each worksheet there is a row which contains the Text "Assembly"

What I need to do is consolidate the Assembly data for each part.

So the first worksheet is called Main and the Part Numbers are in Column A so I need to take the first part number, find the corresponding worksheet, extract the row that contains the text "Assembly" and output a row to a new worksheet that contains the part number and the assembly data.

I would be grateful for any help that you EXCEL experts out there may be able to offer.

Many thanks,

Steve

Steve Hare
  • 11
  • 1
  • 3
  • "take the first part number, find the corresponding worksheet, extract the row..." How do you find the corresponding worksheet? Is the worksheet name an exact match to the part number? – Nate Nov 12 '14 at 21:11
  • Also, do you need to do this to all the items on the first sheet at once, or just a macro that runs on individual items when called for? – Nate Nov 12 '14 at 21:12
  • Also, this text "Assembly", is it always in the first column, or is it mixed in somewhere? You want to copy the contents of the entire row? – Nate Nov 12 '14 at 21:14

1 Answers1

1

Let me make sure that I understand the problem.  The first sheet in the workbook (whose name is “Main”), contains part numbers in column A:

Subsequent sheets have part numbers as their names.  These sheets have “varied layouts”, but they all have a cell in column A that contains the word “Assembly”:

And you want to populate a Consolidated sheet with the “assembly” rows from the “part” data sheets.

In your “Consolidated” sheet, cell A1, enter

=VLOOKUP("assembly", INDIRECT(Main!$A1&"!$A$1:$Z$9"), COLUMN()-COLUMN($A$1)+1, FALSE)

Adjust the $Z$9 to correspond to the longest “assembly” row and the highest row number where “assembly” appears.  (To be safe, make it the greatest number of rows on any sheet.)  Then select this cell, drag it out to the appropriate number of columns, and then drag that down to the appropriate number of rows.

Explanation:

  • Main!$A1 gets the part number from cell A1 on the first (Main) sheet.  The A is made absolute by the $, so, even when you drag this to the right, it stays $A.  But the 1 is relative (no $), so, when you drag this down to row 2 on the “Consolidated” sheet, it becomes Main!$A2.
  • Main!$A1&"!$A$1:$Z$9" concatenates the part number, which is the name of the “part” data sheet, with the string !$A$1:$Z$9 (as adjusted for your actual dimensionality), forming a string like, for example, 17!$A$1:$Z$9.  This is a textual representation of the address range for the “part” sheet.  If any of your part numbers contain spaces (or exclamation marks), you’ll need to put the sheet name into quotes: "'"&Main!$A1&"'!$A$1:$Z$9".  If any of your part numbers contain single quotes (apostrophes), you’ll have problems.
  • INDIRECT(Main!$A1&"!$A$1:$Z$9") takes the textual representation of the address range and turns it into an actual address range.
  • COLUMN() is the column number of the current cell.  COLUMN($A$1) is 1.  So the formula COLUMN()-COLUMN($A$1)+1 works out to just COLUMN(); i.e. the current column number.
  • VLOOKUP("assembly", address range, column number, FALSE) searches the address range (data sheet) for a row (VLOOKUP refers to vertical search) that contains assembly in the first column, and returns the value from the specified column.

I used COLUMN($A$1) so you can insert a column to the left and have everything automagically shift.  For example, you might want to put =Main!$A1 into column 1 of the Consolidated sheet:

You’ll notice that blank cells on the parts data sheet appear as zeroes on the consolidated sheet.  If that’s a problem, see Display Blank when Referencing Blank Cell in Excel.  If your Main sheet contains part numbers that do not correspond to data sheets, you’ll get errors on your Consolidated sheet.  You can handle those with IFERROR().

Community
  • 1
G-Man Says 'Reinstate Monica'
  • 7,678
  • 22
  • 41
  • 87
  • Thanks for this G-Man this is just what i need, but there are issues. Some of my part numbers and hence the related worksheets have spaces in. This is not working where there are spaces. – Steve Hare Nov 13 '14 at 11:07
  • Just re-read your text - missed the bit about allowing for spaces! DOH. All good now. – Steve Hare Nov 13 '14 at 11:18
  • If this answer works for you, please “accept” it by clicking on the check mark. ([What should I do when someone answers my question?](http://superuser.com/help/someone-answers)) – G-Man Says 'Reinstate Monica' Nov 13 '14 at 17:31