I had a problem I had to work around. I think there has to be a better way, though.
I have reports that I would like to autoformat into a certain column arrangement, in a table that's sortable and filterable. Just have a table with columns A, B, C, D, E, and shift them to another table in the order of C, A, D, B, E, and maybe sorted numerically by column B. Some sums at the end.
Seems simple, right? And I need simpletons to use it. So ideally, their use should be as simple as pasting a report onto the sheet, maybe doing a data refresh, and done.
I could just do cell references. Make cell A1 reference cell C1, and A2 reference C2, etc. But then you'd have to sort the source to sort the organized output. Some higher-ups will be referencing it, and I want them to be able to use table sorts and filters.
I tried to use PowerQuery to yank the table data into a new table. This seems promising. However, when you paste in new reports, if the dataset is SMALLER, you have erroneous duplicated or excess rows at the bottom that mess up sums. Instructions to correct this introduce more steps prone to user error.
So far what I'm doing is having Sheet 3 house the raw data dump, Sheet 2 uses cell references to place these into a table (With an IF saying that if the cell equals zero, make it "" instead), and Sheet 1, the display sheet, is a powerquery drawing from the Table on Sheet 2, with the added detail to exclude blank rows.
It works. I can instruct them to just wipe sheet 3 and dump the raw data into Sheet 3!A1, viola, it works. A table that arranges itself, sums neatly, and that has sort and filter functions that don't need any special handling. They can't dump the data in a broken way as long as they follow instructions to clear the whole sheet, and the copy/paste is accurate - not sure how it can be made any easier than that.
Am I overcomplicating this? It feels like this SHOULD be incredibly easy. I feel like I'm lighting a furnace to kill a fly.
I have reports that I would like to autoformat into a certain column arrangement, in a table that's sortable and filterable. Just have a table with columns A, B, C, D, E, and shift them to another table in the order of C, A, D, B, E, and maybe sorted numerically by column B. Some sums at the end.
Seems simple, right? And I need simpletons to use it. So ideally, their use should be as simple as pasting a report onto the sheet, maybe doing a data refresh, and done.
I could just do cell references. Make cell A1 reference cell C1, and A2 reference C2, etc. But then you'd have to sort the source to sort the organized output. Some higher-ups will be referencing it, and I want them to be able to use table sorts and filters.
I tried to use PowerQuery to yank the table data into a new table. This seems promising. However, when you paste in new reports, if the dataset is SMALLER, you have erroneous duplicated or excess rows at the bottom that mess up sums. Instructions to correct this introduce more steps prone to user error.
So far what I'm doing is having Sheet 3 house the raw data dump, Sheet 2 uses cell references to place these into a table (With an IF saying that if the cell equals zero, make it "" instead), and Sheet 1, the display sheet, is a powerquery drawing from the Table on Sheet 2, with the added detail to exclude blank rows.
It works. I can instruct them to just wipe sheet 3 and dump the raw data into Sheet 3!A1, viola, it works. A table that arranges itself, sums neatly, and that has sort and filter functions that don't need any special handling. They can't dump the data in a broken way as long as they follow instructions to clear the whole sheet, and the copy/paste is accurate - not sure how it can be made any easier than that.
Am I overcomplicating this? It feels like this SHOULD be incredibly easy. I feel like I'm lighting a furnace to kill a fly.
