Formula Help for Google Sheets.

[Beth12345]

Im unsure if this is really simple and im not seeing the answer, however can someone please help

I am wanting to have a 12 month calendar/table, where the starting month is is selected from a drop down list . As in towards the top of the page I will have "Starting Month" with a drop down list (in B9) where the user selects the month they would like to start from. And then in a table below, the month names populate in row B13:M13.

I have the page set up currently so the month names would be in merged cells (ie first month name would be in B13 and C13, second month name would be D13 and E13). I am uncertian if the merged cells creates any issue.

Appreciate any guidance


Thanks Kindly

 

[SanjayGMusafir]

I have the page set up currently so the month names would be in merged cells (ie first month name would be in B13 and C13, second month name would be D13 and E13). I am uncertian if the merged cells creates any issue

Please share your current page set up using XL2BB to understand the whole scenario.

Also, may I suggest you to update your current Excel Version here as well as in your profile so that anyone visiting this thread to help you knows your Excel Version and would suggest solution accordingly.

 

[etaf]

B13 to M13 is 12 months - if you are merging cells - that will cause an issue - especially if you want to use those dates later in a calculation

Did you want to stop when at end of year - so if the dropdown is april - then cells K,L,M are left blank

i have used real dates , and just formatted to show months and so will work with calcs
otherwise can use text and concatenate together in merged cells if needed

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	4/1/22
2
3
4		April	May	June	July	August	September	October	November	December	January	February	March
Sheet3

Cell Formulas
Range	Formula
B4	B4	=A1
C4:M4	C4	=DATE(YEAR(B4),MONTH(B4)+1,DAY(B4))

merged cells and TEXT

Book1
	A	B	C	D	E	F	G	H	I
1	4/1/22
2
3
4		April May		July August		September October		November December
Sheet3

Cell Formulas
Range	Formula
B4	B4	=TEXT(A1,"MMMM")&" "&TEXT(DATE(YEAR(A1),MONTH(A1)+1,1),"MMMM")
D4	D4	=TEXT(DATE(YEAR(A1),MONTH(A1)+3,1),"MMMM") &" "& TEXT(DATE(YEAR(A1),MONTH(A1)+4,1),"MMMM")
F4	F4	=TEXT(DATE(YEAR(A1),MONTH(A1)+5,1),"MMMM") &" "& TEXT(DATE(YEAR(A1),MONTH(A1)+6,1),"MMMM")
H4	H4	=TEXT(DATE(YEAR(A1),MONTH(A1)+7,1),"MMMM") &" "& TEXT(DATE(YEAR(A1),MONTH(A1)+8,1),"MMMM")

 

[SanjayGMusafir]

if you are merging cells - that will cause an issue

Merging cell might give you a visual beauty but is not advisable particularly when populating formula, as it spoils the cell referencing.

 

[Beth12345]

Thanks all for responses so far.

I am using Google Sheets, so unsure if I can upload a mini sheet, however I will attach a screen shot.

The drop down list is in the formal "January, February, March" etc (as days are irrelevant, and this will be used across multiple years), the =TEXT(A1,"MMM .... formula utilises dates in numeric format and I wasnt sure how to get around that.

I referenced row B13:M13 for ease, my apologies. I have posted a screen shot, the drop down list is in B9. And i would like the months populated in row 13 (highlighted), there are two merged cells per month, so January would be in B/C13, February in D/E13, March in F/G13 etc. I can remove the merge if more effective, as Sanjay suggested it is for aesthetic purposes only.

I thought to have B13 copy the drop down selection (ie, just a =B9 formula) then hoped to auto fill the remaining months, however this doesn't work.

I would love any suggestions if anyone knows how this could work

 

[Fluff]

I am using Google Sheets

This is something you should have clearly initially (preferably within the title) also such questions should be posted in the General Discussion & Other Applications section of the board. I have done that for you this time.