Answered

Apply an Excel cell value as a parameter in another Excel worksheet

(0) Share

Report

Posted on by markautomates

How to use Power Automate to select a value from an Excel worksheet and use it as a parameter value in another worksheet?

Categories:

Power Automate Desktop

All responses (7)

Answers (1)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

You have two options. You can write to excel for the value you found and place it somewhere in the second workbook to reference it locally, or you can write the formula in the second workbook to look at the cell in the first workbook. Do it once manually to see the excel formula syntax.
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Just to make sure of the right terminologies, you really meant workbook and not worksheet 🙂

Michael's suggestions are good to go with both the workbooks.
markautomates 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the suggestions! What I am really looking for is the how-to do this? Can you perhaps point to a tutorial where Power Automate is being used for this purpose?
markautomates 13 on at

Like
a
(0)

Report
Copy link

Link copied!

MichaelAnnis,

This is a crude explanation, but it might help:
For each record in worksheet1,
if worksheet2.column(x) = worksheet1.column(A) and
worksheet2.column(y) = worksheet1.column(B) then apply
worksheet1.column(C) to each formula found in worksheet2

I can do this with Excel formulas but I am not sure how I might do this with Power Automate Desktop?
Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

For PAD to do it would cause 2 loops on your data. 1 loop for each row in worksheet 1 and looping through to possibly find the matching row in worksheet 2. It's not a good time savings.

If you can do it with Excel formulas, then I would have PAD write the formula and copy it down to the last row. If that is not an option (such as "if worksheet 2 is not replaced by worksheet 1, then keep the value that already exists in worksheet 2", then put a new formula in a different column with that logic, and then overwrite the old column with the new column.

The other option is Excel VBA, if you know that, then PAD just opens up Excel, runs the macro, and is done. But the macro will be 100 times faster than PAD with this logic.
markautomates 13 on at

Like
a
(0)

Report
Copy link

Link copied!

MichaelAnnis,

It sounds like you're saying that, performance wise, it is best to do any complex logic in code instead of having PAD go through any complex looping and logic. Is that correct?
MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Absolutely. Excel VBA is 100x faster than PAD at anything Excel based.