SpreadEveryone: The Fetchland Excel wire

1 lurker | 101 watchers

GlennR Oct 2022 12:46pm, 24 Oct 2022 66,184 posts	0 GlennR Yeah. Do it properly in a SUMIFS() table.
SPR Oct 2022 12:59pm, 24 Oct 2022 38,646 posts	0 SPR I think Glenn must have keyword alert for pivot tables 🤣
SPR Oct 2022 1:05pm, 24 Oct 2022 38,647 posts	0 SPR In regards to Larkim's question, doesn't how the values come out depend on what you have in the row and column sections?
larkim Oct 2022 1:19pm, 24 Oct 2022 19,436 posts	0 larkim Yeah, sumifs would be fine, but I've a big data table that I need to summarise by varying different options and I don't want to risk incomplete summarisation. My challenge is that I want to add a couple of calculated fields. e.g. imagine every row is a sales enquiry, and then I've also got a binary field for "turned into an offer" and another one "turned into a sale". So I've got a calculated field which is "turned into a sale / turned into an offer" to give a conversion percentage, and I want the data to come out on top of each other by year. e.g. 2020 2021 2022 Enq vol Conversion But then I want those two rows to show (for example) "Region" or "Month" or "Product Range" If it was in access it'd be a doddle to create a report to do it.
run free Oct 2022 2:03pm, 24 Oct 2022 5,217 posts	0 run free Hey Larkim - yes there is. Use the Filters portion of the PivotTable or Slicers BTW Am all good with PivotTables but find Calculated Fields limited and can produce crap outputs.
larkim Oct 2022 2:06pm, 24 Oct 2022 19,440 posts	0 larkim Maybe I need to do it another way to avoid the calc'd fields. But they seem so neat and tidy! Still can't fathom out a way to stack the outputs though. I know we shouldn't think of Excel as a presentation tool, but sometimes presentation of info is important too!
run free Oct 2022 2:13pm, 24 Oct 2022 5,218 posts	0 run free Also larkim to do this Glenn's way, convert your dataset into a Table (For JamieKai as well). Make sure your dataset is contiguous (no blank rows or columns) and has a header row. Select a single cell in the dataset and then select Insert Table. If the dataset has been correctly defined, the dialog should pick up the entire range. This will provide you the automation you're looking for. So JK if you have based your data validation off the table, when you add more data to your list, your data validation will provide the increased options Same for Larkim....if you're worried about data being missed out (which could also happen with a PivotTable) Finally Larkim - if you want something totally versatile then learn Power Pivot
larkim Oct 2022 2:21pm, 24 Oct 2022 19,442 posts	0 larkim I was worried about completeness if I used sumifs(); happy that the data is good. PowerPivot may be the answer, but probably haven't got the time to learn this afternoon!
larkim Nov 2022 11:26am, 14 Nov 2022 19,603 posts	0 larkim Over to google sheets... I'm just having a play around with some race results which are in html tables online. Google sheets has the lovely "=importhtml()" formula which brings an online table "live" into a spreadsheet. And with =unique({importhtml([tables1tuff]);importhtml([table2stuff]}) I can drop in all the results for all races so far into one nice table. But as the results don't include any info about which results they relate do, I'd like to automagically append a column which tells me which table it has pulled from. Is that do-able by wrapping it all up in somethng like =query(unique({blah}),Select col 1 as [something]) I've got it sort of working with a kludgy helper column that can make use of the fact that position 1 occurs only at the start of a new set of data, so I can increment something there, but it's not as nice as I'd like.
Sam Jelfs Nov 2022 9:12am, 17 Nov 2022 1,254 posts	0 Sam Jelfs Off topic, but thought it might entertain a few in here... A poem by Brian Bilston