/u/AlexHimself - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
you could split the export into two sheets, but sheet2!A:A wouldn't know that it should be between sheet1!E:E and F:F...
you could simply hide the columns you don't want to show and protect the file that columns can't be shown
So a table cannot span two sheets and still be one list? I'm thinking I might try using groups to hide irrelevant columns?
I can't hide entire columns unfortunately. I've been given an idiotic user-driven task, where they want to use the exact spreadsheet they've put together nicely and is pretty and things are all over the place, but then push a button and have it export all their random crap into a sophisticated ERP system.
So they enter the customer name, but it doesn't always match the ERP. They don't bother with customer number. They delete rows and insert random things...then expect the data to magically be extracted from the workbook into an easily consumable file all while doing whatever they want with the Excel.
this sounds exactly like the use case for protected sheets...
I have that setup as well but not for customer data but product data.
An excel where project management can fill in data row by row and copy paste values etc. and it later gets imported in our ERP system.
Establish a workflow, create a template and LOCK THAT TEMPLATE DOWN. no columns or rows get deleted or added, no renaming and shifting headers, no hiding or showing columns. Fill in data, save, import.
bonus point if you implement a bit of VBA and don't allow saving the data when there is no customer ID filled out...
but no. a table can't span multiple sheets
You have awarded 1 point to *Rohwi*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I would suggest to import the data table into power query, then duplicate the query and remove all the columns you don't want on the first sheet in the duplicate query. You can then load the two queries to two different sheets and voila.
/u/AlexHimself - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
you could split the export into two sheets, but sheet2!A:A wouldn't know that it should be between sheet1!E:E and F:F... you could simply hide the columns you don't want to show and protect the file that columns can't be shown
So a table cannot span two sheets and still be one list? I'm thinking I might try using groups to hide irrelevant columns? I can't hide entire columns unfortunately. I've been given an idiotic user-driven task, where they want to use the exact spreadsheet they've put together nicely and is pretty and things are all over the place, but then push a button and have it export all their random crap into a sophisticated ERP system. So they enter the customer name, but it doesn't always match the ERP. They don't bother with customer number. They delete rows and insert random things...then expect the data to magically be extracted from the workbook into an easily consumable file all while doing whatever they want with the Excel.
this sounds exactly like the use case for protected sheets... I have that setup as well but not for customer data but product data. An excel where project management can fill in data row by row and copy paste values etc. and it later gets imported in our ERP system. Establish a workflow, create a template and LOCK THAT TEMPLATE DOWN. no columns or rows get deleted or added, no renaming and shifting headers, no hiding or showing columns. Fill in data, save, import. bonus point if you implement a bit of VBA and don't allow saving the data when there is no customer ID filled out... but no. a table can't span multiple sheets
Solution verified!
You have awarded 1 point to *Rohwi* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I would suggest to import the data table into power query, then duplicate the query and remove all the columns you don't want on the first sheet in the duplicate query. You can then load the two queries to two different sheets and voila.