SOLVED

Looking to pull data from one workbook to another custom formatted workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-2654013%22%20slang%3D%22en-US%22%3ELooking%20to%20pull%20data%20from%20one%20workbook%20to%20another%20custom%20formatted%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2654013%22%20slang%3D%22en-US%22%3E%3CP%3EI%E2%80%99m%20looking%20to%20figure%20out%20how%20to%20pull%20data%20from%20one%20worksheet%20and%20drop%20it%20into%20a%20preformatted%20worksheet%20(same%20doc).%20Pivot%20Tables%20almost%20gets%20me%20there%2C%20but%20I%20think%20I%20may%20need%20to%20go%20a%20different%20direction%20given%20the%20specific%20layout.%3C%2FP%3E%3CP%3EI%20have%203%20worksheets%20(from%20left%20to%20right)%20attached%3C%2FP%3E%3COL%3E%3CLI%3ERaw%20Data%20%E2%80%93%20all%20the%20referenced%20data%3C%2FLI%3E%3CLI%3EPrint%20One%20Owner%20%E2%80%93%20the%20example%20shown%20is%20how%20I%20want%20the%20final%20product%20to%20look%20and%20is%20print%20friendly%20so%20you%E2%80%99re%20not%20constantly%20having%20to%20reset%20the%20Set%20Print%20Area%20or%20making%20several%20one-off%20worksheets.%20What%E2%80%99s%20not%20shown%20is%20a%20query%2Ffilter%20function.%3C%2FLI%3E%3CLI%3EAll%20Detail%20Formatted%20%E2%80%93%20is%20similar%20to%20Item%20%232%20above%2C%20but%20it%20shows%20all%20data%20grouped%20(and%20subgrouped)%20in%20a%20similar%20print%20friendly%20format.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2654013%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2656491%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20to%20pull%20data%20from%20one%20workbook%20to%20another%20custom%20formatted%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2656491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120509%22%20target%3D%22_blank%22%3E%40domdel%3C%2FA%3E%26nbsp%3BDoes%20this%20come%20close%20enough%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2657067%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20to%20pull%20data%20from%20one%20workbook%20to%20another%20custom%20formatted%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2657067%22%20slang%3D%22en-US%22%3EThat%20is%20definitely%20a%20lot%20closer%20than%20what%20I%20had%20attempted.%20Thank%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20I%20have%20a%20feeling%20my%203rd%20tab%20would%20be%20a%20challenge%20though.%3C%2FLINGO-BODY%3E
Occasional Contributor

I’m looking to figure out how to pull data from one worksheet and drop it into a preformatted worksheet (same doc). Pivot Tables almost gets me there, but I think I may need to go a different direction given the specific layout.

I have 3 worksheets (from left to right) attached

  1. Raw Data – all the referenced data
  2. Print One Owner – the example shown is how I want the final product to look and is print friendly so you’re not constantly having to reset the Set Print Area or making several one-off worksheets. What’s not shown is a query/filter function.
  3. All Detail Formatted – is similar to Item #2 above, but it shows all data grouped (and subgrouped) in a similar print friendly format.
5 Replies

@domdel Does this come close enough?

That is definitely a lot closer than what I had attempted. Thank you I have a feeling my 3rd tab would be a challenge though.
best response confirmed by domdel (Occasional Contributor)
Solution

@domdel Is this close enough?

@Jan Karel Pieterse thank you so much, this is great. Would you mind providing a summary? I noticed you included a slicer, but I have some questions. 

  1. For Rows: Lender, how did you suppress the expand/collapse (+/-) ? the field Field has subtotal = automatic, but when I attempt to recreate this, It treats this row with the expand/collapse toggle (I would like to know how to turn this off for this field). I tried setting subtotal to none on this row field, but it still shows the toggle icon.
  2. For Print One Owner worksheet, how would you set "Project Name" as the only expand/collapse field? (disabling the expand/collapse on Project Address)
I did not suppress any of the +/- icons and as far as I know this is an all-or-nothing setting for the entire pivot table (PivotTable Analyze tab, Options button, Display tab of that dialog).