Vlookup using multiple worksheets

[Merlin]

I am creating an estimating worksheet and I would like to know the best way
to get data from my database worksheets into my detail estimate worksheet.
My thoughts were to utilize vlookup and place if I place a "1" in a certain
column, have that row of data pull into the estimate detail spreadsheet.

I tried to use Vlookup but it would not work for me =VLOOKUP(1,'Demo-CSA
Database'!C3:C5000,2,'Demo-CSA Database'!$C$3:$C$5000,false)
I know this is wrong. can someone help me figure this out? Do I have to
have matching data in my estimate spreadsheet? Is there a better function to
do this.

I want the data for items that I pick, by placing a 1 in column J to be
automatically pulled into the estimate sheet.

 

[JLatham]

First, the bad Yes, in a VLOOKUP() you are trying to match a known value on the sheet with
a matching value in the first column of a table somewhere else.

The way this would typically be handled would be for you to have a column on
your detail estimate sheet where you would select some unique product/service
identifier that also appears in your table. Quite often you can use Data -->
Validation with the entries of that first column in the table as the List for
the validation. If your tables are on some other sheet, you'll need to give
a name to the entries in that first column so you can set up the data
validation.

Then, with the data validation set up, you can put a VLOOKUP() formula in
cells on the same row that might look something like this (assuming your
first product/service entry is in A2)
=IF(A2<>"",VLOOKUP(A2,'Demo-CSA Database'!$C$3:$F$5000,2,False),"")
What that says is:
If A2 is not blank, then take the value in A2 and find it in the first
column (C) of the table on 'Demo-CSA Database' sheet that goes from C3 to
F5000 and if you find the match, return the value from the second column of
that table (column D) - oh, and the items in column C of the table don't have
to be in order.

Now, if you were to select all of the cells on the 'Demo-CSA Database'
sheet in C3 down to C5000 and while they are selected, type in a name like
ProductList in the 'Name Box' (that area right above the "A" column
identifier that shows what cell you are in) and end it with the [Enter] key,
then that group of cells becomes Named, or is now a Named Range, and you can
use it to set up Data Validation on the details sheet:

Select the cells you want to be able to choose items from on that sheet and
use Data ---> Validation and choose LIST as the "Allow" type entry, and in
the "Source" entry area that will then appear, type this formula:
=ControlList

and you're on your way.

Hope this helps.

 

[Eduardo]

Hi,
If I didn't missunderstand you you have a cost descrition to make an
estimation and you want to pull the cost from the database worksheet if that
is the case and assuming that your cost description is in column A, and in
the database is in column C and the cost is in column D do,

=sumproduct(A1='Demo-CSA Database'!$C$3:$C$5000),'Demo-CSA
Database'!$d$3:$d$5000)

copy the formula down,