Excel跨工作簿行数据自动填充及基于MS Forms响应批量生成供应商RFQ Excel表单的技术实现问询

Hey there! Let's tackle your two Excel automation questions step by step—since you don’t have coding experience, I’ll focus on no-code/low-code solutions that fit your skill set:

Absolutely, this is totally doable without coding! The easiest tool for this is Power Automate (it’s free for most Microsoft 365 users, and super beginner-friendly):

• Step 1: Prep your workbooks
  • Format both your source worksheet (where you’ll add new rows) and target worksheet (where data needs to land) as Excel Tables (select the range > go to Insert > Table). Tables let Power Automate easily recognize columns, so you won’t have to mess with cell coordinates.
• Step 2: Build the flow
  1. Open Power Automate and create a new "Automated cloud flow".
  2. Pick the trigger: Excel Online (Business) > When a row is added (use this if your files are on OneDrive/SharePoint) or Power Automate Desktop > Monitor Excel worksheet for new rows if you’re using local Excel files.
  3. Connect your source workbook and select the table you created.
  4. Add an action: Excel Online (Business) > Add a row into a table, then connect your target workbook and its table.
  5. Map source columns to target columns (just click dropdowns to match fields).
  6. Save the flow—now every new row added to the source table will auto-populate in the target workbook!

This is a bit more involved, but still totally achievable with Power Automate—no coding required. Here’s a simplified, beginner-friendly workflow:

First, prep your templates #

• For each vendor’s RFQ form, name the cells that need to be filled (right-click the cell > Define Name). For example, name the "Vendor Name" cell VendorName, "Product Request" cell ProductRequest, etc. This avoids having to remember cell coordinates like A1 or B5, making mapping way easier.
• Save all vendor templates in a dedicated folder on OneDrive/SharePoint (cloud storage works best for Power Automate access).

Build the Power Automate flow #

1. Create a new "Automated cloud flow" with the trigger: Microsoft Forms > When a new response is submitted, then select your Forms survey.
2. Add an action: Microsoft Forms > Get response details to pull all answers from the new submission.
3. Add an "Apply to each" loop (this handles multiple vendors selected in the form):
   • Make sure your Forms survey has a multi-select question where respondents pick which vendors to send RFQs to. Use this question’s answer as the loop input.
   • Inside the loop:
     1. Use the Copy file action: Select the vendor’s template from your template folder, copy it to a new "Completed RFQs" folder. Name the new file dynamically (e.g., RFQ - [Vendor Name] - [Today's Date]) using content from the form response.
     2. Use the Set cell value action (under Excel Online): Connect the copied file, select the named cell (like VendorName), and map it to the corresponding Forms response field. Repeat for every named cell in the template.
     3. (Optional) Add a Send an email action to attach the completed RFQ and send it to the vendor automatically.

Tips for beginners #

• Start small: Test the flow with just one vendor first, get that working, then add the multi-vendor loop.
• Use pre-built templates: Search Power Automate’s template gallery for "MS Forms to Excel"—tweak these to fit your RFQ needs instead of building from scratch.
• Lean into test mode: Power Automate lets you run the flow step-by-step to spot issues. You already know how to record Excel macros, so this is just a more visual version of that workflow!

You don’t need SQL or coding skills for this—all tools are designed for non-technical users. Start with the first question’s flow to get comfortable, then move to the RFQ automation. It’ll take a bit of trial and error, but you’ve got this!

内容的提问来源于stack exchange，提问作者sassifrasstic