Dropdown dependencies

**Araujo1958** · 12-15-2010, 07:22 AM

Hi All
Hoping someone can help with a small problem I have?
I have a 2 worksheet file & both sheets are multi column.
Sheet 1 has a combination of drop downs & free txt fields but no calculations and feeds Sheet 2 has various formula and no direct entry into its fields.
Sheet1 Col R (Dropdown) feeds Sheet2 Col AA & Sheet1 Col K (yes/no dropdown) feeds Sheet2 Col S.
What I need to do is retain the normal usability but with the added that if Col R is selected it not only puts the normal feed through to sheet 2 col AA but also selects "No" in sheet2 Col S.
If you've managed to understand what I'm aiming at, is it possible & how????
Many many thanks for your anticipated help.

araujo

**Palmetto** · 12-15-2010, 11:12 AM

Details are lacking, but perhaps this will do what you want.
When a cell in column-R on sheet1 is selected (no changed), it make the cell in column-AA of sheet2 have a value of "No", using the same row as the selected cell.

This code must go into the worksheet module of Sheet1.
Note: sheet references are sheet code names, not worksheet (tab) names.

Please Login or Register  to view this content.

If this is not what you intended, then state your requirements more precisely and with more details, or upload a sample workbook that shows the desired results.

**Araujo1958** · 12-15-2010, 01:13 PM

Palmetto, thank you for your response which unfortunately does not appear to do what I require.
However, I think I was looking at this in slightly the wrong way. As "Sheet 1" feeds "Sheet 2" I can ignore Sheet 2. So the question is whilst still retaining an optional Y/N dropdown in Column K if Column R is selected and populated via a dropdown (with the options of Amendment or Adjustment) is it then possible for Column K to automatically default to "No"?
Hope that this is slightly clearer than my first attempt to explain.

Regards
Araujo

**Palmetto** · 12-15-2010, 04:19 PM

Revised code, which uses the worksheet_change event.
If the changed cell is in column-R and the cell value is not a null value then cell-K of the same row is change to "No".

Please Login or Register  to view this content.

**Araujo1958** · 12-16-2010, 06:58 AM

Palmetto,appreciated and I'm sure it's me but I inserted the code you provided via the VBA editor. I then entered text via the dropdown menu in column "R" and nothing appeared in Column "K". I'm sure I am doing something stupid...... once again HELP!!!
Thank You.

**Palmetto** · 12-16-2010, 09:53 AM

Did you put the code into the worksheet module? That is where it must go.

If not, then:

Right-click the worksheet tab and choose View Code
Copy and paste the code into the code pane (normally on the right side of the screen)
Close the VB Editor