Display value chosen from drop-down in a separate cell

[clairehermitage]

Hi there,

I'm looking for a method to display whichever option is chosen from a drop-down menu to appear in a separate cell.

I have a range of data which fall into particular categories, but the list is in no particular order. For example, in column H, I have days of the week...

MONDAY
MONDAY
SUNDAY
TUESDAY
THURSDAY
FRIDAY
MONDAY

When I filter on column H, I want whichever day I have chosen (let's say Sunday) to appear in cell K1.

The ultimate aim is to then use whatever appears in K1 as a printed header to a range of documents so that I don't have to split my single spreadsheet into separate worksheets.

I have tried linking cells using the Developer tab, and naming cells. I have played with using VLOOKUP to ignore blank cells, and even some minor VBA code, but cannot seem to get anything to work.

I've spend two days trawling the internet and forums, so am really hoping someone can help!

 

[onlyadrafter]

Hello,

Hopefully you will have calculation turned on. Have assumed H1 is a header.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    For MY_ROWS = 2 To Range("H" & Rows.Count).End(xlUp).Row
        If Rows(MY_ROWS).Hidden = False Then
            Range("K1").Value = Range("H" & MY_ROWS).Value
            GoTo CONT:
        End If
    Next MY_ROWS
CONT:
    Application.EnableEvents = True
End Sub

this code needs to go into the relevant sheet code window, not a standard module.

Is this suitable?

 

[clairehermitage]

Many thanks for your reply, however, this is a bit out of my depth!
Could you explain in a little more detail please?
Thanks again!

 

[onlyadrafter]

Hello,

When you are in Excel, press Alt + F11 (this will bring up the VISUAL BASIC window). In this window, select VIEW and then select Project Explorer (this may be visible already). It will be headed Project - VBAProject. Inside this window, you will see VBAProject (your file name). Under this you should see Microsoft Excel Objects (there will be a - or a + next to it). If it is + select the + (as in Explorer). This will show a list of the tabs in this spreadsheet. Double click on the sheet name, where you have the drop down, a new window will appear.

Paste the code provided earlier into here. Go back to Excel, and change the drop down selection and the new selection should appear in cell K1.