Regarding Dependent Dropdown in few cells

S

ssgautam007

New Member
Joined
May 22, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi , Can anyone help me with attached excel file . I have applied IFS function in all the cell after applying data validation in D4 column of worksheet.

My question is

Is there any way that i select "Shipping expenses for spare parts sales "in D4 of worksheet , it gives me option of dependent dropdown G4 and I4 so that i can select each item separately from three option related to "Shipping expenses for spare parts sales"?



Really appreciate your help.

Mastersheet

Work Sheet.xlsx
FGHIJK
4SCENARIOLedger accountCost centerDistrib. channelProduct groupProject
5Shipping_expenses_for_incoming_goods306000 - Freight costs4TA - Parts salesnot used715000 - Spare partsnot used
6Custom duties for incoming goods306500 – Custom duties on import4TA - Parts salesnot used715000 - Spare partsnot used
7shipping expenses for a specific truck (including invoices from Craters and Freighters)190010 – Deferred FreightN/AN/AN/Ause truck project (starts with 9)
8Packaging expenses (except Craters and Freighters)492000 - Packing expenses4FV - Truck salesnot usednot useduse truck project if possible (starts with 9)
9or
10100448 - Packing material
11Shipping expenses for spare parts sales495000 - Shipping expenses4TA - Parts sales800LAS715000 - Spare parts100651 - Air
12442LPN100654 - Domestic
13378999….100652 - Sea
14External storage493500 - Outside warehouse costs4TA - Parts sales800LAS715000 - Partsuse truck project if possible (starts with 9)
154FV – Truck sales442LPN105103 - T264or
16378999….105104 - T284100417 – Rents of building
17105106 - T276
18Late container returns455000 – Services4UN – Warehousenot usednot used100449 – Late container returns
19Assembly491100 – Field assembly costs4KB – After sales, Trucksnot used105103 - T264use truck project (starts with 9)
20105104 - T284
21105106 - T276
Master Sheet




Worksheet



Work Sheet.xlsx
DEFGHI
3SCENARIOLedger accountCost centerDistrib. channelProduct groupProject
4Shipping expenses for spare parts sales495000 - Shipping expenses4TA - Parts sales800LAS715000 - Spare parts100651 - Air
5442LPN100654 - Domestic
6378999….100652 - Sea
Work Sheet
Cell Formulas
RangeFormula
E4E4=IFS(D4='Master Sheet'!F5,'Master Sheet'!G5,D4='Master Sheet'!F6,'Master Sheet'!G6,D4='Master Sheet'!F7,'Master Sheet'!G7,D4='Master Sheet'!F8,'Master Sheet'!G8,D4='Master Sheet'!F11,'Master Sheet'!G11,D4='Master Sheet'!F14,'Master Sheet'!G14,D4='Master Sheet'!F18,'Master Sheet'!G18,D4='Master Sheet'!F19,'Master Sheet'!G19)
F4F4=IFS(D4='Master Sheet'!F5,'Master Sheet'!H5,D4='Master Sheet'!F6,'Master Sheet'!H6,D4='Master Sheet'!F7,'Master Sheet'!H7,D4='Master Sheet'!F8,'Master Sheet'!H8,D4='Master Sheet'!F11,'Master Sheet'!H11,D4='Master Sheet'!F14,'Master Sheet'!H14:H15,D4='Master Sheet'!F18,'Master Sheet'!H18,D4='Master Sheet'!F19,'Master Sheet'!H19)
G4:G6G4=IFS('Work Sheet'!D4='Master Sheet'!F5,'Master Sheet'!I5,'Work Sheet'!D4='Master Sheet'!F6,'Master Sheet'!I6,'Work Sheet'!D4='Master Sheet'!F7,'Master Sheet'!I7,'Work Sheet'!D4='Master Sheet'!F8,'Master Sheet'!I8,'Work Sheet'!D4='Master Sheet'!F11,'Master Sheet'!I11:I13,'Work Sheet'!D4='Master Sheet'!F14,'Master Sheet'!I14:I16,'Work Sheet'!D4='Master Sheet'!F18,'Master Sheet'!I18,'Work Sheet'!D4='Master Sheet'!F19,'Master Sheet'!I19)
H4H4=IFS('Work Sheet'!D4='Master Sheet'!F5,'Master Sheet'!J5,'Work Sheet'!D4='Master Sheet'!F6,'Master Sheet'!J6,'Work Sheet'!D4='Master Sheet'!F7,'Master Sheet'!J7,'Work Sheet'!D4='Master Sheet'!F8,'Master Sheet'!J8,'Work Sheet'!D4='Master Sheet'!F11,'Master Sheet'!J11,'Work Sheet'!D4='Master Sheet'!F14,'Master Sheet'!J14:J17,'Work Sheet'!D4='Master Sheet'!F18,'Master Sheet'!J18,'Work Sheet'!D4='Master Sheet'!F19,'Master Sheet'!J19:J21)
I4:I6I4=IFS(D4='Master Sheet'!F5,'Master Sheet'!K5,D4='Master Sheet'!F6,'Master Sheet'!K6,D4='Master Sheet'!F7,'Master Sheet'!K7,D4='Master Sheet'!F8,'Master Sheet'!K8:K10,D4='Master Sheet'!F11,'Master Sheet'!K11:K13,D4='Master Sheet'!F14,'Master Sheet'!K14:K16,D4='Master Sheet'!F18,'Master Sheet'!K18,D4='Master Sheet'!F19,'Master Sheet'!K19)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Scenario='Master Sheet'!$F$5:$F$21E4:I4
Cells with Data Validation
CellAllowCriteria
D4List=Scenario
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
You must log in or register to reply here.

Similar threads

W
  • Question
If formula without skipping lines
2 3
Replies
22
Views
1K
Fluff
Fluff
U
  • Question
Seeking Help with CELLS Property in VBA
2
Replies
13
Views
222
Alex Blakenburg
A
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
118
gtd526
gtd526
N
  • Question
Count - specific consecutive order
Replies
6
Views
480
Peter_SSs
Peter_SSs
A
  • Question
Ignoring values on a different sheet
Replies
3
Views
276
Joe4
Joe4

Forum statistics

Threads
1,214,540
Messages
6,120,107
Members
448,945
Latest member
Vmanchoppy

Share this page

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
Back
Top