A quotation sheet has two jobs: show the right client data and price the right materials for the product that was chosen. Both can be done with three lists, one drop-down for each, and a handful of formulas. This tutorial builds it in the order the data flows.

The data: four tables and a helper sheet

The workbook has four sheets that hold data (Clients, Materials, Products and Issuer) and a Control sheet that holds the unique lists. Only the Quotation sheet is edited by the person quoting; the rest are filled once and extended later.

Quotation sheet layout with the client block, the product selector and the material list / Hoja Quotation con el bloque del cliente, el selector de producto y la lista de materiales

Clients sheet with the Client, Street, City, State, ZIP, Phone and Email columns / Hoja Clients con las columnas Client, Street, City, State, ZIP, Phone y Email

Materials sheet listing each supply with its price per unit / Hoja Materials con cada insumo y su precio por unidad

Products sheet with the Item, Material, Quantity per unit and Unit columns / Hoja Products con las columnas Item, Material, Quantity per unit y Unit

Issuer sheet with the names and phone numbers of the people who prepare quotations / Hoja Issuer con los nombres y teléfonos de quienes elaboran cotizaciones

Materials stores each supply with its price. Products links a product to the materials it needs, with the quantity per unit. Issuer lists who prepares quotations.

Lists that grow by themselves

A drop-down should pick up new rows without anyone editing it. The method here is a named range sized by OFFSET and COUNTA. In the Control sheet, the unique list of clients is:

=UNIQUE(OFFSET(Clients!$A$2,0,0,COUNTA(Clients!$A:$A)-1))

Read it from the inside out: COUNTA(...)-1 counts the filled cells in column A and subtracts the header; OFFSET starts at A2 and takes that many rows; UNIQUE removes repeats. In Microsoft 365 the result spills down on its own.

Then define the name in Formulas → Name Manager → New. The name Clients refers to the same idea applied to the Control list:

=OFFSET(Control!$C$2,0,0,COUNTA(Control!$C:$C)-1)

Name Manager window listing the defined names of the workbook / Administrador de nombres con los nombres definidos del libro

New Name dialog with the name Clients and a dynamic range in Refers to / Cuadro Nombre nuevo con el nombre Clients y un rango dinámico en Hace referencia a

The Quotation sheet then uses Data → Data Validation → Allow: List, with Source =Clients.

Excel ribbon with the Data tab selected / Cinta de Excel con la pestaña Datos seleccionada

Data tab ribbon showing the Data Tools group / Cinta de la pestaña Datos con el grupo Herramientas de datos

Data Tools group with the Data Validation button / Grupo Herramientas de datos con el botón Validación de datos

Data Validation dialog with List selected and the Clients name as Source / Cuadro Validación de datos con Lista seleccionada y el nombre Clients como Origen

Client data with INDEX and MATCH

Once a client is chosen in B9, the address line is built from the row that MATCH finds:

=TEXTJOIN(". ",TRUE,INDEX(Clients_DB,MATCH($B$9,Clients,0),{2,3}))&", "&INDEX(Clients_DB,MATCH($B$9,Clients,0),4)&" "&INDEX(Clients_DB,MATCH($B$9,Clients,0),5)

MATCH returns the row of the client, INDEX takes columns 2 and 3 (street and city) and then 4 and 5 (state and ZIP), and TEXTJOIN puts a period between street and city. Clients_DB is the same OFFSET pattern with a width: COUNTA(Clients!$1:$1) counts the columns. The validity date is =F5+30, with =TODAY() in F5.

A product drop-down and a material list that follows it

The product selector is a combo box (Form Control). If the Developer tab is missing, enable it in File → Options → Customize Ribbon.

Developer tab ribbon with the Design Mode button / Cinta de la pestaña Programador con el botón Modo Diseño

Excel ribbon with the File tab on the left / Cinta de Excel con la pestaña Archivo a la izquierda

File menu with Options at the bottom / Menú Archivo con Opciones al final

Excel Options on Customize Ribbon with the Developer check box ticked / Opciones de Excel en Personalizar cinta de opciones con la casilla Programador marcada

Insert the combo box from Developer → Insert, size it over rows 14 and 15, and open its properties.

Developer tab Insert menu with the Combo Box form control / Menú Insertar de la pestaña Programador con el control de formulario Cuadro combinado

Quotation sheet with the combo box placed over the product rows / Hoja Quotation con el cuadro combinado sobre los renglones del producto

Developer tab ribbon with the Properties button / Cinta de Programador con el botón Propiedades

In Format Control, set Input range to the Products name (unique products from the Control sheet), Cell link to $B$15 and Drop down lines to 5.

Format Control dialog with Input range, Cell link, Drop down lines and 3-D shading / Cuadro Formato de control con Rango de entrada, Vincular con la celda, Líneas de lista desplegable y Sombreado 3D

The linked cell holds a position number, not a name, so B14 translates it with =INDEX(Products,B15). To hide the number in B15, apply the custom format ;;; (three semicolons).

Format Cells dialog on the Custom category with three semicolons as the type / Cuadro Formato de celdas en la categoría Personalizada con tres puntos y coma como tipo

With the product in B14, the materials come from one FILTER in B18:

=FILTER(Materials,Products_List=$B$14,"Product not found")

It returns every material whose product matches B14, and the list grows or shrinks with the product. The next columns read each material back with INDEX/MATCH against the same names: quantity per unit times the quantity in D14, the unit, the price from Materials_Price, and the subtotal as quantity times price.

Totals and "add" buttons without macros

The original version used a macro to place totals under the list and ActiveX buttons to jump to the next empty row of each sheet. A spilled list needs neither. Put Subtotal, tax rate, tax and Total in a block to the right of the list, where the list cannot overwrite them, and make each "add" button a formula:

=HYPERLINK("#'Clients'!A"&(9+COUNTA(Clients!$A$9:$A$1000)),"+ Add client")

The link jumps to the first free row of the table; typing there extends it and every list that reads it.

Limits

  • OFFSET is volatile and COUNTA(A:A) counts everything in column A. A note typed below a list changes its size. A table column (=tblClients[Client]) avoids both problems.
  • FILTER, UNIQUE and XLOOKUP need Microsoft 365 or Excel 2021. ActiveX buttons do not run on Excel for Mac or Excel for the web.
  • Quantities are not rounded to purchase units. 0.075 boxes of screws is an estimate, not an order.
  • The tax rate is an input. Confirm the rate that applies to the transaction before sending a quotation.

Practice files

The screenshots above come from the original quotation file. The 2027 version of the workbook uses tables instead of OFFSET, the totals block and the "add" links, with fictitious data and no registration.

Quotation sheet with a client, a product and quantity chosen, the material list and the summary block / Hoja Quotation con un cliente, un producto y una cantidad elegidos, la lista de materiales y el bloque de resumen

If you want a quotation system built around your own price list, get in touch.