Dropdown List Sum From Another Sheet

[varunwalla]

In Sheet1 Cell A1 should show a drop down list from sheet2 i.e

1. Bob Sales Value,
2. Josh Sales Value,
3. Ricky Sales Value

and Sheet1 Cell B1 their sum should be shown for e.g Bob Sales Value should add from sheet2 from b1tob5 cells respectively

Book1
	A	B
1	Dropdown List	Output
Sheet1

Book1
	A	B	C	D
1	No	Bob Sales Value	Josh Sales Value	Ricky Sales Value
2	1	100	133	224
3	2	125	22	22
4	3	65	33	24
5	4	22	2	21
6	5	44	11	242
Sheet2

 

[Fluff]

How about

+Fluff New.xlsm
	A	B
1	Josh Sales Value	201
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUM(INDEX(Sheet2!B2:D6,0,MATCH(A1,Sheet2!B1:D1,0)))

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=Sheet2!$B$1:$D$1

 

[varunwalla]

could you post steps i posted the tired pasting the values but returns the error

 

[Fluff]

Select A1 on sheet1 & on the data tab select Data Validation, change the Allow box to "List", then in the Source box put =Sheet2!$B$1:$D$1 & click ok

 

[varunwalla]

thanks now the values loads fine is it possible to display sum of Bob sales value by default without selecting from drop down. out of interest why 0 is used in the formula =SUM(INDEX(Sheet2!B2:D6,0,MATCH(A1,Sheet2!B1:D1,0))) what condition it matches or it is use case

 

[Fluff]

out of interest why 0 is used in the formula

Which 0, there are two of them?
You could use
=SUM(INDEX(Sheet2!B2:D6,0,IF(A1="",1,MATCH(A1,Sheet2!B1:D1,0))))
Which will sum col B if A1 is blank

 

[varunwalla]

both zero i entered the formula but no output

 

[Fluff]

The first 0 tells the index function to return all rows & the 2nd tells the match function to do an exact match.

Is sheet2 laid out as per your OP? If so make sure that your numbers are real numbers & not text.