I am brainstorming on what would be the best method to compile data, but I keep finding issues with my ideas. I'm hoping some of you have a good solution I can use. Let me describe what I'm doing and looking to do.
I have a large workbook that has a summary page, which uses lots of formulas to extract my data from other tabs in the workbook. I actually create two versions of the same workbook. One workbook contains the data as-is and the other workbook contains the data after using the "Sort" command. Every day or so, I do a "Save as..." of the unsorted workbook to allow me to update the previous day's workbook with the newest data. I've found it to be a pain to reuse the sorted workbook because the formulas get screwed up after sorting.
My workbook also contains a "Results" tab. During my daily routine, I obtain results from the previous day and enter it on the results tab in the sorted workbook. The results then transfer to an "Actual" column on the summary page through formulas.
I intend to build a workbook to compile each day's Summary sheet from the sorted workbook onto a single tab so I can use it in regression analysis.
Since I reuse the unsorted sheet daily, I suppose I could create a template, then save it in the sorted format. Not sure how much I would need it in the original format, but wanting the option to have it that way is why I save it both ways. There isn't a way to unsort is there?
Now the other complicated part. For the data that I want to use in the regression, I am wanting to modify some of the formulas to use the actual values in the cells instead of the ranked values. In a stats class I had before, I was told rankings regressions don't work properly with numbers that are actually rankings. Please let me know if this is incorrect. I considered making a copy of the "Summary" sheet into a new workbook, modifying the formulas, refreshing the data each day, placing the results tab in the workbook instead, then copying the data to the compilation workbook. This won't work though anyway because the file name changes everyday. Maybe I could create the copy of the Summary page with the modified formulas, then paste just the values into the workbook. If this workbook was pulling the data from the other workbook, I could just copy the values that are being referenced for the lookups.
Maybe I'm overthinking it. If you have any ideas on which route you would take with this, please let me know. I'd love to hear your suggestions.
Thanks in advance.
I have a large workbook that has a summary page, which uses lots of formulas to extract my data from other tabs in the workbook. I actually create two versions of the same workbook. One workbook contains the data as-is and the other workbook contains the data after using the "Sort" command. Every day or so, I do a "Save as..." of the unsorted workbook to allow me to update the previous day's workbook with the newest data. I've found it to be a pain to reuse the sorted workbook because the formulas get screwed up after sorting.
My workbook also contains a "Results" tab. During my daily routine, I obtain results from the previous day and enter it on the results tab in the sorted workbook. The results then transfer to an "Actual" column on the summary page through formulas.
I intend to build a workbook to compile each day's Summary sheet from the sorted workbook onto a single tab so I can use it in regression analysis.
Since I reuse the unsorted sheet daily, I suppose I could create a template, then save it in the sorted format. Not sure how much I would need it in the original format, but wanting the option to have it that way is why I save it both ways. There isn't a way to unsort is there?
Now the other complicated part. For the data that I want to use in the regression, I am wanting to modify some of the formulas to use the actual values in the cells instead of the ranked values. In a stats class I had before, I was told rankings regressions don't work properly with numbers that are actually rankings. Please let me know if this is incorrect. I considered making a copy of the "Summary" sheet into a new workbook, modifying the formulas, refreshing the data each day, placing the results tab in the workbook instead, then copying the data to the compilation workbook. This won't work though anyway because the file name changes everyday. Maybe I could create the copy of the Summary page with the modified formulas, then paste just the values into the workbook. If this workbook was pulling the data from the other workbook, I could just copy the values that are being referenced for the lookups.
Maybe I'm overthinking it. If you have any ideas on which route you would take with this, please let me know. I'd love to hear your suggestions.
Thanks in advance.
