I have attached a sample file. The problem is that I need to put the departments in alphabetical order, however, this messes up the data. If you sort the "Prod Hrs New" tab, then the data on "Prod Hrs by Month" are incorrect. Is there anyway to fix this given I have over 900 departments to go through. (I only did a small example on here though).
Help! Data is incorrect on Sheet 2 after Sort of Sheet 1
- Thread starter CodeRed
- Start date
eibi
Active Member
Tiago,
It looks like we've got bi-weekly periods in the source sheet, and those are being converted to monthly periods in the tabulation sheet.
So - - the period 07/12/14 on the source sheet is divided by 14 days (because it is a 14 day period), and then multiplied by 12 days because only 12 of the 14 were actually in July.
Similarly -- the period 08/09/14 on the source sheet is divided by 14 days and then multiplied by 4 because only 4 of the 14 days that it includes are from July...
It looks like we've got bi-weekly periods in the source sheet, and those are being converted to monthly periods in the tabulation sheet.
So - - the period 07/12/14 on the source sheet is divided by 14 days (because it is a 14 day period), and then multiplied by 12 days because only 12 of the 14 were actually in July.
Similarly -- the period 08/09/14 on the source sheet is divided by 14 days and then multiplied by 4 because only 4 of the 14 days that it includes are from July...
Not exactly. The issue is that I have multiple spreadsheets attached to the cells for the Prod Hrs by Month tab, but when we get a new facilities and I have to add them, it throws everything off when I have to put them in order. Mainly the past data. However, you were spot on about the calculations for the formula. Is there a way that I can sort the data and the formula would follow the location or division.
eibi
Active Member
There are a million ways to skin this cat.
Let's try this one first: In the Prod Hrs by Month table, use formulas to fill columns A, B, and C.
In A2, paste: ='Prod Hrs New'!A2
In B2, paste: ='Prod Hrs New'!B2
In C2, paste: ='Prod Hrs New'!C2
Drag these formulas down to fill the whole table.
Now, DON'T sort the data in the Hrs by Month table. Instead, go back to the Prod Hrs New table and sort your data there...
After it's all sorted, return to your tabulation table and see if it's what you want.
All best.
Let's try this one first: In the Prod Hrs by Month table, use formulas to fill columns A, B, and C.
In A2, paste: ='Prod Hrs New'!A2
In B2, paste: ='Prod Hrs New'!B2
In C2, paste: ='Prod Hrs New'!C2
Drag these formulas down to fill the whole table.
Now, DON'T sort the data in the Hrs by Month table. Instead, go back to the Prod Hrs New table and sort your data there...
After it's all sorted, return to your tabulation table and see if it's what you want.
All best.
eibi
Active Member
Red,
Don't be offended by my saying that you were asking the wrong question. You see, you didn't have a sorting problem, you had a faulty data correlation.
In fact, the root problem is that on your Tabulation worksheet, your formulas are written to calculate in the order that the data appears on the Raw Data worksheet.
So even when you sorted the names on the Tabulation worksheet, the calculations on the still followed the sequence of your Raw Data spreadsheet.
As I said, there are many ways to solve this problem -- and the solution I've given you admittedly isn't the most sophisticated. But it seemed the most appropriate based on the sample file you provided.
Hope this is a helpful explanation...
All best.
Don't be offended by my saying that you were asking the wrong question. You see, you didn't have a sorting problem, you had a faulty data correlation.
In fact, the root problem is that on your Tabulation worksheet, your formulas are written to calculate in the order that the data appears on the Raw Data worksheet.
So even when you sorted the names on the Tabulation worksheet, the calculations on the still followed the sequence of your Raw Data spreadsheet.
As I said, there are many ways to solve this problem -- and the solution I've given you admittedly isn't the most sophisticated. But it seemed the most appropriate based on the sample file you provided.
Hope this is a helpful explanation...
All best.
Last edited: