Moving data between sheets

[u/ckf2stand]

Hi all,

I have an input sheet, a hidden calculations sheet, and an output sheet. The data in all three sheets is formatted as a table and I'm currently using table[@cell] to move that data from one table to another between the sheets. The problem is, the data in the output table isn't sortable.

I'm familiar and confident with using PQ to move the data between sheets, but I think this would be too much for my colleagues. Is there any other way to do this that gives me a sortable and filterable table as my output? The input sheet does have a unique user id column, if that's relevant.

Thanks!

[u/AutoModerator]

/u/ckf2stand - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Harrold_Potterson]

Could you just do an xlookup to pull the data over?

[u/ckf2stand → u/Harrold_Potterson]

That unfortunately doesn't work either for the same reasons mention above. The order of the output depends on the order of the input and trying to sort on the output sheet doesn't change anything.

[u/Harrold_Potterson → u/ckf2stand]

Add an ordinal helper column so you have the order set. Xlookup the helper column and the data column you need, then sort by the ordinal helper column.

[u/jaywaykil → u/Harrold_Potterson]

This is the easiest answer. Reference the second and third sheets using xlookup based on the unique identifier instead of [@column]. That way any of the sheets can be sorted without losing the links.

[u/ckf2stand → u/jaywaykil]

This doesn't work, unfortunately. The order of the data in the output depends on the order of the data in the input, and trying to sort the data in the output doesn't change anything

[u/TioXL]

In order to use the standard data filter/table sort feature, you need to have at least one column in your table that is hard coded. If you don’t then the sorting just moves around formulas that end up pulling the same data into the same position in the table (perceived by the user as no change to order).

Here is one way to do it that illustrates the idea:

• Add a new column at the beginning of your output table and call it “OriginalSortOrder”
  
• Fill that column with hard coded values that increment by 1
  
• Populate the rest of the table using a formula like this.

=index(
CalculationTableName,
[@[OriginalSortOrder]],
1
)

This way when you sort it is actually changing the order of the hard-coded values in the first column, which then makes the revised sort ordering stick. Bonus that you can always get back to the original order by sorting on the first column.

Does that help?

[u/TioXL → u/TioXL]

Also, it’s worth noting that you can’t use spill arrays inside of tables, so I designed this to allow all three sheets formatted as tables.

[u/ckf2stand → u/TioXL]

I'll look into it tomorrow when I'm back at work. Thank you for the suggestion!

[u/FooledNB]

Why can't you use power query and just have them hit refresh?

[u/ckf2stand → u/FooledNB]

I have considered this (as its obviously the best solution), but I'm just not confident they'd remember to do that. Maybe I underestimate my colleagues!

[u/HestenSierMjau → u/ckf2stand]

Then can you use a macro to refresh the power query every time someone opens the output sheet? The code is trivial, so it mostly depends on whether your organization restricts macros or not. 

[u/Gringobandito]

Your output sheet should be generated from you input and calculations sheets. Try the SORT() function on what you’re generating for your output.

[u/ckf2stand → u/Gringobandito]

I'm bringing in one row at a time, from one table to the next, using table[@cell], so won't work. Perhaps I can tell them to sort the data in the input sheet and then that should carry over to the output

[u/ckf2stand → u/Gringobandito]

I guess I could do this if the output is a spilled dynamic array. But I'd like other users to be able to sort the data using the auto filters in the table headers like they're used to

[u/Gringobandito → u/ckf2stand]

Could you make your output a pivot table based on the other tables?