How "should" I do this...

%3CLINGO-SUB%20id%3D%22lingo-sub-2092138%22%20slang%3D%22en-US%22%3EHow%20%22should%22%20I%20do%20this...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2092138%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20in%20advance%20for%20your%20suggestions.%20Trying%20to%20wrap%20my%20head%20around%20the%20best%20way%20to%20implement%20a%20project.%20Have%20used%20Excel%20since%20DOS%20so%20I%20have%20a%20(somewhat)%20good%20grasp%20on%20getting%20it%20to%20do%20what%20we%20need%2C%20including%20a%20basic%20working%20knowledge%20of%20VBA.%20With%20that%20being%20said%20here%20is%20what%20I%20need%20to%20do...%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20a%20Contractors%20Estimating%20workbook%20with%20multiple%20sheets%20(each%20representing%20a%20room%20or%20location%20on%20the%20property).%20Each%20sheet%20has%20a%20list%20of%20items%20that%20%22could%22%20be%20replaced%20(pulled%20from%20a%20lookup)%20and%20while%20all%20sheets%20are%20different%20the%20base%20layout%20is%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20is%20to%20(programmatically%20-%20not%20cut%20and%20paste)%20incorporate%20the%20data%20from%20all%20the%20sheets%20(once%20completed%2Fupdated)%20into%20a%20single%20estimate%20sheet.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20provide%20a%20sample%20but%20was%20hoping%20someone%20has%20seen%20(or%20done)%20something%20similar%20to%20get%20me%20started.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2092138%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2092787%22%20slang%3D%22en-US%22%3ERe%3A%20How%20%22should%22%20I%20do%20this...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2092787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F940906%22%20target%3D%22_blank%22%3E%40bhamberg%3C%2FA%3E%26nbsp%3BSounds%20like%20something%20that%20could%20be%20done%20by%20using%20%22Get%20%26amp%3B%20Transform%20Data%22%20(a.k.a.%20Power%20Query)%2C%20provided%20you're%20NOT%20on%20a%20Mac.%3C%2FP%3E%3CP%3EIt%20can%20connect%20to%20data%20in%20multiple%20sheets%2C%20transform%20the%20data%20it%20finds%20in%20them%20and%20load%20a%20table%20back%20into%20a%20regular%20Excel%20sheet%2C%20or%20with%20Power%20Pivot%2C%20create%20pivot%20tables%20analysing%20the%20data%20as%20you%20wish.%20You%20can%20add%2Fdelete%20sheets%20on%20the%20go%20and%20it%20should%20automatically%20refresh%20with%20the%20push%20of%20a%20button.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20easy%20this%20will%20be%20depends%20on%20how%20your%20estimation%20sheets%20are%20structured%2C%20the%20output%20required%20and%20your%20own%20skills%20when%20it%20comes%20to%20using%20Power%20Query.%3C%2FP%3E%3CP%3EAn%20example%20workbook%20(delete%20any%20confidential%20info%20though)%20would%20help%20ascertaining%20what's%20possible%20and%20how%20to%20approach%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Thanks in advance for your suggestions. Trying to wrap my head around the best way to implement a project. Have used Excel since DOS so I have a (somewhat) good grasp on getting it to do what we need, including a basic working knowledge of VBA. With that being said here is what I need to do...  

 

We have a Contractors Estimating workbook with multiple sheets (each representing a room or location on the property). Each sheet has a list of items that "could" be replaced (pulled from a lookup) and while all sheets are different the base layout is the same.

 

What I need is to (programmatically - not cut and paste) incorporate the data from all the sheets (once completed/updated) into a single estimate sheet.  

 

I can provide a sample but was hoping someone has seen (or done) something similar to get me started. 

1 Reply

@bhamberg Sounds like something that could be done by using "Get & Transform Data" (a.k.a. Power Query), provided you're NOT on a Mac.

It can connect to data in multiple sheets, transform the data it finds in them and load a table back into a regular Excel sheet, or with Power Pivot, create pivot tables analysing the data as you wish. You can add/delete sheets on the go and it should automatically refresh with the push of a button.

 

How easy this will be depends on how your estimation sheets are structured, the output required and your own skills when it comes to using Power Query.

An example workbook (delete any confidential info though) would help ascertaining what's possible and how to approach it.