Using sumproduct function to count cell values on a remote workbook

[dylanb02]

Hey guys, thanks in advance for help..

I have a function that looks like this right now...
=SUMPRODUCT(--(
'http://someurl.com/[VARIABLE FIL
NAME.xlsx]SheetName'!$F$1:$F$65000="P"))

It calls out to a column in another sheet by URL and counts the numbe
of times a certain value is displayed in column F. Right now it onl
works when I manually type in the URL. What I want to do is someho
update VARIABLE FILE NAME to be the value in a specific cell. Any ide
how I could replace Variable File Name with a cell value

 

[Spencer101]

Hey guys, thanks in advance for help..

I have a function that looks like this right now...
=SUMPRODUCT(--(
'http://someurl.com/[VARIABLE FIL
NAME.xlsx]SheetName'!$F$1:$F$65000="P"))

It calls out to a column in another sheet by URL and counts the numbe
of times a certain value is displayed in column F. Right now it onl
works when I manually type in the URL. What I want to do is someho
update VARIABLE FILE NAME to be the value in a specific cell. Any ide
how I could replace Variable File Name with a cell value?

Hi,

Have a quick Google (other search engines are available ) for how t
use the =INDIRECT() function. This 'should' help you out with thi
query.

Hope it helps

S

 

[dylanb02]

Hi,

Have a quick Google (other search engines are available ) for how t
use the =INDIRECT() function. This 'should' help you out with thi
query.

Hope it helps

S.

Thanks... What I've found is that INDIRECT can't be used if the othe
documents are not open. Is that accurate

 

[dylanb02]

Thanks... What I've found is that INDIRECT can't be used if the othe
documents are not open. Is that accurate?

I've also tried playing around with INDIRECT.EXT, PULL functions bu
haven't had much luck. Any suggestions