Auto-updating a table from anther table with Power Query

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
45
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,776
Office Version
  1. 365
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
45
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
6,776
Office Version
  1. 365
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
45
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,822
Messages
5,772,469
Members
425,760
Latest member
zj042060

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top