Sep 19 2020 06:09 PM
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
Sep 20 2020 02:09 AM
Sep 20 2020 03:04 AM
Sep 20 2020 11:24 AM
@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.
Sep 20 2020 11:25 AM
@Rajesh_Sinha - forward you a sample spreadsheet of what I am looking to do. Thank you for the assistance/direction.
Best,
Renata
Sep 21 2020 12:23 AM
@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.
How it works:
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.
N.B. Replace shown data set with yours, and apply the array formula as been instructed.
Sep 22 2020 05:44 PM
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 selected
Sep 22 2020 08:25 PM
Sep 26 2020 01:41 PM
@Rajesh_Sinha - 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:
iMR Content Status | Comms |
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 Package | X |
4. Renewal/re-review of expiring asset (no change) | |
5. Update existing asset with Important Safety Information |
Sep 26 2020 01:50 PM
Whether cells F:O gets populated with an "X" would be determined by another table such as:
Comms | HCC | Legal | Med Affairs | Med Info | Privacy | ProjOwner | Proofreader | Regulatory | SMEs | |
1. Asset contains content not approved content | ? | X | X | ? | ? | ? | X | X | X | ? |
2. Asset contains previously approved content or resized/reformatted content (resized/reformatted should be noted in item desc) | X | X | X | ? | ||||||
3. Convention Package | X | X | X | X | ||||||
4. Renewal/re-review of expiring asset (no change) | ? | ? | X | X | X | |||||
5. Update existing asset with Important Safety Information | X | X | X | X | X | |||||
6. Derivative_Low Tier (Layout/Color Changes ONLY - No Content Changes) | ? | ? | X | X | X | |||||
7. Derivative_Minor (Limited New Content and/or Delivery Platform) | X | X | ? | ? | ? | X | X | X | ||
8. Derivative_Major (Extensive Changes, New Content and/or Delivery, Built from Approved Assets) | ? | X | X | X | X | X | X | X |
Sep 26 2020 04:50 PM
@Rajesh_Sinha 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",""))))))))
Sep 27 2020 02:42 AM
Sep 27 2020 02:49 AM
Sep 27 2020 06:38 AM
@Rajesh_Sinha - please see attached sample