Dropdown List Sum From Another Sheet

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
AB
1Dropdown ListOutput
Sheet1


Book1
ABCD
1NoBob Sales ValueJosh Sales ValueRicky Sales Value
21100133224
321252222
43653324
5422221
654411242
Sheet2
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,920
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
AB
1Josh Sales Value201
Sheet1
Cell Formulas
RangeFormula
B1B1=SUM(INDEX(Sheet2!B2:D6,0,MATCH(A1,Sheet2!B1:D1,0)))
Cells with Data Validation
CellAllowCriteria
A1List=Sheet2!$B$1:$D$1
 

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
could you post steps i posted the tired pasting the values but returns the error
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,920
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,920
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
both zero i entered the formula but no output

Capture.PNG
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,920
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top