Numbers vs Text search

[Guest]

I am sure someone could abbreviate this lengthy formula for me and I'd be
appreciative, but my real problem is more perplexing to me. Here is the

formula:

=SUMPRODUCT(--('[FILE.xls]Sheet1'!$I$2:$I$1800=10),'[FILE.xls]Sheet1'!$U$2:$U$1800
+SUMPRODUCT(--('[FILE.xls]Sheet2'!$I$2:$I$1800=10),'[FILE.xls]Sheet2'!$U$2:$U$1800
+SUMPRODUCT(--('[FILE.xls]Sheet3'!$I$2:$I$1800=10),'[FILE.xls]Sheet3'!$U$2:$U$1800
+SUMPRODUCT(--('[FILE.xls]Sheet4'!$I$2:$I$1800=10),'[FILE.xls]Sheet4'!$U$2:$U$1800
+SUMPRODUCT(--('[FILE.xls]Sheet5'!$I$2:$I$1800=10),'[FILE.xls]Sheet5'!$U$2:$U$1800
+SUMPRODUCT(--('[FILE.xls]Sheet6'!$I$2:$I$1800=10),'[FILE.xls]Sheet6'!$U$2:$U$1800)

The =10 refers to an office number, and the above gets me the information I
need when the offices are numeric (10-19), but now they have offices 1A, 1B

and 1C, etc as well and the above does not work. I do not control the input
or format of FILE.xls. Any suggestions are welcome.

 

[Dave Peterson]

Didn't this kind of change work for you:

=SUMPRODUCT(--('[FILE.xls]Sheet1'!$I$2:$I$1800="1A"),
'[FILE.xls]Sheet1'!$U$2:$U$1800)
+.........

Would it be worth it to create another worksheet in file.xls that contains the
formulas you need.

Then you could just point at the cells you need.

It might make it quicker when file.xls is closed, too.

I am sure someone could abbreviate this lengthy formula for me and I'd be
appreciative, but my real problem is more perplexing to me. Here is the

formula:

=SUMPRODUCT(--('[FILE.xls]Sheet1'!$I$2:$I$1800=10),'[FILE.xls]Sheet1'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet2'!$I$2:$I$1800=10),'[FILE.xls]Sheet2'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet3'!$I$2:$I$1800=10),'[FILE.xls]Sheet3'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet4'!$I$2:$I$1800=10),'[FILE.xls]Sheet4'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet5'!$I$2:$I$1800=10),'[FILE.xls]Sheet5'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet6'!$I$2:$I$1800=10),'[FILE.xls]Sheet6'!$U$2:$U$1800)

The =10 refers to an office number, and the above gets me the information I
need when the offices are numeric (10-19), but now they have offices 1A, 1B

and 1C, etc as well and the above does not work. I do not control the input
or format of FILE.xls. Any suggestions are welcome.

 

[Guest]

Thanks for taking the time to help out. However, ="1A" does not pull the
information like =10 does.

I do not have control over file.xls so I cannot create another worksheet in
it. If I could I would replace the text office number 1A with 17 and enter
that into my formula and it would work fine.

Didn't this kind of change work for you:

=SUMPRODUCT(--('[FILE.xls]Sheet1'!$I$2:$I$1800="1A"),
'[FILE.xls]Sheet1'!$U$2:$U$1800)
+.........

Would it be worth it to create another worksheet in file.xls that contains the
formulas you need.

Then you could just point at the cells you need.

It might make it quicker when file.xls is closed, too.

I am sure someone could abbreviate this lengthy formula for me and I'd be
appreciative, but my real problem is more perplexing to me. Here is the

formula:

=SUMPRODUCT(--('[FILE.xls]Sheet1'!$I$2:$I$1800=10),'[FILE.xls]Sheet1'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet2'!$I$2:$I$1800=10),'[FILE.xls]Sheet2'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet3'!$I$2:$I$1800=10),'[FILE.xls]Sheet3'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet4'!$I$2:$I$1800=10),'[FILE.xls]Sheet4'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet5'!$I$2:$I$1800=10),'[FILE.xls]Sheet5'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet6'!$I$2:$I$1800=10),'[FILE.xls]Sheet6'!$U$2:$U$1800)

The =10 refers to an office number, and the above gets me the information I
need when the offices are numeric (10-19), but now they have offices 1A, 1B

and 1C, etc as well and the above does not work. I do not control the input
or format of FILE.xls. Any suggestions are welcome.