Creating an EQUIPMENT LIST with items from multiple sheets

[Hollow94]

Hi,

Firstly - i tried looking around for answers on this forum and had no luck that relates to my specific case. I am a fairly novice excel user and will require some guidance - I THINK VBA is what I need to get my head around, but this is a new concept for me. I'll give you some info as to hopefully contextualize what i require.

Scenario:
I am a self employed AV Integrator and I want to streamline how I quote and produce documentation for clients and myself. Generally my approach is;
- Make a quote on one excel document (for client)
- Copy the line items from the quote into a new document which i call "equipment list". This is the document I then work out some of the finer design details and use to order off. (for me, my accountant,my suppliers)

This can be confusing and leaves the chance of mistakes if I dont transfer all the data over 100% accurately, the solution I thought of is to produce a database of all my suppliers in one document (one supplier = one sheet) with all the relevant information - such as buy price, sell price, description, product number, etc, and then incorporate an equipment list that automatically adds and subtracts any products I enter a quantity >1 for - I imagine this being on a different sheet.

What I want to happen is:
I enter a quantity of a product on a sheet; eg "Sonance1", excel reads there is a cell with a value greater than 1 in the "quantity" column and adds the entire row to an "Equipment list" on a new sheet (EQU). Meaning now there is one sheet that i can use as a platform to make PDFs for myself, client, accountant and suppliers.

If possible i'd like the EQU to be able to understand that if I enter a value that is greater than 1, EG:3 - it will generate 3 lines of that product. This equipment list needs to be reliable to add and subtract lines. So if a quantity changes from 5, to 4. the sheet is aware of this and reduces the equipment list by 1 line.

I've attached a document I am working for so far with a template I've made, the empty equipment list page, and then one supplier price list as a test sheet. (there are hidden sheets). There is also a 'reference tab' which is what I use to tally up some key information (such as LAN ports, PoE budget, and Amplifier channels i need.).

Really appreciate anyone who takes the time to help a confused small business owner out! I'll keep a close eye on this thread to answer any follow up questions


Best,

[JeteMc]

Perhaps this will move your project a bit further.
1. Use Power Query (Get & Transform) to make a consolidated table.
2. Produce a pivot table based on the new table and only display those items with quantities greater than zero.
Note that this proposal doesn't generate multiple lines per product.
Let us know if you have any questions.

[Hollow94]

Thanks for your help - How would I implement this if i were to have multiple pages of data to create an equipment list out of?

[JeteMc]

From the Data tab choose Get data > From other sources > Blank query
In the query editor window type =Excel.CurrentWorkbook() in the formula bar and press enter -> Note that this is case sensitive i.e. use capital E, C and W <-
Use Keep rows and Remove rows until only the tables of equipment lists remain
Remove the column with table names and expand the remaining column
Select Close and Load.
In the attached the sheets that had data have been unhidden and the tables given names that I could recognize and produced a PQ table from those.
Let us know if you have any questions.