Question description:
This user has given permission to use the problem statement for this
blog.
I have 12 months columns from C to with many lines from C to N
I need in column A to have the possibility to select any month with all the lines
Solved by I. B. in 41 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
26/04/2018 - 01:56
hello there! can you share with me the file you're working with? You can upload it by clicking the "clip" icon next to this chatboxThanks! :)
User
26/04/2018 - 01:57
Can I show you an example?
Excelchat Expert
26/04/2018 - 01:57
oh sure! that would be better then. :_)
User
26/04/2018 - 01:57
Or write down in the excel sheet an easy example?
Excelchat Expert
26/04/2018 - 01:57
Yep, we can work with that. :)
User
26/04/2018 - 01:58
So I need in the column A
User
26/04/2018 - 01:59
to have a dropdown list in A1
User
26/04/2018 - 01:59
With the month
User
26/04/2018 - 01:59
And when I select the month all the information below is display in A2,A3,A4...
Excelchat Expert
26/04/2018 - 01:59
I see!
Excelchat Expert
26/04/2018 - 02:00
Just to clarify, your table only consists of a single instance of the 12 months right? Like there's no repeated January or December.
User
26/04/2018 - 02:00
Yes
User
26/04/2018 - 02:00
Only 12 months
Excelchat Expert
26/04/2018 - 02:01
Okay, let me work on it then. :)
User
26/04/2018 - 02:01
Perfect thank you
User
26/04/2018 - 02:02
The names aren't important
Excelchat Expert
26/04/2018 - 02:03
Yep. But I'm assuming there will be a lot of names?
User
26/04/2018 - 02:03
Yes exactly
Excelchat Expert
26/04/2018 - 02:03
okay. :)
Excelchat Expert
26/04/2018 - 02:07
Done!
User
26/04/2018 - 02:07
So I just need to copy the formula you used ?
User
26/04/2018 - 02:08
Wow, thank you
Excelchat Expert
26/04/2018 - 02:08
Yes. Just copy the formula in A2, then drag it down. :)
Excelchat Expert
26/04/2018 - 02:09
This is assuming that the month selection is in A1. :)
Excelchat Expert
26/04/2018 - 02:09
And that the months are from C1:N1.
User
26/04/2018 - 02:09
yes sure
User
26/04/2018 - 02:09
I only need to do a regular data validation list in A1
Excelchat Expert
26/04/2018 - 02:09
You can try it first in your excel file to test before we end the session. :)
Excelchat Expert
26/04/2018 - 02:09
Yes :D
User
26/04/2018 - 02:09
and the formula from A2 to the bottom
Excelchat Expert
26/04/2018 - 02:10
Yes. The data validation should match the C1:N1 month names. :)
User
26/04/2018 - 02:10
Ok thank you!
User
26/04/2018 - 02:10
I'm going to try this right now
Excelchat Expert
26/04/2018 - 02:10
Sure please do! And tell me if it works. :)
Excelchat Expert
26/04/2018 - 02:14
how was the testing?
User
26/04/2018 - 02:15
It's hard because the cells are not the same
User
26/04/2018 - 02:15
so i'm trying to change the formula but its not working
Excelchat Expert
26/04/2018 - 02:15
what do you mean? You could tell me the cells on your part and I could modify the formula for you. :)
User
26/04/2018 - 02:15
Ok thanks
User
26/04/2018 - 02:16
The months are in cells G8 to R8
User
26/04/2018 - 02:16
The Data validation cell is in E8
User
26/04/2018 - 02:16
The ''name'' are from C10 to C60
User
26/04/2018 - 02:17
And the data should be display in E10 to E60
Excelchat Expert
26/04/2018 - 02:19
=if(c10="","",if($e$8="","Please select month",hlookup($e$8,$G$8:$R$108,row(2:2),0)))
Excelchat Expert
26/04/2018 - 02:19
Copy that in cell E10 and drag it all the way down to E60. :)
User
26/04/2018 - 02:20
It says the logical test is invalid
Excelchat Expert
26/04/2018 - 02:23
Can you see if the excel preview data matches the cell locations of your excel file?
User
26/04/2018 - 02:23
yes give me 10 sec
User
26/04/2018 - 02:24
Yes !
Excelchat Expert
26/04/2018 - 02:24
Nice! I've modified the formula in E10. You can copy it and paste in your excel file to see if it works. :)
Excelchat Expert
26/04/2018 - 02:26
Did it work? :)
User
26/04/2018 - 02:27
No :/
Excelchat Expert
26/04/2018 - 02:28
Aw. How come. Hmm. I believe it will be best if you could upload the file, and for me to check it. Maybe the problem lies elsewhere, not in the formula.
User
26/04/2018 - 02:28
It says the error message: We found a problem with this formula. Try clicking Insert Function.....
Excelchat Expert
26/04/2018 - 02:29
I tried it also in my local excel file, and it works. hmm
User
26/04/2018 - 02:29
I'm going to try to work with this formula
User
26/04/2018 - 02:29
Thank you
Excelchat Expert
26/04/2018 - 02:29
You may actually get a copy of our excel preview by clicking File > Download as > Microsoft Excel (.xlsx)
User
26/04/2018 - 02:30
ok thank you!
Excelchat Expert
26/04/2018 - 02:30
And I also urge that we solve this during the session. :) Just to help you save some time. :)
Excelchat Expert
26/04/2018 - 02:31
Just to clarify, when you made the Data validation, did you select the actual G8:R8 as your criteria? That would probably help.
User
26/04/2018 - 02:31
Yes
Excelchat Expert
26/04/2018 - 02:32
I see. When you copied the formula, did you copy the cell or the formula text? And when you pasted it, did you double-clicked the cell first, before pressing ctrl+V?
User
26/04/2018 - 02:32
the formula text
Excelchat Expert
26/04/2018 - 02:33
Nice. I hope you made sure to copy every character in there. Did you try pasting it by first clicking the cell, then clicking the formula bar, and pasting it there?
User
26/04/2018 - 02:34
Oh i know whats the problem
User
26/04/2018 - 02:34
I change my , symbol
User
26/04/2018 - 02:34
to !
Excelchat Expert
26/04/2018 - 02:34
Oh, what's the problem? :)
User
26/04/2018 - 02:34
|
Excelchat Expert
26/04/2018 - 02:34
Oh lol. :D
Excelchat Expert
26/04/2018 - 02:35
So did it work right now? :)
User
26/04/2018 - 02:36
YES finally !
User
26/04/2018 - 02:36
Haha, thank you so much
User
26/04/2018 - 02:36
It was my fault
Excelchat Expert
26/04/2018 - 02:36
Nice! :) Sure thing. :) Lol, we all make mistakes. :)
Excelchat Expert
26/04/2018 - 02:36
If everything is good now, you can end the session now. Please do make a good feedback! Thanks again and have a nice day. :)
User
26/04/2018 - 02:37
Thank you have a nice day!
Excelchat Expert
26/04/2018 - 02:37
thanks!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.