Move to cell range when drop down selected.

[mando112]

Hi all,


Really stuck here and hoping the forum can help.


I am currently setting up a project plan style document, I have a data validation for the months and have managed to get the dates populated based on this selection. I am trying to get range J5:AN18 to display if "Jan" is selected in the validation drop down, and when "Feb" is selected Range AO5:BP18 and so on for the rest of the months, the idea is to hide all the other columns so they are only displayed when selected from the drop down list.


Thanks in advance for any help.

[Sam Capricci]

one way is to use if statements with vlookups, for instance if(wherever Jan is,vlookup(wherever Jan is,J5:AN18, then if(wherever Feb is,vlookup(wherever Feb is,A05:BP18 etc.
Not sure what you've tried or if you have sample data that might make it easier to develop a set of formulas for you.

[mando112]

Thanks Sambo Kid


I have attached a sample file if it makes it easier.

[Sam Capricci]

It would help to know what results you want where. In your first post you mention you want all other columns hidden when selecting Jan or Feb etc. Which ranges would be hidden?
You didn't include any results you want and in which cells. I'm assuming your dropdown you refer to is in cell J2 of the project planner tab, is that correct? Where does the data come from that would populate the results? What are the results you are looking to have populated, something for each column from J through AN if Jan is selected? What gets hidden, if you selected Feb for example would columns J through AN be hidden along with BQ through NJ? And if you select Feb and col J is hidden you cannot select another month without un-hiding that column?
An explanation here and a workbook with results and where they come from would be a better help.

[mando112]

Sorry guys, I have been very vague I will try to explain in a bit more detail.

so the dates run from J4:NJ4 this is from the 1st Jan to 31st December.

The idea is that when the month is selected from the drop down in J2 all the dates corresponding to the month selected will be displayed, this would include all the dates for example 1st Jan to 31st Jan as well as the activity entries below the dates. So the range that would show is J4:AN41. If Feb is selected in J2 the range shown in the same place would be AO8:BP41.

I am trying to store data in the cells under the monthly dates and if the month is changed from Jan to Feb I would need the previous data to stay in the cell and not be overwritten.

I hope that explain its a bit better and thanks for your help so far.

[Sam Capricci]

well, not trying to be nasty but your layout is not very helpful. It is impossible to see all the range of data from Jan through Dec and understand what you want populated in what locations.
Can you reload the workbook with some actual data, show what will be selected and show some expected results AND where they would come from?