Please help

Copper Contributor

Hello, I would like to format an excel document where I have a sheet in which I enter out of all the possible payment methods, if the respective sites have them or not, and I want to take the inputs from that sheet into a big general one automatically, like import the data, and at the same time create a search criteria so that if u search the payment method it show only the sites which have it. Thanks in advance!

1 Reply
best response confirmed by MihaiCsiszer (Copper Contributor)


To achieve your goal of importing data from one sheet to another and creating a search criteria, you can use Excel's data manipulation features such as Power Query and filtering. Here's a step-by-step guide to help you accomplish this:

  1. Set up your sheets:
    • Create two sheets in your Excel workbook: one for entering the payment methods and their availability, and another for the general data view.
    • In the sheet with payment method data, ensure that each payment method is listed in a column and the corresponding availability is marked for each site.
  2. Import data using Power Query:
    • In the general data sheet, go to the "Data" tab in the Excel ribbon.
    • Click on "Get Data" or "From Table/Range" (depending on your Excel version).
    • Select the sheet with payment method data and choose the range of cells containing the data.
    • Import the data using Power Query, which will open a new query editor window.
  3. Transform the data:
    • In the Power Query editor, perform any necessary transformations, such as removing unnecessary columns or adjusting data types.
    • Rename the columns as desired, including the payment method column and the availability column.
  4. Load the data into the general data sheet:
    • Once you have finished transforming the data in the Power Query editor, click on "Close & Load" to load the data into the general data sheet.
    • Choose the appropriate option for loading the data, such as loading it to a table or directly to the worksheet.
  5. Apply filtering/search criteria:
    • In the general data sheet, select the table or range where the imported data is located.
    • Go to the "Data" tab in the Excel ribbon and click on the "Filter" button.
    • Enable filtering on the table, which will add filter arrows to each column header.
    • Click on the filter arrow in the payment method column and select the desired payment method(s) to filter the data based on availability.

Now, when you search or filter for a specific payment method in the general data sheet, it will display only the sites that have that payment method available.

Note: The steps provided here are general guidelines, and the specific steps and options may vary slightly depending on your Excel version. All steps/information have been created with the help of the AI, therefore without guarantee.