Formulas created by concatenating text from multiple cells.

[Alexandroid]

Hey everyone, This one might be hard to explain so please be patient with me.

What I'm trying to do is create a formula that will go into a closed workbook, and calculate the amount of times the letter "P" is in column "N:N" in specific worksheets of that workbook. I have the name of the workbook listed in Cell A4 of the workbook that is Openned (not the one I'm trying to gather the information from) and A8-A20 contains the name of the sheet within the workbook that is closed.

I've tried the following formula:

Formula #1
=SUMPRODUCT(--("'["&TEXT($A$4,"00")&".xls]"&TEXT($A8,"00")&"'!$N:$N"="P"))

The formula without pointing to cells A4 and A8 would look like this:

Formula #2
=SUMPRODUCT(--('N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N$:$N$="P"))


The problem is that if I use formula #1 from above, when I click "Evaluate Formula" it ends up finding all the correct text to complete the path that is needed, but it's putting that string between double quotes ("'N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N$:$N$") and the formula just returns 0 (zero) instead of the proper number of times it found the letter P in the column N. This isn't the first time I'm having trouble with the Quotes when creating a formula this way so if there is a solution to that, that is what I would like.


The reason I'm using the above formula is because the workbooks that this formula goes through are closed and I get the #REF:

=COUNTIF(INDIRECT("'["&TEXT($A$4,"00")&".xls]"&TEXT($A8,"00")&"'!$N:$N"),"P")

I'm sure I'm missing something to paint the picture of what I need so please let me know if you need any clarification!

Thanks

 

[wigi]

Hi

You need INDIRECT to piece together the cell reference, but INDIRECT does not work on closed workbooks...

 

[Alexandroid]

Yeah I know about the Indirect portion and I am looking for a solution that I can use with the workbooks staying closed. I don't want to have to open them all up just to get the values.

Thanks though! Still need a solution for this if anyone else has any ideas.

 

[shahfahad]

Spreadsheet Page Excel Tips: A VBA Function To Get A Value From A Closed File

This might help.

 

[Alexandroid]

Thanks Shahfahad,
I think I'm going to have to use VBA to get this done but if any one knows how to do this using a simple function, it would be greatly appreciated. With the SUMPRODUCT, I'm able to get the value to return using the full path, but when I used the dynamic concatenating it gives "0".
This works:
=SUMPRODUCT(--('N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N:$N="P"))
This doesn't:
=SUMPRODUCT(--("'N:\folder\anotherfolder\["&TEXT($A$4,"00")&".xls]"&TEXT($A8,"00")&"'!$N:$N"="P"))
When I run the function that doesn't work through "Evaluate Formula" in excel, the final product just before it actually processes the entire function is:
Evaluated Formula:
=SUMPRODUCT(--("'N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N:$N"="P"))
If you compare the Evaluated Formula with the "This works" formula from above,the only thing that is different is that there are double quotes added because the formula had to evaluate the internal TEXT() functions and it treats it like a string instead of as a path (which I figured should be a string either way).

Just to make the above abvious, here are the 2 formulas:
=SUMPRODUCT(--("'N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N:$N"="P")) - After Evaluation.
=SUMPRODUCT(--('N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N:$N="P")) -Formula with full path added manually.


Thanks again for your help everyone!

 

[RoryA]

There is no simple function. You need to use VBA, or simply use the edit links button rather than typing the workbook path in a cell when you need to change the source.

 

[Alexandroid]

Thank you RoryA,

If we forget about the closed workbook issue (I'm trying to get information on the links and connections as well), what I would really love to figure out is that following:

Why does this:
=SUMPRODUCT(--("'N:\folder\anotherfolder\["&TEXT($A$4,"00")&".xls]"&TEXT($A8,"00")&"'!$N:$N"="P"))

Turn into this with the extra double quotation marks at the start of the path and at the end of the $N:$N:
=SUMPRODUCT(--("'N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N:$N"="P"))

If I type the following without the quotes, then it works with the workbook closed:
=SUMPRODUCT(--('N:\folder\anotherfolder\[spreadsheet.xls]Sheet1'!$N:$N="P"))

Is there a reason why, when I use the formula to build the path from cells A4 and A9 (first formula of this post) it adds those double quotes which causes the formula not to get the correct data compared to if I write the formula with the correct path without the double quotes, it works with the workbook closed without having to create any links.

***********UPDATE*************
Looks like I can't post attachments yet (either haven't provided enough posts to the forum or it's a setting I'm not sure how to turn on) so this next part will not be useful until I figure out how to upload the file... If you would like to help me with this then maybe I can send you the files by private message instead?
***********END UPDATE**********

To make it easier to understand what I'm trying to achieve, I've created a 2 quick workbooks that I'm going to attempt to upload to this post.

Book1.xlsx = a summary file where I'm trying to get the above formulas to work. It is the workbook that will be open and gathering the results from multiple closed workbooks.
Book2.xlsx = an example of the closed workbook that the information is extracted from and gathered in book1.xlsx.

Thank you for all your helpe everyone!

 

[RoryA]

You cannot create a reference by simply concatenating bits of text together using '&' or CONCATENATE - only the INDIRECT function works in that way, and it doesn't work with closed workbooks, as you know.

As regards attachments, you cannot post them on this board. It's just not allowed. As it happens, I don't need to see what you're doing, I understand well enough.

 

[Alexandroid]

ahh I understand so using the INDIRECT fuction would solve my double quotes issue and concatenating problems but would cause the closed workbook issue....

Thanks for clearing that all up!

 

[RoryA]

Yep. You also originally had double jeopardy as COUNTIF won't work with closed workbooks either.