Using sumproduct function to count cell values on a remote workbook

Hey guys, thanks in advance for help..

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

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

Originally Posted by dylanb02

Hey guys, thanks in advance for help..

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

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

Hi,

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

Hope it helps

S.

Originally Posted by Spencer101

Hi,

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

Hope it helps

S.

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

Originally Posted by dylanb02

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

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