IF/IFS across multiple workbooks for direct link to main workbook?

**Elijah** · 01-17-2021, 01:21 PM

Hi all,

This one may be a bit tricky. I have a hard time uploading a workbook sample since the data is sensitive and there are three workbooks, but if needed be, I'll try to create a mock-up which I can upload for testing.

Details:

I have Workbook 1 which is always open. This is my main workbook.

This workbook have a column (H) which imports/links data in a column from Workbook 2 or 3, but should only do so if the dates in WB 1 and WB 2 or 3 are equal.

It's random if I have WB2 or WB3 open (or updated) at the same time, so the idea is to create link which will grab data from whichever WB is open.

Following this, I wanted to create an IF statement which

first checks if WB1 Date = WB2 Date and if so, returns values from column E in WB2.

if not, it should check if WB1 Date = WB3 Date and if so, return values from Columnn E in WB3.

I've tried implementing this both using a regular IF and finally IFS:

=IFS([@Date]=WB2.xlsm!SeriesTable[@Date],WB2!SeriesTable[@H],[@Date]='WB3'!SeriesTable[@Date],'WB3'!SeriesTable[@H])

What invariably happens is that if I open WB1 without the other two - I get #REF upon receiving new data.

If I open WB2 - WB1 updates perfectly.

However, if I open WB3 (without first opening WB2), it's still #REF.

My conclusion is that perhaps IF/IFS does not work across multiple workbooks, then. And that I maybe just have to accept that this is not possible.

Any thoughts?

Thanks in advance.

**AliGW** · 01-17-2021, 01:25 PM

Pretty sure that IFS will work across workbooks only if both workbooks are open.

**Elijah** · 01-17-2021, 01:35 PM

It does seem like that may be the case, AliG. Thanks for responding.

What I was hoping/thinking was that if WB2 wasn't open, the IF statement would proceed to WB2 and retrieve data from there...

**Pete_UK** · 01-17-2021, 02:57 PM

Can't you use IF (rather than IFS) with AND ?

Pete

**Elijah** · 01-17-2021, 03:47 PM

Hi, Pete,

Would you be so kind to give me a suggestion on how to do that?

I did try a simple IF at first (had to translate it using a translator, so apologies if the syntax should be incorrect):

Please Login or Register  to view this content.

Thanks in advance.

**Pete_UK** · 01-17-2021, 05:20 PM

I have an old version of Excel on this PC, but I'm in the process of upgrading to a new PC in another room, so I'll get back to you on that.

It would help if you attached samples of your workbooks so I can click in the cells to get the structured references. Can you also tell me the full path(s) to your files.

Pete

**Elijah** · 01-17-2021, 05:24 PM

Hi, Pete,

Thanks. And no rush!

I realize attaching the workbooks would be the best, but I would have to take some time to anonymize them if so be and I'm not sure I can find time for that until tomorrow. If you want to or can spell out the pseudo-code/principle instead, I can try to finalize it on my own.

If not, I should be able to do it some time tomorrow.

Best regards,

Elijah

**Elijah** · 01-18-2021, 06:04 AM

Hi again,

I created 3 sample workbooks: WB1, WB2 and WB3 and did some testing of my own.

1) Open only WB1. You should get #REF on the linked column.

2) Open WB3. Copy and paste the date values into the table in order to "update" the table and make it correct. Nothing happens.

3) Open WB2. WB1 automatically updates with values from WB3. No changes are done in WB2.

So, it seems like it's exactly how Ali said it to be: both workbooks needs to be open. I assume the IF loops through both workbooks and if WB2 isn't there it stops from completing.

The simple solution is to simply have both workbooks open at once. It's no big deal. But if there's a way around it - I'd love to hear it.

**Bo_Ry** · 01-18-2021, 09:05 AM

Use normal reference instead of structured reference

=IF([@Date]=[WB2.xlsx]Ark1!$D8,[WB2.xlsx]Ark1!$B8,IF([@Date]=[WB3.xlsx]Ark1!$D$8,[WB3.xlsx]Ark1!$B8,""))

**Elijah** · 01-18-2021, 02:21 PM

Hey, Bo_Ry,

Thank you!

That did seem to work great. The only problem is that when I update my source table in WB1 (it copies data from an SQL DB) - I get #REF using this solution.

So, I suppose I just have to settle on needing to have both workbooks open at once and using the original formula.

Thanks.

Elijah

IF/IFS across multiple workbooks for direct link to main workbook?

LinkBack

Thread Tools

Rate This Thread

Display

IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Re: IF/IFS across multiple workbooks for direct link to main workbook?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] link data from another workbook to one main workbook

[SOLVED] Update Main Workbook from 3 other workbooks

Creating a Macro that Imports data from multiple workbooks into one Main workbook

Generate separate workbooks from main Workbook

Link multiple workbooks to master workbook (below each other)

Close all workbooks except for the main workbook

[SOLVED] Macro to link Sheets to main workbook

Bookmarks

Bookmarks

Posting Permissions