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.

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.

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.

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.

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 -

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 -

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?