Drop down list to edit other cells

[ManuGrim]

Hello,

A1 = drop down list with Jan, Feb, Mar, etc.
A2 = drop down list with 2014, 2015, etc.
For each month that I select from the drop down from A1 I wish to write in B1 a value. And if I select Feb, to be able to write in the same B1 cell (as now it is empty because I selected Feb) the corresponding value for Feb...and so on.

Is there a way you guys can help me?

Thank you in advance.

 

[Lymm]

Hi, a sample file, with sample data and out put may make it easier for someone to help you.

 

[Khalid NGO]

Hi ManuGrim,

Not understood your requirement clearly.
You need 2 drop downs.
1) in A1 for Months
2) in A2 for Years

Now what do you need in B1?

Also can you upload your sample file with some data?
Thanks

 

[ManuGrim]

Sorry. I cannot provide anything because I don't know how to do what I just explained. It is possible to do it by conditional formatting or do I need VBA?
I believe it should be on two sheets. One where I enter the data and on another sheet where the data is written.
Meaning,
if A1 = Jan than the value entered in B1 should be written in Sheet2 B3
if A1 = Feb than the value entered in B1 should be written in Sheet2 B5
And of course, if afterwords I select the same A1 = Jan, the value should be displayed in B1 as not to write over the previous written value.

Hope I was clear enough and it's super easy to make a formula or code

 

[ManuGrim]

Here is a draft

Thank you

 

[Khalid NGO]

Hi ManuGrim,
Do you need this ?
See the attachment.

 

[ManuGrim]

Haha Thanks Khalid...but that is just a Dropdown...I am able to do that...but take a look at the one posted above...That is what I want.

 

[bobhc]

Your question has been moved to correct section of the forum.

 

[ManuGrim]

10x

 

[Debraj]

If I have got your query correctly.. then I think, a simple IF will also work..

Check the attached..

But, still curious to know.. how you want to use this .. in real life..

 

[ManuGrim]

Thanks for the quick reply.

If I select Jan + 2014 and write in B1 the value 4 than this value is written in J2.(and the value stays there on J2)
THEN I select Feb + 2014 and write in the same B1 the value 5 which is written in J3.(and the value stays there on J3)
THEN I select Mar + 2014 and write in the same B1 the value 6 which is written in J4.(and the value stays there on J4)
and so on...

And after entering for the hole year, maybe I figure out that in Jan 2014 I made a mistake and need a value of 7 instead of a value of 4. Then I select A1=Jan and A2=2014 and the number 4 appears in B1 where I change it to 7.

I hope you guys understand what I need

 

[Debraj]

the query is moving toward VBA section.. Is this acceptable..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Application.EnableEvents = False
    On Error GoTo Deb
        Range("J2:U13").Cells([MATCH($A$1,$I$2:$I$13,false)], [MATCH($A$2,J1:U1,false)]) = Target
End If
Deb:
    Application.EnableEvents = True
End Sub

 

[ManuGrim]

Perfect
I knew it was easy.
Can you write another line to match the values from J2:U13 with B1 whenever I change the dropdown?
If I enter a value of 7 in Jun and then Select from the dropdown Aug, the value that I wrote (7) should disappear as I have no value in Aug. But if I have a value, to show it in B1.

And than I have everything I have asked for

 

[Debraj]

Hi Manu..

Just a small twist in the code.
check this one..

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    On Error GoTo Deb
    Select Case Target.Address
    Case "$B$1"
        Range("J2:U13").Cells([MATCH($A$1,$I$2:$I$13,false)], [MATCH($A$2,J1:U1,false)]) = Target
   
    Case "$A$1", "$A$2"
        [B1] = ""
    End Select
Deb:
    Application.EnableEvents = True
End Sub

 

[ManuGrim]

Thank you for the code. Works nicely.

But if the table is fully populated, I want B1 to bring the cell from the corresponding intersection of row and column.

Ex:
If A1=Jun
A2=2015
Value in row&column is 21 than B1 displays 21

If I change A1 to Jul
A2=2015
Value in row&column is 31 than B1 displays 31

Is there something like this?

Thanks again

 

[Swapnil]

Can you please whether this is what you wwanted...

 

[ManuGrim]

Yes,

Combined with the VBA code from before, where the data is written in the table due to the code, but B1 should also get the values from the table.
If "$A$1" and "$A$2" change than [B1] = "the value in table array"

 

[Swapnil]

A small modification in the above code.... Please confirm whether this is fine or not...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    On Error GoTo Deb
    Select Case Target.Address
    Case "$B$1"
        Range("J2:U13").Cells([MATCH($A$1,$I$2:$I$13,false)], [MATCH($A$2,J1:U1,false)]) = Target
   
    Case "$A$1", "$A$2"
        [B1] = Range("J2:U13").Cells([MATCH($A$1,$I$2:$I$13,false)], [MATCH($A$2,J1:U1,false)]).Value
   
    End Select
Deb:
    Application.EnableEvents = True
End Sub

 

[ManuGrim]

That is exactly what I am looking for!!!
Thank you very much!

 

[Debraj]

Thanks @Swapnil .. for your helping hands..

 

[ashish mehra]

Hi ManuGrim,

This reminds me of Data Table feature in What-If Analysis.

Great work by Deb & Swapnil.

Regards,
AM

 

[ashish mehra]

Your question has been moved to correct section of the forum.

Hi bobhc,

Can you please share where this post has been moved?

Regards,
AM

 

[bobhc]

http://chandoo.org/forum/