Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Hello! I am an experienced user of Excel who is being quickly driven
to madness today by an incomprehensible error I've never see before. I have a spreadsheet with quality data by quarter for several facilities. Each facility has its own worksheet. My manager would like to see the data by facility for each quarter, necessitating four new worksheets. It seems like this should be an easy task. For my example, let's say I have worksheets FAC1, FAC2 and FAC3. Each one has 10 variables in rows (column A, rows 5-15) with both an "n" and a "%" column for each of four quarters (columns C & D for Q1, E & F for Q2, etc). The first two variables define the denominators for the other variables and therefore have no % figure, so the cells are merged. I have now added a new worksheet, Q1. (Q1 is a copy of FAC1 with the labels changed and data deleted to save time formatting.) It has 10 variables in rows (column A, rows 5-15) with both an "n" and "%" column for each of the three facilites (columns C & D are for Facility 1, E & F for Facility 2, etc). SO... In worksheet FAC1, C5-D5 are merged cells with a value "100," E5- F5 are merged cells with a value "110," and so on. In worksheet FAC2, C5-D5 are merged cells with a value "50." In worksheet FAC3, C5-D5 are merged cells with a value "36." In worksheet Q1, I want merged cells C5-D5 to display the value "100," E5-F5 to display the value "50" and G5-H5 to display the value "36." I entered the formula [='FAC1'!C5:D5] in Q1 C5-D5 and it happily displayed the value "100." But when I entered the formula [='FAC2'! C5:D5] in Q1 E5-F5, it returned a #VALUE error and said "formula omits adjacent cells." When I allowed it to "fix" the error, it adjusted the formula to [='FAC2'!C5-F5] and displayed the value from FAC2 merged cells E5-F5. In the error checking options menu, I turned off "formula omits cells in region," "inconsistant formula in region" and "number stored as text." The content of the cell was no longer flagged, but it still said "#VALUE." I deleted the formula and tried again - same result: #VALUE. But now the error message reads "a value used in the formula is of the wrong data type." I then changed all the cells with numbers to the "number" category in the cell formatting menu. No change. I deleted the formulas and re- entered them. No change. I then changed all the cells with numbers to the "general" category in the cell formatting menu. No change. I deleted the formulas and re- entered them. No change. I thought it might be the fact that the cells were merged, so I tried it with rows 7 and 8 - (i.e. Q1 F7 = [='FAC2'!D7]). #VALUE. I don't know what to do and this is a terrible way to start one's morning. Can anyone explain what I'm doing wrong? I have summarized multiple worksheets this way before and have never had this kind of trouble. I do not want to have copy and paste this data each quarter by hand, but that's what it's looking like right now. Thank you in advance for any and all help!! -Sydney |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Errrr, quite a bit to read ! Maybe posting a sample of your data will help understand easier? -- Pecoflyer Cheers ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31096 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
On Nov 18, 9:59*am, Pecoflyer
wrote: Errrr, quite a bit to read ! Maybe posting a sample of your data will help understand easier? -- Pecoflyer Cheers ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=31096 Sorry! I thought I had posted some of the data, but in prose. :) Worksheet: FAC1 A B C D E F G H I J 3 Variable Benchmark Q1 Q2 Q3 Q4 4 % n % n % n % n 5 100 110 107 113 6 90 95 94 97 7 10% 10 11% 12 9% 10 10% 11 Worksheet: FAC2 A B C D E F G H I J 3 Variable Benchmark Q1 Q2 Q3 Q4 4 % n % n % n % n 5 50 52 47 46 6 48 50 40 45 7 10% 5 11% 6 12% 5 10% 5 Worksheet: FAC3 A B C D E F G H I J 3 Variable Benchmark Q1 Q2 Q3 Q4 4 % n % n % n % n 5 36 30 40 32 6 35 30 35 31 7 9% 3 13% 4 5% 2 15% 5 Worksheet: Q1 A B C D E F G H 3 Variable Benchmark FAC1 FAC2 FAC3 4 % n % n % n 5 ='FAC1'!C5:D5 ='FAC2'!C5:D5 ='FAC3'!C5:D5 6 ='FAC1'!C6:D6 ='FAC2'!C6:D6 ='FAC3'!C6:D6 7 ='FAC1'!C7 ='FAC1'!D7 ='FAC1'!C7 ='FAC1'!D7 ='FAC1'!C7 ='FAC1'!D7 I hope that helps! Thank you! Sydney |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Nuts. That doesn't look right at all.
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Hi Sydney, my fingers getting somewaht older I try not to type too much. Could youplease add an xls file ( Go Advanced - Manage attachments) Thx (if the admin reads this one, I'm fried !) -- Pecoflyer Cheers ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31096 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Sorry - I don't see an "advanced" and my dummy data in a new sheet is
not doing the same thing. I really don't want to have to remake this whole file from scratch, but I can't share the actual data. I was really just hoping someone could tell me if this is a question of settings or something Excel just can't do. But now that I've tried it with a fresh workbook, I know that Excel CAN do it. I just don't know why it won't do it in the original document. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
On Nov 18, 12:05*pm, Sydney wrote:
Sorry - I don't see an "advanced" and my dummy data in a new sheet is not doing the same thing. I really don't want to have to remake this whole file from scratch, but I can't share the actual data. I was really just hoping someone could tell me if this is a question of settings or something Excel just can't do. But now that I've tried it with a fresh workbook, I know that Excel CAN do it. I just don't know why it won't do it in the original document. Sorry - by "this file" I mean the thing I have to do for work. I have a dummy data file ready to go - I just don't see how to attach it. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Nevermind - I've found a solution. ::sigh:: I'm sure there's a logical
explanation as to why single cells wouldn't copy any better than merged cells, but I changed the formula on the merged cells to [='FAC1'!C5] instead of [='FAC1'!C5:D5] and not only did those cells reference the correct number without an error message, but suddenly the other, single cell references below started working too. Just another reminder of how much there is in the world that I just don't understand. :) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Word of advice, lose the merged cells :)
-- ** John C ** "Sydney" wrote: Nevermind - I've found a solution. ::sigh:: I'm sure there's a logical explanation as to why single cells wouldn't copy any better than merged cells, but I changed the formula on the merged cells to [='FAC1'!C5] instead of [='FAC1'!C5:D5] and not only did those cells reference the correct number without an error message, but suddenly the other, single cell references below started working too. Just another reminder of how much there is in the world that I just don't understand. :) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing from multiple wksts - incomprehensible #VALUE error
Thanks - that seems to be the solution.
I just wish Excel was consistent. Or maybe I don't. Maybe I benefit from it's quirks and oddities more than I know. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Multiple Columns | Excel Worksheet Functions | |||
REF# error on summing ranges | Excel Worksheet Functions | |||
Summing on multiple conditions | Excel Worksheet Functions | |||
a similar convention for tabs/wksts as in for R[1]C[1]? | Excel Discussion (Misc queries) | |||
Summing with multiple criteria | Excel Worksheet Functions |