Apologies that I am not quite good enough at Excel to take advantage of the many answers there are available on this topic- as most of them make reference to VBAs which I am still learning.
I want to program one database ('Display') to have dynamic references to another database ('Source'), even when the second database is closed. I've been able to do this on some workbooks, but so far I am struggling to do so with the current project.
On the 'Display' database, I have an array formula that looks up a certain name from the relevant range in the 'Source' database, and then returns a value associated with that name (again from an array). Since there is only one value associated with each name, I have used an averageIF formula instead of VLookup or Match, etc. The formula is essentially as follows:
{=IFERROR(AVERAGEIF('C:\Users\name\Desktop\folder\[file.xlsx]Sheet1'!$D$1:'C:\Users\name\Desktop\folder\[file.xlsx]Sheet1'!$D$100, C1, 'C:\Users\name\Desktop\folder\[file.xlsx]Sheet1'!$FT$1:'C:\Users\name\Desktop\folder\[file.xlsx]Sheet1'!$FT$100)), "")}
The formula works fine when the 'Source' workbook is open, but ceases to work once the 'Source' workbook is closed. The Source workbook is large enough (500mb) that having the workbook open is problematic.
I know I should learn how to use VBAs properly, but failing that- are there any suggestions as to how to make this formula work correctly when the Source workbook is closed?
Bookmarks