Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am having some trouble with a long formula and multiple spreadsh
I have 5 separate spreadsheets that I am pulling information from. The
problem is I receive an error that my formula is too long. Not only that, but I don't even know if my formula will work. I want the total amount of the number "1" in cell H3 coming from a specific column in multiple spreadsheets that are on a specific date specified in G3. On the other spreadsheets the column with numbers in it is D and the dates are in column I. Is this possible? Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am having some trouble with a long formula and multiplespreadsh
It would help if you posted your formula.
If you are unsure that it works, perhaps you could try to write it for, say, two worksheets and test it out that way, and then extrapolate as required to encompass the other worksheets. Hope this helps. Pete On Jul 2, 2:25*pm, LiveUser wrote: I have 5 separate spreadsheets that I am pulling information from. The problem is I receive an error that my formula is too long. Not only that, but I don't even know if my formula will work. I want the total amount of the number "1" in cell H3 coming from a specific column in multiple spreadsheets that are on a specific date specified in G3. On the other spreadsheets the column with numbers in it is D and the dates are in column I. Is this possible? Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am having some trouble with a long formula and multiple spre
=SUMPRODUCT(--('X:\DEPT\Confidential\Confindential\[Candace - Confidential
Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3),--('X:\DEPT\Confidential\Confidential\[Candace - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"),(--('X:\DEPT\Confidential\Confidential\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3)),(--('X:\DEPT\Confidential\Confidential\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"))) I need to add three more spreadsheets. (I put confidential in places that I cannot show what information I have.) Thank you. "Pete_UK" wrote: It would help if you posted your formula. If you are unsure that it works, perhaps you could try to write it for, say, two worksheets and test it out that way, and then extrapolate as required to encompass the other worksheets. Hope this helps. Pete On Jul 2, 2:25 pm, LiveUser wrote: I have 5 separate spreadsheets that I am pulling information from. The problem is I receive an error that my formula is too long. Not only that, but I don't even know if my formula will work. I want the total amount of the number "1" in cell H3 coming from a specific column in multiple spreadsheets that are on a specific date specified in G3. On the other spreadsheets the column with numbers in it is D and the dates are in column I. Is this possible? Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am having some trouble with a long formula and multiple spre
I put a hyperlink on a worksheet called DataLinks.
In A1 the hyperlink links to X:\Dept\Confidential\Confidential\[Candace - Confidential Customer Communication Tracker.xls In A2 the hyperlink links to X:\Dept\Confidential\Confidential\[Chris - Confidential Customer Communication Tracker.xls So, couldn't my formula be something like this: =SUMPRODUCT(--(DataLinks!A1Sheet1'!$I$3:$I$590=G3),--(DataLinksA1Sheet1'!$C$3:$C$590="1"),(--(DataLinksA2Sheet1'!$I$3:$I$590=G3)),(--(DataLinksA2Sheet1'!$C$3:$C$590="1")) ?? "LiveUser" wrote: =SUMPRODUCT(--('X:\DEPT\Confidential\Confindential\[Candace - Confidential Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3),--('X:\DEPT\Confidential\Confidential\[Candace - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"),(--('X:\DEPT\Confidential\Confidential\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3)),(--('X:\DEPT\Confidential\Confidential\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"))) I need to add three more spreadsheets. (I put confidential in places that I cannot show what information I have.) Thank you. "Pete_UK" wrote: It would help if you posted your formula. If you are unsure that it works, perhaps you could try to write it for, say, two worksheets and test it out that way, and then extrapolate as required to encompass the other worksheets. Hope this helps. Pete On Jul 2, 2:25 pm, LiveUser wrote: I have 5 separate spreadsheets that I am pulling information from. The problem is I receive an error that my formula is too long. Not only that, but I don't even know if my formula will work. I want the total amount of the number "1" in cell H3 coming from a specific column in multiple spreadsheets that are on a specific date specified in G3. On the other spreadsheets the column with numbers in it is D and the dates are in column I. Is this possible? Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am having some trouble with a long formula and multiple spre
Simplifying this further, you seem to have:
=SUMPRODUCT(--('path[Candace_filename]Sheet1'!$I$3:$I$590=G3),-- ('path[Candace_*filename]Sheet1'!$C$3:$C$590="1"),(-- ('path[Chris_filename]Sheet1'!$I$3:$I$590=G3)),(-- ('path[Chris_filename]Sheet1'!$C$3:$C$590="1"))) and you could get rid of 2 pairs of brackets to make it: =SUMPRODUCT(--('path[Candace_filename]Sheet1'!$I$3:$I$590=G3),-- ('path[Candace_*filename]Sheet1'!$C$3:$C$590="1"),-- ('path[Chris_filename]Sheet1'!$I$3:$I$590=G3),-- ('path[Chris_filename]Sheet1'!$C$3:$C$590="1")) So, this is looking at the same ranges in two separate files, and is counting if column I =G3 and column C ="1" in both files. Does this work for the two files? You might need to change the "1" to just 1 if you have numbers in those cells rather than text values. In terms of reducing the length of the formula, can you change the names of the files? (Or, take a copy of them, rename them, and then access these copies?) If you had the files open at the same time, then you would not need the path in the formula, so that could also make it smaller. Hope this helps. Pete On Jul 2, 5:07*pm, LiveUser wrote: =SUMPRODUCT(--('X:\DEPT\Confidential\Confindential\[Candace - Confidential Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3),--('X:\DEPT\Confidential\Confidential\[*Candace - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"),(--('X:\DEPT\Confidential\Confidential*\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3)),(--('X:\DEPT\Confidential\Confidential*\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"))) I need to add three more spreadsheets. (I put confidential in places that I cannot show what information I have.) Thank you. "Pete_UK" wrote: It would help if you posted your formula. If you are unsure that it works, perhaps you could try to write it for, say, two worksheets and test it out that way, and then extrapolate as required to encompass the other worksheets. Hope this helps. Pete On Jul 2, 2:25 pm, LiveUser wrote: I have 5 separate spreadsheets that I am pulling information from. The problem is I receive an error that my formula is too long. Not only that, but I don't even know if my formula will work. I want the total amount of the number "1" in cell H3 coming from a specific column in multiple spreadsheets that are on a specific date specified in G3. On the other spreadsheets the column with numbers in it is D and the dates are in column I. Is this possible? Thank you.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am having some trouble with a long formula and multiple spre
Both sheets are formatted the same just different data.
Basically the same. A1 will be the same on both sheets, other than the value. At this time it would be hard to rename the file. What about my data link idea? "Pete_UK" wrote: Simplifying this further, you seem to have: =SUMPRODUCT(--('path[Candace_filename]Sheet1'!$I$3:$I$590=G3),-- ('path[Candace_Â*filename]Sheet1'!$C$3:$C$590="1"),(-- ('path[Chris_filename]Sheet1'!$I$3:$I$590=G3)),(-- ('path[Chris_filename]Sheet1'!$C$3:$C$590="1"))) and you could get rid of 2 pairs of brackets to make it: =SUMPRODUCT(--('path[Candace_filename]Sheet1'!$I$3:$I$590=G3),-- ('path[Candace_Â*filename]Sheet1'!$C$3:$C$590="1"),-- ('path[Chris_filename]Sheet1'!$I$3:$I$590=G3),-- ('path[Chris_filename]Sheet1'!$C$3:$C$590="1")) So, this is looking at the same ranges in two separate files, and is counting if column I =G3 and column C ="1" in both files. Does this work for the two files? You might need to change the "1" to just 1 if you have numbers in those cells rather than text values. In terms of reducing the length of the formula, can you change the names of the files? (Or, take a copy of them, rename them, and then access these copies?) If you had the files open at the same time, then you would not need the path in the formula, so that could also make it smaller. Hope this helps. Pete On Jul 2, 5:07 pm, LiveUser wrote: =SUMPRODUCT(--('X:\DEPT\Confidential\Confindential\[Candace - Confidential Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3),--('X:\DEPT\Confidential\Confidential\[Â*Candace - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"),(--('X:\DEPT\Confidential\ConfidentialÂ*\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$I$3:$I$590=G3)),(--('X:\DEPT\Confidential\ConfidentialÂ*\[Chris - Confidential Customer Communication Tracker.xls]Sheet1'!$C$3:$C$590="1"))) I need to add three more spreadsheets. (I put confidential in places that I cannot show what information I have.) Thank you. "Pete_UK" wrote: It would help if you posted your formula. If you are unsure that it works, perhaps you could try to write it for, say, two worksheets and test it out that way, and then extrapolate as required to encompass the other worksheets. Hope this helps. Pete On Jul 2, 2:25 pm, LiveUser wrote: I have 5 separate spreadsheets that I am pulling information from. The problem is I receive an error that my formula is too long. Not only that, but I don't even know if my formula will work. I want the total amount of the number "1" in cell H3 coming from a specific column in multiple spreadsheets that are on a specific date specified in G3. On the other spreadsheets the column with numbers in it is D and the dates are in column I. Is this possible? Thank you.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am having some trouble with a long formula and multiple spre
Have you tried it? Just to bring one item of data from a closed file?
I suspect you would need to use INDIRECT to get a correct cell reference, and this will not work with closed workbooks. Pete On Jul 2, 7:10*pm, LiveUser wrote: Both sheets are formatted the same just different data. Basically the same. A1 will be the same on both sheets, other than the value. At this time it would be hard to rename the file. What about my data link idea? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why does the page number appear in the middle of my excel spreadsh | Excel Discussion (Misc queries) | |||
Convert multiple columns into one long row | Excel Discussion (Misc queries) | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
Custom export with multiple rows for each row in original spreadsh | Excel Worksheet Functions | |||
How can I use a JPEG watermark as background in an excel spreadsh. | Excel Discussion (Misc queries) |