Create Spreadsheet with Dynamic Data Population Capabilities

%3CLINGO-SUB%20id%3D%22lingo-sub-1688227%22%20slang%3D%22en-US%22%3ECreate%20Spreadsheet%20with%20Dynamic%20Data%20Population%20Capabilities%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1688227%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20have%20my%20spreadsheet%20column%20populate%20dynamic%20upon%20a%20drop%20down%20selection.%26nbsp%3B%20The%20choice%20in%20the%20drop%20down%20will%20fill%20in%20other%20pertinent%20data%20across%20the%20row%20within%20appropriate%20columns.%26nbsp%3B%20Is%20this%20a%20capability%20and%20if%20so.%26nbsp%3B%20Can%20someone%20walk%20me%20through%20how%20to%20do%20it%3F%26nbsp%3B%20Thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1688227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1688355%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20Spreadsheet%20with%20Dynamic%20Data%20Population%20Capabilities%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1688355%22%20slang%3D%22en-US%22%3Eit%20can%20be%20done%2C%20can%20you%20be%20more%20specific%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1688376%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20Spreadsheet%20with%20Dynamic%20Data%20Population%20Capabilities%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1688376%22%20slang%3D%22en-US%22%3EYes%20it's%20very%20much%20possible%2C%2C%2C%20if%20I'm%20not%20wrong%20I%20can%20say%20that%20you%20are%20in%20need%20of%20Dependent%20drop%20down%2C%2C%20please%20share%20some%20sample%20data%20%26amp%3B%20expected%20output%2C%20will%20help%20us%20to%20fix%20the%20issue.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I want to have my spreadsheet column populate dynamic upon a drop down selection.  The choice in the drop down will fill in other pertinent data across the row within appropriate columns.  Is this a capability and if so.  Can someone walk me through how to do it?  Thanks in advance

13 Replies
Highlighted
it can be done, can you be more specific?
Highlighted
Yes it's very much possible,,, if I'm not wrong I can say that you are in need of Dependent drop down,, please share some sample data & expected output, will help us to fix the issue.
Highlighted

@Ramiz_Assaf - Hi Ramiz -- thank you for responding.

 

I want to create a drop down list and depending on what is selected. the corresponding columns will be populated with pre-defined values...in this case a simple X to identify the need for that function to review.

 

I have 150 types for which each has 4 possible selections (the dependent list). Depending on what is selected from the 4 possible selections it will indicate across columns which functional area is impacted by simply placing an "X" in the corresponding cell within the row.

Highlighted

@Rajesh-S - forward you a sample spreadsheet of what I am looking to do.  Thank you for the assistance/direction.

 

Best,

Renata

Highlighted

@Renata830 ,,,

 

Since you have not shared any sample data therefore, I would like to use data I've recently used for project to create Dynamic Drop down, which is driven by array ( CSE ) formula.

 

Rajesh-S_0-1600671850938.png

 

How it works:

  • Find the source data in range A2:C20, has 2 main building type, has their 3 Entry & Size (color applied for visualization ).

 

  • An Array (CSE) formula in cell E2, creates unique list of Building Type.

    {=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$20),0)),"")}
  • N.B. Create Drop Down in cell E13 and use E2:E3 as List value.


    • An array (CSE) formula in G2, gets relates Entry Type value.

       {=IFERROR(INDEX($B$2:$B$20, MATCH(0, IF($E$13=$A$2:$A$20, COUNTIF($G$1:$G1, $B$2:$B$20), ""), 0)),"")}

    N.B. Create Drop Down in cell G13 and use G2:G3 as List value.


    • Cell H2 has an array (CSE) formula, gets related Overall Size.

      {=IFERROR(INDEX($C$2:$C$20, SMALL(IF(COUNTIF($E$13, $A$2:$A$20)*COUNTIF($G$13, $B$2:$B$20), ROW($A$2:$C$20)-MIN(ROW($A$2:$C$20))+1), ROW(A1)), COLUMN(A1)),"")}

    N.B. Create Drop Down in cell H13 and use H2:H10 as List value.


    • Finish array formula with Ctrl+Shift+Enter & fill down.
    • You may adjust cell references in formula and Drop Down List value as needed.
    • Formula returns Entry Type & Overall Size, depends on the data, at source in range A2:C20.
    • For neatness you may hide data appearing in E2:H10.

