Auto-updating a table from anther table with Power Query

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hey everyone,

I hope everyone is staying safe and happy!

Here are the basics of what I am working with:
  • Worksheet 1 has a table named PayRequest
    • Within this table is a list of vendors who need to be paid
    • This table contains the type of payment they want (Wire or Check) along with their banking information (if they want a wire)
    • Relevant Column Headers - There are 23 columns in the PayRequest table but these are the ones that matter for what I need to do right now
      • Vendor
      • Tax ID
      • Payment Type (Check or Wire)
      • Amount
  • Worksheet 3 has a table named CheckRequests
    • This table needs to only include the vendors who have requested a check
    • Column Headers
      • Bank Account - this is a dynamic drop down menu for the user to select which account the funds will be coming out of
      • Vendor - Name of Vendor from PayRequest table
      • Transaction Date - Manually entered data for date the payment is sent
      • Ref Number - Manually entered data
      • Memo - Tax ID from PayRequest table
      • Payment Amount - Amount from PayRequest table
I want to automatically enter the data for each vendor that requests a check into the CheckRequests table from the PayRequest table. The fields I need to auto-update in the CheckRequests table are: Vendor, Memo, & Payment Amount. I would like to do this in Power Query so the table updates to as many rows as needed.

I first tried just referencing the cells in the CheckRequests table to the common cell in the PayRequest table

=PayRequest[@[Vendor]]

This works but shows all vendors whether they want a wire or check.

I would like to use Power Query like I have for another table within the workbook. However, I don't believe I can include the drop down for the Bank Account which I need. I also haven't figured out how to add a blank column within Power Query for the two manually entered columns required.

The issues I am running into:
  • Entering a dynamic drop down menu in Power Query (if that is even possible which I don't think it is)
  • Enter blank columns for manual entry within Power Query
  • Having the CheckRequests table only bring in vendor data if the vendor selected check as their payment method

Any help would be greatly appreciated. If I need to explain this better please let me know. Thank you.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,932
Office Version
  1. 2019
Platform
  1. Windows
Please upload via XL2BB your sample data so that we can try to manipulate it without trying to recreate your worksheets from your descriptions.
 

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
41
Office Version
  1. 365
Platform
  1. Windows
I have uploaded a workbook with the details. I hope this is what you were looking for.

Table Workbook for MrExcel Question.xlsx
A
7
Payment Request

Table Workbook for MrExcel Question.xlsx
B
7
Check Requests


I have never used XL2BB so hopefully I did it correctly.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,932
Office Version
  1. 2019
Platform
  1. Windows
No data uploaded. You need to highlight the entire range. You have presented blank data sheets. Please try again.
 

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
41
Office Version
  1. 365
Platform
  1. Windows
How about this attempt

Table Workbook for MrExcel Question.xlsx
ABCDE
1RequestorTax IDVendorAmountPayment Type
2Joe12345Mountain Dew $ 5,000.00 Wire
3Joe67890InstaPot $ 10,000.00 Wire
4Joe15920Spotify $ 23,456.00 Check
5
6This is the PayRequest table in Worksheet 1
7
Payment Request


Table Workbook for MrExcel Question.xlsx
ABCDEF
1Bank AccountVendorTransaction DateRef NumberMemoExpenses Amount
2This will be a dynamic drop down from a named range located in a data worksheetVendor from PayRequest tableManually entered dateManually entered numberTax ID from PayRequests tableAmount from PayRequests table
3
4
5This is the CheckRequests table
6
7
Check Requests
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,643
Members
410,696
Latest member
JTrehan
Top