Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Creating different values based on 2 columns in linked spreadsheet

Copper Contributor

Hello

 

I have 2 spreadsheets and I need help creating formula as below.  I can do the first step but struggling on the second.

 

Breakdown

  • If there is a date in column P of Spreadsheet 1 then column D of Spreadsheet2 needs to state “advise pending”.
  • When there is a date in both column P and Q of the Spreadsheet1 then column D of Spreadsheet2 needs to state “advice implemented

Hope this makes sense.

 

Thanks in advance.

4 Replies

@fiscal775

 

What is the formula you're currently using? 

Do you need the formula in the same row of Spreadsheet 2 as the row with the date on Spreadsheet 1?

Or do we need to find the correct row? If so, how?

@Hans Vogelaar 

 

Hello Hans

 

Yes it will be the same numbered row in both spreadsheet.

 

That only updates if there's data in one column but not sure how to add to second part.

=IF('filelocation.xlsx]Mar - May 2024'!P2<>"", "Advise Pending", "")

best response confirmed by fiscal775 (Copper Contributor)
Solution

@fiscal775 

Use

 

=IF('filelocation.xlsx]Mar - May 2024'!P2<>"", IF('filelocation.xlsx]Mar - May 2024'!Q2<>"", "Advice Implemented", "Advice Pending"), "")

 
 
You're a star, you even correct my spelling mistake :)
1 best response

Accepted Solutions
best response confirmed by fiscal775 (Copper Contributor)
Solution

@fiscal775 

Use

 

=IF('filelocation.xlsx]Mar - May 2024'!P2<>"", IF('filelocation.xlsx]Mar - May 2024'!Q2<>"", "Advice Implemented", "Advice Pending"), "")

 
 

View solution in original post