Formula Help for Google Sheets.

Beth12345

New Member
Joined
Sep 26, 2022
Messages
2
Platform
  1. Windows
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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


Cell Formulas
RangeFormula
B4B4=A1
C4:M4C4=DATE(YEAR(B4),MONTH(B4)+1,DAY(B4))


merged cells and TEXT

Cell Formulas
RangeFormula
B4B4=TEXT(A1,"MMMM")&" "&TEXT(DATE(YEAR(A1),MONTH(A1)+1,1),"MMMM")
D4D4=TEXT(DATE(YEAR(A1),MONTH(A1)+3,1),"MMMM") &" "& TEXT(DATE(YEAR(A1),MONTH(A1)+4,1),"MMMM")
F4F4=TEXT(DATE(YEAR(A1),MONTH(A1)+5,1),"MMMM") &" "& TEXT(DATE(YEAR(A1),MONTH(A1)+6,1),"MMMM")
H4H4=TEXT(DATE(YEAR(A1),MONTH(A1)+7,1),"MMMM") &" "& TEXT(DATE(YEAR(A1),MONTH(A1)+8,1),"MMMM")
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 

Attachments

  • Formula help screenshot.png
    Formula help screenshot.png
    81.6 KB · Views: 7
Upvote 0

Forum statistics

Threads
1,223,323
Messages
6,171,467
Members
452,406
Latest member
kken tthompson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top