N.B.  Replace shown data set with yours, and apply the array formula as been instructed.

Highlighted

@Rajesh-S 

 

Here's an example of what I am trying to do.  I am still building out the data e.g. When an "X" should appear but I want to know how to apply a formula to populate the columns once a certain content type is selectedFilterDynamicTable.jpg

Highlighted
Finally you have provided the real data but late, & your need is different,,, if I'm not wrong you want to populate F2:M2 with X if certain value appears in Column E, having Drop Down, if so then you need a simple Nested IF.

For example: If Assets Contain appears in E2 then populate F2:M2 with X.

Write this is F2 =If($E2="Assets Contains","X", "")

You may extend it for multiple check,,,

=If($E2="Assets Contains","X", if($E2="No Assets Contains","Y", ""))

Fill it Right then Down.

Hope this works for you!!
Highlighted

@Rajesh-S - I emailed you directly so I'm not sure why you did not get my original send.  And this is different than what I'm asking.  The first condition is what is selected...the 2nd condition is whether or not an "X" should appear in columns F:O; Not all of them will be required. There is another table that identifies if column F through O should be checked with an X if a certain selection is made in column E.

 

Nevertheless - Rajesh - I tried the formula you provided and here is what I got:

 
=IF($E3="1. Asset contains content not approved","X",IF($E3="2. Asset contains previously approved content or resized/reformatted content (resized/reformatted should be noted in item desc)","Y",IF($E3="3. Convention Package","X",IF($E3="4. Renewal/re-review of expiring asset (no change)","X",IF($E3="5. Update existing asset with Important Safety Information","X", "")))))
 
Only 3 out of the 5 populated although the conditions were met. What am I missing here...
 
iMR Content StatusComms
1. Asset contains content not approved  
2. Asset contains previously approved content or resized/reformatted content (resized/reformatted should be noted in item desc)Y
3. Convention PackageX
4. Renewal/re-review of expiring asset (no change)  
5. Update existing asset with Important Safety Information  
Highlighted

@Renata830 

 

Whether cells F:O gets populated with an "X" would be determined by another table such as:

 

 CommsHCCLegalMed AffairsMed InfoPrivacyProjOwnerProofreaderRegulatorySMEs
1. Asset contains content not approved content?XX???XXX?
2. Asset contains previously approved content or resized/reformatted content (resized/reformatted should be noted in item desc)      XXX?
3. Convention Package X    XXX 
4. Renewal/re-review of expiring asset (no change)    ?? XXX 
5. Update existing asset with Important Safety Information    XX XXX 
6. Derivative_Low Tier (Layout/Color Changes ONLY - No Content Changes)    ?? XXX 
7. Derivative_Minor (Limited New Content and/or Delivery Platform)  XX???XXX 
8. Derivative_Major (Extensive Changes, New Content and/or Delivery, Built from Approved Assets)  ?XXXX XXX 
Highlighted

@Rajesh-S I tried it a fifth time and still can only get the first 2 in blue to populate the cell...I'm not sure what I am doing wrong.  Here is the entire formula:

 

=IF($E2="2. Asset contains previously approved content or resized/reformatted content (resized/reformatted should be noted in item desc)","Y",IF($E2="3. Convention Package","Y",IF($E2="1. Asset contains content not approved","Y",IF($E2="4. Renewal/re-review of expiring asset (no change)","Y",IF($E2="5. Update existing asset with Important Safety Information","Y",IF($E2="6. Derivative_Low Tier (Layout/Color Changes ONLY-No Conent Changes)","Y",IF($E2="7. Derivative_Minor (Limited New Content and/or Delivery Platform)","Y",IF($E2="8. Deriviative_Major (Extensive Changes, New Content and/or Delivery, Built from Approved Assets","Y",""))))))))

Highlighted
No I've not received any mail,, better upload the file here only, and let me download and examine the issue.
Highlighted
If I'm not wrong maximum 255 characters can be tested with IF,,, and you are violating the rule !!
Highlighted

@Rajesh-S - please see attached sample