how you link a drop down to populate information from other sheets? (quotation type of fo

[ravihotwok]

Afternoon everyone, its been a while but I need your expertise again. I am creating a new quotation database in excel, I have multiple sheets which are each for different services I do, for example one sheet is called carbon, the next powdercoating, ect

I have a main sheet called Quotation, in this I have a cell in column a with a heading called Service, this is a drop down list consisting of carbon, powdercoating ect, the the next cell in column b I want a drop down list which will be populated with the list of items and also cost for that relevant sheet.

each of the service sheets (carbon, powdercoating ect) are formatted the same, item description in one cell and price in the next

Can anyone offer any advice please?

many thanks

[JohnTopley]

Welcome back to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[ravihotwok]

Very basic sample form attached

[ravihotwok]

maybe use tables?

[tanasedn]

Hi,
For cell B3 in Quote worksheet select data validation >list and in Source box enter the formula:

Please Login or Register  to view this content.

In cell C3 insert a vlookup formula:

Please Login or Register  to view this content.

[JohnTopley]

Create named ranges: CARBON, CARBON_TBL, POWDERCOATING, POWDERCOATING_TBL

Data Validation in B3

list ==>INDIRECT(A3)

in C3

=IFERROR(VLOOKUP($B3,INDIRECT($A3 &"_TBL"),2,FALSE),"")

[ravihotwok]

ooof! thanks peeps, way above my head but I will give it a quick go and report back :-)

The option by Diana populates it great but I need the list of items to be a drop down list in cell B3 on the quote sheet if that makes sense

[ravihotwok]

hmm think ive got lost trying to do what you have explained John,

do I create a table called carbon_tbl? or just select the range and give that a name called carbon_tbl

[JohnTopley]

That is what post #6 does!

[ravihotwok]

please excuse my ignorance I didnt realise you had uploaded a file. Thats exactly what I am after! thank you very much. is there any limit to the number of columns? as in at the moment I have only item and cost, if I wanted item, cost, comments, production time is this ok? im guessing I just change the column number in the formula ?

[JohnTopley]

You can add to the named ranges as required and if you want to return, for example, "Production Time", then just add a VLOOKUP to reference the appropriate column.

If you have any problems then come back to the forum.

[ravihotwok]

Hello everyone,

currently my spreadsheet has 2 services carbon and powdercoating, how would i go about adding an additional 4 services? they would be in the same format (item and cost)

[JohnTopley]

If they are separate sheets, then replicate the process for the first two: you should not need to change the DV in "Quote".

I would have placed then all on one sheet : the named ranges could remain the same but reference the single sheet.

[ravihotwok]

Morning Everyone,

right I have created another generator, this time taking the advice and keeping all the services onto one sheet. I have attached it for anyone to look at. My question is would it be better to create a table for each service as the items for each service will grow so I want o=it to be easy to add to it and then sort ascending. hope this makes sense.

[ravihotwok]

can anyone help?? really close to getting this layout done I think im just hopeless with these formulas

[JohnTopley]

Posted example for "Flocking" removed "Electrostatic" from heading)

Question: is price dependent on Finish: if so you need to create tables based on Service/Finish/Price

[ravihotwok]

hello john thanks for taking the time to reply, the finish is simply to confirm the colour of the item, price wont change. The lists with item and cost are what dictates the price. Hope that makes sense, just downloading the file now and will have a gander. cheers

[ravihotwok]

on the unit price, is there a way to remove the drop down so that it just auto populates with the corresponding value? for example when flocking is cc the value will be 333?

[JohnTopley]

Created named range "Flocking_Tbl"

Removed DV from "Unit Price" and replaced with

=VLOOKUP(B7,INDIRECT(A7&"_Tbl"),2,FALSE)

so named ranges are value in A7 with "_Tbl" added.

NOTE: Excel will replace blanks with "_" (Underscore)in named ranges so "Injector Diagnostics" will become "Injector_Diagnostics"


so VLOOKUP becomes (for all)

=IFERROR(VLOOKUP(B7,INDIRECT(SUBSTITUTE(A7," ","_")&"_Tbl"),2,FALSE),"")

Added IFERROR to avoid error when changing the DV values

AND DV for "Description" becomes

=INDIRECT(SUBSTITUTE($A$7," ","_")) (for all)

I still think to need a more comprehensive pricing based on FINISH as well as ITEM (??)

[ravihotwok]

Morning John,

thats spot on mate thank you very much, as for finish, there are only really colours and glossy/matt finish for carbon. Neither affect price as materials are all the same price. Awesome help as always mate