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 <LiveU...@discussions.microsoft.com>
> 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 <LiveU...@discussions.microsoft.com>
> > > 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 -
>
>
|
Similar Threads
