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

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.

4 Replies

# Re: Creating different values based on 2 columns in linked spreadsheet

@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?

# Re: Creating different values based on 2 columns in linked spreadsheet

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

# Re: Creating different values based on 2 columns in linked spreadsheet

Use

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

# Re: Creating different values based on 2 columns in linked spreadsheet

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

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

# Re: Creating different values based on 2 columns in linked spreadsheet

Use

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