Altering existing formula to work with closed workbooks?

Howdy! I have a formula that's loading a list of names based on how many times it's repeated on another document. It works wonderfully!

My only issue is, I found out after trying to show it to someone else that the workbook it's referencing needs to be open for it to return the correct values. I'm trying to figure out how to re-work the existing formula to still do what it's currently doing, but not be dependent on the source workbook being open. After 3 hours of trying to wrap my head around SUMPRODUCT and how to make that work with what I want to do, I figured I'd do what I always do when I"m ready to beat my head against my desk:

Ask y'all for help :P

Here's my formula currently:
{=IF(ROWS(B$15:B15)>$O$15,"",INDEX(MainDoc.xlsx!ColumnName,SMALL(IF(MainDoc.xlsx!UserName=$H$1,ROW(MainDoc.xlsx!UserName)),ROWS(B$15:B15))))}

I'm currently having to enter these as array formulas to make them work, and as mentioned the MainDoc.xlsx has to be open otherwise everything just says #VALUE!. Any ideas how I can achieve the same result?

Any input would be wonderful! Thanks, this forum is always crazy helpful.

If MainDoc.xlsx is a separate file that you are trying to access, then your syntax is incorrect - you should have the filename (plus extension) inside square brackets, followed by the sheet name on that file and preceded by the full path to that file, and all of that should be inside apostrophes before the !

I assume that ColumnName and UserName are named ranges defined within that file.

Also, as you are using a filename with an .xlsx extension, then you are clearly not using XL2003 anymore, as it states in your profile - please update this.

Hope this helps.

Pete

Such a simple error on my part. Thanks so much, that worked beautifully!

And I updated my excel year, good catch, didn't realize I'd mistyped it

Looks like I spoke to soon. I closed the workbook and re-opened to see if the new changes worked, and all the cells are returning #VALUE! still. Here's the updated syntax:

{=IF(ROWS(B$15:B15)>$O$15,"",INDEX('C:\test\[MainDoc.xlsx]Sheet1'!ColumnName,SMALL(IF('C:\test\[MainDoc.xlsx]Sheet1'!UserName=$H$1,ROW('C:\test\[MainDoc.xlsx]Sheet1'!UserName)),ROWS(B$15:B15))))}

Is the IF statement causing it to not return values properly or? The #VALUE! errors all go away and return with the correct information once I open the MainDoc.xlsx sheet. I'm just trying to get it to return the data without that sheet having to be open.

Glad to be able to help - thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

Heya! Sorry, your method looked to work, but then I closed the doc and re-opened fresh and it was still returning the #VALUE! error. Not sure if anything's wrong with the updated syntax I posted earlier or if I need to use a different function to retrieve the data from a separate file.

If you have ideas, I'm all ears

I've just been trying to search XL Help to see if there is some restriction on using array functions with closed workbooks, but since I upgraded to Windows 10 last week I've not been able to access Help. I think that might be the problem.

I tend not to use that approach (of using INDEX/SMALL/IF... through an array formula) - instead, I like to use a helper column to identify records which match the criteria and then you can just use a standard (non-array) INDEX/MATCH formula with the ROWS function to increment through the identified records.

Perhaps you can provide a mock-up sample workbook which has the same data layout as you use, with one sheet equivalent to the external file (makes it easier to test out) and the other sheet equivalent to your current file, and then I'll be able to trouble-shoot your formula more easily, and try out some alternatives.

Pete

Sure thing! Attached is a mockup book. Sheet1 would represent the document pulling the info, and the other tab would be a completely different, unopened document.

I copied/pasted my formulas but for some weird reason that I don't feel like digging into since it's a test sheet, the values it's returning are from the cell below where it's finding the info.

Basically, I just need it to do what it's doing but pull from a closed sheet

Hi there,

I've been out visiting, so sorry for the delay in responding. Thanks to Ford for stepping in.

The attached file shows the approach I had in mind, which is a bit different than Ford's - I've put all the formulae in Sheet1, which is to be in the "Open" workbook, so that they get updated. I've also renamed the "This tab has to be a different doc" sheet to "Closed", as it is a bit easier to follow.

I've inserted two new columns (A and B) in Sheet1, for use as helper columns (coloured blue), and consequently your table gets shifted over. I've also set up a data validation drop-down in D1 where you can choose from the names listed in column N, making use of the named range "Names" - I've not used your other named ranges, as it is easier to copy a formula across if it uses column references. I didn't need the value that you had in C1 (formerly A1), so I've deleted that. I put this formula in A4:

=IF(Closed!$A2=$D$1,MAX(A$3:A3)+1,"-")

and this one in B4:

=IF(ROWS($1:1)>MAX(A:A),"-",MATCH(ROWS($1:1),A:A,0)-2)

The first one (which I would normally have in the other sheet if we were only working on one file) identifies the record(s) which match the criteria by giving each a unique sequential number, and the second one identifies the row in the Closed sheet where those records occur, all bunched up. By returning hyphens for non-matched records, you can easily see where the formulae are active - these should be copied down beyond the amount of data that you have in the Closed sheet (I've copied to row 29), and then the columns can be hidden if you want the sheet to look like it did before.

I then put this formula in C4:

=IF(OR($B4="",$B4="-"),"",INDEX(Closed!B:B,$B4))

which can then be copied across to F4. Note that the formula in column B means that we only have to calculate the MATCH function once for each record, rather than include it within an INDEX/MATCH in columns C to F. C4 and F4 need to be formatted as a date (the others are General), and then the formulae can be copied down as far as you think you need them.

You can test this out quite easily by changing the name in D1 and seeing the data change appropriately.

I'll now separate these out into two files, MainDoc.xlsx and Open.xlsx and put them into a C:/temp directory to test them out as independent files.

Hope this helps.

Pete

Okay, after an infusion of coffee I can now give you the next phase.

I've attached two files, ef838a_Open and ef838b_MainDoc, which you should save into the folder C:/temp with the same names. The ef838b_MainDoc file just comprises the Closed sheet with your table of data, and I have deleted your named ranges as I don't make use of them.

If you open the ef838a_Open file you must enable the automatic update of links, and then you can see that the formulae have changed as follows:

A4: =IF('C:\temp\[ef838b_MainDoc.xlsx]Closed'!$A2=$D$1,MAX(A$3:A3)+1,"-")

B4: =IF(ROWS($1:1)>MAX(A:A),"-",MATCH(ROWS($1:1),A:A,0)-2) (i.e. no change, as it just refers to data within Sheet1 of the Open book)

C4: =IF(OR($B4="",$B4="-"),"",INDEX('C:\temp\[ef838b_MainDoc.xlsx]Closed'!B:B,$B4))

If you change the name in D1 you will see the data change automatically.

As Ford pointed out before, you cannot just change the name of the ef838b_MainDoc file by renaming it - the way to do that is to open the file, then use File | Save As to save the file with a new name, and then the items in red above will also change. If you want to get data from your own version of the MainDoc file, then you can just remove the ef838b_ from the formulae in A4 and C4, and copy them down again (you may need to copy down further if you have more data than in your example file). I suspect also that you might have more columns in your real file, so you may need to copy the formula in C4 across further and apply appropriate formatting before copying those down.

Hope this helps.

Pete