Question description:
This user has given permission to use the problem statement for this
blog.
I have a work book with multiple sheets. I need to search all sheets for specific text, and sum the quantities. This is an estimate form with multiple sheet that I am trying to generate a Bill of Material from the data on these multiple sheets.
Solved by K. J. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/08/2018 - 01:49
Hi
User
07/08/2018 - 01:50
hello
Excelchat Expert
07/08/2018 - 01:50
can you share the excel please
User
07/08/2018 - 01:53
this is a basic example of what I am trying to acomplish
User
07/08/2018 - 01:54
I need a formula to look for the "description" in all sheets
Excelchat Expert
07/08/2018 - 01:54
give me few minutes.. let me check the data..
User
07/08/2018 - 01:54
and generate a it in the BOM sheet with sums
Excelchat Expert
07/08/2018 - 01:55
Ok... help me if my understanding of the problem is correct..
Excelchat Expert
07/08/2018 - 01:56
You need a BoM sheet which would pick description from each sheet and then provide the sum of cost?
User
07/08/2018 - 01:56
correct, but provide sum of QTY needed
Excelchat Expert
07/08/2018 - 01:57
ok..
User
07/08/2018 - 01:57
basically so we can auto generate a BOM base off of multiple sheets allowing us to send the BOM sheet to distribution for pricing.
Excelchat Expert
07/08/2018 - 01:58
will the description be on column A across the sheets?
User
07/08/2018 - 01:58
not all sheets
User
07/08/2018 - 01:58
the descriptions will change per sheet
User
07/08/2018 - 01:58
some will mate sheet one, some will be totally different
User
07/08/2018 - 01:59
match*
Excelchat Expert
07/08/2018 - 01:59
How many sheets do you have?
User
07/08/2018 - 01:59
an estimate could have up to 20
User
07/08/2018 - 01:59
or more
User
07/08/2018 - 02:00
some may only have 3-5
Excelchat Expert
07/08/2018 - 02:01
ok.. will the keyword "description" be on row-2?
User
07/08/2018 - 02:01
yes
User
07/08/2018 - 02:01
on every sheet
User
07/08/2018 - 02:01
Headers will be identical on every sheet
Excelchat Expert
07/08/2018 - 02:02
ok..so after the column Description there will be QTY column.. and we will build a sum of the QTY
User
07/08/2018 - 02:02
correct
Excelchat Expert
07/08/2018 - 02:03
ok.. give me sometime to build a solution..
User
07/08/2018 - 02:20
any luck? I have been banging my head against the desk trying to figure this one out
Excelchat Expert
07/08/2018 - 02:20
Hey.. i'm trying to see if there is a formula based approach..
User
07/08/2018 - 02:21
no problem....I know this is more complicated
User
07/08/2018 - 02:21
and appreciate the help
Excelchat Expert
07/08/2018 - 02:21
i know there is VBA approach.. but we are not supporting VBA in this platform..
User
07/08/2018 - 02:21
ahh
Excelchat Expert
07/08/2018 - 02:21
we have another 20mins for this session..
User
07/08/2018 - 02:22
yea more than likely not enough time :)
Excelchat Expert
07/08/2018 - 02:22
:)
Excelchat Expert
07/08/2018 - 02:26
How familier are you with excel??
Excelchat Expert
07/08/2018 - 02:26
i think we might have a non-vba solution...
User
07/08/2018 - 02:27
somewhat
Excelchat Expert
07/08/2018 - 02:27
ok.. lets give a try..
Excelchat Expert
07/08/2018 - 02:27
i will ping you the steps.. in few mins
User
07/08/2018 - 02:27
ok
Excelchat Expert
07/08/2018 - 02:35
The non VBA solution I was thinking is..
Excelchat Expert
07/08/2018 - 02:36
Step-1.. across the sheets remove all the blank rows.
Excelchat Expert
07/08/2018 - 02:36
Steps-2 : Convert the data in each of the sheets o a table
Excelchat Expert
07/08/2018 - 02:37
Step-3 : insert a pivot table..
Excelchat Expert
07/08/2018 - 02:38
IN the pivot table fields you have an option More Table..
Excelchat Expert
07/08/2018 - 02:39
this would give you all the tables created in step-2
Excelchat Expert
07/08/2018 - 02:40
Step-4 : Build a relationship between description across each of the tables..keeping one of the table as primary..
Excelchat Expert
07/08/2018 - 02:40
Step-5 : Drag the description field to the row..
Excelchat Expert
07/08/2018 - 02:41
Step 6 : get the QTY from each of the tables into the Values area..
User
07/08/2018 - 02:43
but as data is changed on the sheet will it update in the pivot table?
Excelchat Expert
07/08/2018 - 02:43
CHeck sheet-5 in the attached excel
[Uploaded an Excel file]
Excelchat Expert
07/08/2018 - 02:43
yes.. you need to refresh the pivot
Excelchat Expert
07/08/2018 - 02:43
to do that.. right click on the pivot.. and click "Refresh"...
Excelchat Expert
07/08/2018 - 02:44
it will fetch the latest data across.. all the tables..
User
07/08/2018 - 02:46
perfect, I certainly appreciate it
Excelchat Expert
07/08/2018 - 02:46
Are you able to follow..
User
07/08/2018 - 02:47
yes
Excelchat Expert
07/08/2018 - 02:47
I have marked the session as done..
User
07/08/2018 - 02:47
thank you for your help!
Excelchat Expert
07/08/2018 - 02:47
please leave a feedback when you close the session..
Excelchat Expert
07/08/2018 - 02:48
It was a challenging problem to work on..
Excelchat Expert
07/08/2018 - 02:48
have a nice evening.. good luck..
User
07/08/2018 - 02:48
you too!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
