SpreadEveryone: The Fetchland Excel wire

1 lurker | 98 watchers

run free Mar 2021 6:10pm, 12 Mar 2021 4,732 posts	0 run free When you create your relationships, you're creating a Data Model which you can view in Power Pivot. You will need to write a bit of DAX - which is a formula language in Power Pivot, which is a bit like Excel functions.... 1. Use the SUM function - like totaling up each column 2. Use the CALCULATE function - chameleon function - in this case have used it like a SUMIF in your example - so that the PivotTable will report as you want it
run free Mar 2021 6:43pm, 12 Mar 2021 4,733 posts	0 run free With the way your forecast table is designed, you will have to create measures (DAX functions) for each month. If you want a low maintenance solution, redesign your Forecast table and you only have to develop 2 measures..... and after all you have to do is add your data to the bottom of the Forecast table and refresh your PivotTable
chunkywizard Mar 2021 7:59pm, 12 Mar 2021 11,601 posts	0 chunkywizard Unfortunately I get given the forecast file from my customer so I can't change the format
run free Mar 2021 8:33pm, 12 Mar 2021 4,734 posts	0 run free There is always a work around - pull it into Power Query via Data - Get & Transform - From Table (if in Excel 2016) - Excel 2019 has Data - Get Data Unpivot the 3 columns - voila....
run free Mar 2021 8:34pm, 12 Mar 2021 4,735 posts	0 run free You can then just refresh to update
quimby Mar 2021 2:56pm, 17 Mar 2021 4,731 posts	0 quimby Hello. A small question. I suspect it can't be done, and you're let off if you can't solve it, as the question does not involve things within Excel. You can link from an email, say, to: \itisabigserverwitheveryonesjunkonitusers$quimbyWIPx2.xlsx but can you link to a single worksheet within the workbook? Ie open the book with that sheet to the fore? I tried: \itisabigserverwitheveryonesjunkonitusers$quimbyWIPx2.xlsx!Weasels but no joy.
quimby Mar 2021 2:57pm, 17 Mar 2021 4,732 posts	0 quimby There were double backslashes in there but Fetch ate 'em.
um Mar 2021 3:22pm, 17 Mar 2021 4,431 posts	0 um Try http : //path/to/Workbook.xls#SheetName!a1 (remove spaces!)
um Mar 2021 3:26pm, 17 Mar 2021 4,432 posts	0 um Otherwise the web implies adding a macro to the file to alwyas open a specific sheet (and cell) - if that would do?, eg atlaspm.com Private Sub Workbook_Open() Application.Goto Worksheets("Sheet3").Range("CD1025"), True End Sub
SPR Mar 2021 3:30pm, 17 Mar 2021 33,605 posts	0 SPR I was going to suggest the macro to open on the same sheet at all times. Never done a link to a specific tab.