Forum Discussion

RoseH915's avatar
RoseH915
Copper Contributor
Sep 17, 2022

Quote sheet referring to price list

Hello. I am trying to make a quote sheet. We have 300 products. I was hoping there was a way to autofill my quote sheet with Name, Price, Bundle Qty using some sort of reference tool.

 

So what I envision happening:

Sheet 2 has the products listed with Item #, Name, Price and Bundle for each product.

When I go to sheet 1 that has my template, I can enter the item # (or name or any sort of reference that would make this work) and Excel autofills the Name, Price and Bundle for that product into the appropriate cells on my template. Essentially "pulling" that data from sheet 2 to auto fill my cells.

Or I can pull from another workbook, or alternate columns within sheet 1 if need. Whatever Excel is capable of.

 

Is this possible?

Excel
Formulas and Functions
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Sep 17, 2022

    RoseH915 That's done with one of several lookup functions available in Excel, depending on your version. VLOOKUP or INDEX combined with MATCH work in older versions. XLOOKUP works in the most recent versions of Excel (MS365 and 2021).

  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 17, 2022

    RoseH915 Yes, as the previous commenter mentioned, there are many options to accomplish your objective.  The new Dynamic Array formulas are amazing.  Here an example formula available in Office 365 which returns the array of details you mentioned in a horizontal or vertical fashion. 

     

    This one in particular will return the name, price & bundle in a vertical layout.  To use a horizontal layout, just erase the "Transpose(" and the final ")".  

     

    =TRANSPOSE(VSTACK(Table1[#Headers],FILTER(Table1,(Table1[Item'#]=Sheet3!B1))))

      

Share

Resources