mistersink
New Member
- Joined
- Mar 2, 2020
- Messages
- 2
- Office Version
- 2013
- Platform
- Windows
I am trying to set up a shared spreadsheet that is being worked by several people. I have a simplified example attached. Basically there will be a ton of raw data that each person will be filtering and sorting constantly and there are a few comments columns that need to be filled out. The concern is if everyone works in the same sheet, that people might copy paste the wrong way when filtering and accidentally copy over other people's comments. The solution is to split up into multiple sheets and have each person work their own sheet, but I want one central location for the raw data.
I'm showing an example of 13 rows and 3 columns of raw data. the actual size will be about 7000 rows and 50 columns of raw data, so let me know if you think this method is a bad idea to work off of a shared server location (in terms of it being slow). The summary sheet has the source data (specifically columns A, B, and C are the raw data). The comments columns I want to pull from the apple sheet, banana sheet, orange sheet. I'm currently using index match.
The apple, banana, and orange sheets are to be worked by individuals. Each sheet is filtered by only product "apple" using get external data from microsoft query, pointing to the summary sheet.
The comments columns will be filled out on the apple, banana, or orange sheet, and they are part of the data pull from microsoft query, which references all of the summary sheet columns (see name manager for Sourcedata) this should allow the summary sheet to basically show everything in the other sheets combined every time the workbook is refreshed.
If I change one of the comments on the apple sheet, it will exist on both apple and summary sheets because of the index match on the summary sheet. This is true for banana and orange sheets.
The issue I see is sometimes the product might be switched fro apple to banana in the summary sheet, but if this happens the comments don't follow with it (unless it's changed from banana/orange to apple. Changing to apple seems to work and I'm guessing it's because it's first in my index match IFNA statement in the comments columns of the summary sheet. changing from apple to something else the comment gets lost and it defaults to "No Comment" per the index match IFNA statement on the summary sheet.
The other issue I see is if the apple guy wants to add another comment column that doesn’t necessarily need to go in the summary, the comment will not stay with its row if the data is resorted on the summary sheet.
Note: the comments i have in there now are trying to look like the raw data so i can test if the comments stay with the raw data rows appropriately.
In summary, I want the following:
1. raw data on the summary sheet (columns A, B, and C)
2. can add new lines of raw data to the summary sheet periodically (additional order#'s
3. see only specific product data on the apple, banana, orange sheets
4. enter comment 1 and comment 2 columns in the apple banana and orange sheets
5. show the comment from the product sheets on the summary sheet.
6. Able to sort and filter data on the summary, apple, banana, and orange sheets at any time and refresh whenever, and have multiple people working at the same time as a shared spreadsheet on a server.
7. able to handle 7000 rows and 50 columns of raw data (more than showed in the example)"
If you're getting the gist of what I want to accomplish, could you please recommend some fixes or perhaps a completely different approach to doing the same thing? If VBA is recommended I only know extreme basics of VBA.
I'm showing an example of 13 rows and 3 columns of raw data. the actual size will be about 7000 rows and 50 columns of raw data, so let me know if you think this method is a bad idea to work off of a shared server location (in terms of it being slow). The summary sheet has the source data (specifically columns A, B, and C are the raw data). The comments columns I want to pull from the apple sheet, banana sheet, orange sheet. I'm currently using index match.
The apple, banana, and orange sheets are to be worked by individuals. Each sheet is filtered by only product "apple" using get external data from microsoft query, pointing to the summary sheet.
The comments columns will be filled out on the apple, banana, or orange sheet, and they are part of the data pull from microsoft query, which references all of the summary sheet columns (see name manager for Sourcedata) this should allow the summary sheet to basically show everything in the other sheets combined every time the workbook is refreshed.
If I change one of the comments on the apple sheet, it will exist on both apple and summary sheets because of the index match on the summary sheet. This is true for banana and orange sheets.
The issue I see is sometimes the product might be switched fro apple to banana in the summary sheet, but if this happens the comments don't follow with it (unless it's changed from banana/orange to apple. Changing to apple seems to work and I'm guessing it's because it's first in my index match IFNA statement in the comments columns of the summary sheet. changing from apple to something else the comment gets lost and it defaults to "No Comment" per the index match IFNA statement on the summary sheet.
The other issue I see is if the apple guy wants to add another comment column that doesn’t necessarily need to go in the summary, the comment will not stay with its row if the data is resorted on the summary sheet.
Note: the comments i have in there now are trying to look like the raw data so i can test if the comments stay with the raw data rows appropriately.
In summary, I want the following:
1. raw data on the summary sheet (columns A, B, and C)
2. can add new lines of raw data to the summary sheet periodically (additional order#'s
3. see only specific product data on the apple, banana, orange sheets
4. enter comment 1 and comment 2 columns in the apple banana and orange sheets
5. show the comment from the product sheets on the summary sheet.
6. Able to sort and filter data on the summary, apple, banana, and orange sheets at any time and refresh whenever, and have multiple people working at the same time as a shared spreadsheet on a server.
7. able to handle 7000 rows and 50 columns of raw data (more than showed in the example)"
If you're getting the gist of what I want to accomplish, could you please recommend some fixes or perhaps a completely different approach to doing the same thing? If VBA is recommended I only know extreme basics of VBA.
