Sep 16 2022 07:38 PM
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?
Sep 16 2022 09:45 PM
@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).
Sep 16 2022 10:53 PM
@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))))
Sep 22 2022 01:01 PM
Thank you for your help! Worked like a charm. @Riny_van_Eekelen