ExcelBanter - Using sumproduct function to count cell values on a remote workbook

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Using sumproduct function to count cell values on a remote workbook (https://www.excelbanter.com/excel-worksheet-functions/446301-using-sumproduct-function-count-cell-values-remote-workbook.html)

dylanb02

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?

Spencer101

Quote:

Originally Posted by dylanb02 (Post 1602640)

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.

dylanb02

Quote:

Originally Posted by Spencer101 (Post 1602642)

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?

dylanb02

Quote:

Originally Posted by dylanb02 (Post 1602659)

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?

All times are GMT +1. The time now is 08:20 AM.