Create excel spreadsheet and then build flow

(0) Share

Report

Posted on by Nickelbox

126

Hello,

I have been asked to build a spreadsheet that checks a column and if 20 days has elapsed, it sends an email reminder. I am looking for suggestions on how to best build a spreadsheet that allows me to automatically email an individual once exactly 20 days has elapsed. For example, do I add the individual's email address in a hidden column (or can that be done automatically based on the individual's listed name in a different column?) or is there a better way to accomplish this?

Thanks

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (0)

srduval 1,760 Moderator on at

Like (0)

Report
Copy link

Link copied!

Adding an email is the most fool proof, depending on how the names are entered. As data quality is the Achillies heel. Say someone goes by Bob but your AD system has them stored as Robert. If Bob Smith is typed into your spreadsheet you won't find a match for them doing a lookup, so the email will fail.

As for checking how much time has elapsed, is first you'll need to make sure the date coming out of excel is actually a useable date:

How to get date from Excel as a date in Power Automate flow (tomriha.com)

Then do a get future time action and use the excel date as your base date, and units of 20 days. Then a condition action to test if the output of the get future time is equal to today (i'd do a format date on both date/times to be yyyymmdd) if yes send email if no do nothing.

Was this reply helpful? Yes No
Nickelbox 126 on at

Like (0)

Report
Copy link

Link copied!

Do you know if a particular name is entered in an excel field, I can tell excel to correlate that name to a specific email address?

Was this reply helpful? Yes No
srduval 1,760 Moderator on at

Like (0)

Report
Copy link

Link copied!

I don't think so, the closest I can think of is do an import of the active directory (under get data, other datasources) then use data validation rules to make it a drop down menu for name. Then if you set the query to refresh when excel opens (I think that's still an option) it should stay up to date automatically.

Was this reply helpful? Yes No
Nickelbox 126 on at

Like (0)

Report
Copy link

Link copied!

So I thought about it and I think what I can do is use data validation to create a drop-down like you mentioned and then have the cell adjacent with an IF argument so it populates an email address in that cell based on what name is selected. I attempted the IF argument (function) with the below but am not sure what to put in the true/false fields in. Also, I am not entirely sure my IF argument is correct.

'=IF(F4=Anna,“anna@gmail.com”,IF(F4=Julie, “julie@gmail.com” ,IF(F4=Diana,”diana@gmail.com”)))

Was this reply helpful? Yes No
Nickelbox 126 on at

Like (0)

Report
Copy link

Link copied!

Actually, what I ended up doing is inputting the below in the adjacent field and am getting a #NAME? error. I'm not seeing what is wrong.

=IF(F4=Anna,“anna@gmail.com”,IF(F4=Julie, “julie@gmail.com” ,IF(F4=Diana,”diana@gmail.com”)))

Was this reply helpful? Yes No
srduval 1,760 Moderator on at

Like (0)

Report
Copy link

Link copied!

You might be better served creating a table of names and emails where the name you want to lookup is column a then any other information you want would start in b. Excel has a limit as to the number of nested if statements, not to mention the headache of trying to change it later and tracking all the parenthesis, one miss-type and you could spend hours figuring out where you forgot a parenthesis.

If you don't want to/can't use a vlookup, the switch expression might be better.

The Syntax of the SWITCH function includes 4 parameters. Out of 4 parameters of SWITCH functions, 3 are mandatory, and the fourth one is optional based on our requirement.

Expression: This is simply the value or cell we are trying to test the logic.
Value1: This the test value or logical value comparing to the Expression. We can give 126 logical tests.
Result1: If the first logical test is true, what should be the result we want.
Default or Value 2: Here, we can see two things. One is Default; if the first logical test is FALSE, what is the default value we want. The second one is Value2; this is the second logical test for the Expression.
Result 2: This is the result of the second logical test we are testing.

As for your IF statement you need to wrap the evaluated value in quotes since it's text.

=IF(F4="Anna",“anna@gmail.com”,IF(F4="Julie", “julie@gmail.com” ,IF(F4="Diana",”diana@gmail.com”)))

Was this reply helpful? Yes No
Nickelbox 126 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the information. I don't expect to have more than 4 individuals in my IF statement and they shouldn't change. I will, however, keep your suggestion in mind. Thank you so much for your assistance!

Was this reply helpful? Yes No