Know anything about Excel and consolidation? | |
Anonymous Coward User ID: 73105485 United States 01/25/2017 07:55 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 07:57 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? Yes I had to copy a worksheet to make an averages sheet and use the averages function for the consolidation. However when I check the box for links back to source data I keep getting that error message. Last Edited by The Serpent's Maiden on 01/25/2017 07:57 PM |
Anonymous Coward User ID: 73105485 United States 01/25/2017 07:57 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? If you can't, try creating the sum formula using commas to split the different ranges you're summing up. [link to support.office.com (secure)] ( 'Use a formula to consolidate data' section) |
Anonymous Coward User ID: 73105485 United States 01/25/2017 08:00 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? Yes I had to copy a worksheet to make an averages sheet and use the averages function for the consolidation. However when I check the box for links back to source data I keep getting that error message. You just need the averages from several ranges in one cell? And that cell that's holding that is in a different worksheet than the range? Both your link and mine say having the destination / average on the same sheet as the range causes the error. It works if you uncheck the link box, right? |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 08:02 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? If you can't, try creating the sum formula using commas to split the different ranges you're summing up. [link to support.office.com (secure)] ( 'Use a formula to consolidate data' section) Thank you for the link I really appreciate it. I am looking it over now. After reading it over quickly though it seems I am doing everything right. I will try using a comma...see if that helps. Thanks so much :) |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 08:05 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? If you can't, try creating the sum formula using commas to split the different ranges you're summing up. [link to support.office.com (secure)] ( 'Use a formula to consolidate data' section) Thank you for the link I really appreciate it. I am looking it over now. After reading it over quickly though it seems I am doing everything right. I will try using a comma...see if that helps. Thanks so much :) Okay wait...this is my formula...this is all I added... I can't add commas...grr...lol I don't know why this is so hard for me lol Averages!$A$4:$F$8 |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 08:07 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? Yes I had to copy a worksheet to make an averages sheet and use the averages function for the consolidation. However when I check the box for links back to source data I keep getting that error message. You just need the averages from several ranges in one cell? No not one cell multiple And that cell that's holding that is in a different worksheet than the range? no in the same worksheet Both your link and mine say having the destination / average on the same sheet as the range causes the error. It works if you uncheck the link box, right? Yes, but the directions say this exactly: Copy one of the sheet to create an Averages sheet. Use Average to consolidate work hours for all employees with links to the source data. Last Edited by The Serpent's Maiden on 01/25/2017 08:08 PM |
Anonymous Coward User ID: 63209803 United States 01/25/2017 08:11 PM Report Abusive Post Report Copyright Violation | I keep getting this error... Quoting: Serpent Maiden [link to www.helpinfo.com] So...could it be because I used a range for my formulas? I can't figure it out. I need to make a consolidation with links back to the source data but it won't let me. I've been messing with this for an hour. I have simple formulas too...I just used the SUM function for all of them. Perhaps that is why? This is so aggravating any help is appreciated! Thanks... Can you put the consolidated data in a new worksheet? If you can't, try creating the sum formula using commas to split the different ranges you're summing up. [link to support.office.com (secure)] ( 'Use a formula to consolidate data' section) Thank you for the link I really appreciate it. I am looking it over now. After reading it over quickly though it seems I am doing everything right. I will try using a comma...see if that helps. Thanks so much :) A good Excel source is this guy: [link to www.youtube.com (secure)] |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 08:18 PM Report Abusive Post Report Copyright Violation | If you can't, try creating the sum formula using commas to split the different ranges you're summing up. [link to support.office.com (secure)] ( 'Use a formula to consolidate data' section) Thank you for the link I really appreciate it. I am looking it over now. After reading it over quickly though it seems I am doing everything right. I will try using a comma...see if that helps. Thanks so much :) A good Excel source is this guy: [link to www.youtube.com (secure)] He makes it look so easy lol :) Thank You AC |
Anonymous Coward User ID: 73105485 United States 01/25/2017 08:23 PM Report Abusive Post Report Copyright Violation | Yes I had to copy a worksheet to make an averages sheet and use the averages function for the consolidation. However when I check the box for links back to source data I keep getting that error message. You just need the averages from several ranges in one cell? No not one cell multiple And that cell that's holding that is in a different worksheet than the range? no in the same worksheet Both your link and mine say having the destination / average on the same sheet as the range causes the error. It works if you uncheck the link box, right? Yes, but the directions say this exactly: Copy one of the sheet to create an Averages sheet. Use Average to consolidate work hours for all employees with links to the source data. This sounds to me like you have several sheets, and are creating yet another sheet, 'Averages'. Let's say the other sheets are months, so January, February, March. Let's say on those sheets, the range would be A2:A30. In sheet 'Averages', you'd hit that consolidate button. Then, in that dialog in the middle, you'd see, January!$A$2:$A$30 February!$A$2:$A$30 March!$A$2:$A$30 All the ranges have to be the same number of cells per worksheet, and the 'Averages' sheet can't be in that list up there. Using the second method, you'd click in a cell on the 'Averages' sheet and enter the below: =AVERAGE(January!$A$2:$A$30,February!$A$2:$A$30,March!$A$2:$A$30) |
Anonymous Coward User ID: 73105485 United States 01/25/2017 08:25 PM Report Abusive Post Report Copyright Violation | |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 08:30 PM Report Abusive Post Report Copyright Violation | ... Quoting: Serpent Maiden Yes I had to copy a worksheet to make an averages sheet and use the averages function for the consolidation. However when I check the box for links back to source data I keep getting that error message. You just need the averages from several ranges in one cell? No not one cell multiple And that cell that's holding that is in a different worksheet than the range? no in the same worksheet Both your link and mine say having the destination / average on the same sheet as the range causes the error. It works if you uncheck the link box, right? Yes, but the directions say this exactly: Copy one of the sheet to create an Averages sheet. Use Average to consolidate work hours for all employees with links to the source data. This sounds to me like you have several sheets, and are creating yet another sheet, 'Averages'. Yes! Let's say the other sheets are months, so January, February, March. Let's say on those sheets, the range would be A2:A30. In sheet 'Averages', you'd hit that consolidate button. However I don't need to use the other sheets as they have the exact same information. Thats why I just made a copy of one Then, in that dialog in the middle, you'd see, January!$A$2:$A$30 February!$A$2:$A$30 March!$A$2:$A$30 All the ranges have to be the same number of cells per worksheet, and the 'Averages' sheet can't be in that list up there. Oh did you see the formula I was trying to use? That's why then...but the directions are so generic. It does not tell me to use any other worksheets. It would make no sense to if it is the same data ya know? Using the second method, you'd click in a cell on the 'Averages' sheet and enter the below: =AVERAGE(January!$A$2:$A$30,February!$A$2:$A$30,March!$A$2:$A$30) |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 08:34 PM Report Abusive Post Report Copyright Violation | |
Serpent Maiden
(OP) User ID: 73116397 United States 01/25/2017 08:44 PM Report Abusive Post Report Copyright Violation | Well the other worksheets are labeled week 1 2 3 4 This is for class so it's an exercise out of my textbook. I will try to do it your way right now...see what happens :) Thank You!!! Quoting: Serpent Maiden I did it your way and It worked AC but I still don't think its right for some reason. Only because of the data I selected. I have to email my instructor. Damn it lol thanks again. I understand this consolidation stuff better now :) Last Edited by The Serpent's Maiden on 01/25/2017 08:44 PM |