Forum Discussion
Excel - Entering consecutive months in different sheets
I'm working with a worksheet that would have 12 different sheets, one for each month. I'd like to type January on the first worksheet, and for the other 11 months to fill automatically in the other sheets. I've looked through help and online, and can't find the answer. I took a class where the instructor moved really quick when doing it, so I did not get it. I know it can be done, I don't know how. Please help.
Thank you,
16 Replies
If I understand this you want 12 tabs, one for each month. Is this something you do frequently? Because if it's a one time setup then seems pretty easy to just create the separate tabs manually. If for some reason you do this all the time then easiest way is to just record a macro that does what you want done.
Yes, for now it is a one time only thing. I have created the twelve sheets and named them. In each sheet there is a field to put the name of the months, and I wanted it be updated automatically. I may not be even possible. I just saw the instructor doing it, and copied the function he used (=JANUARY!F1), but cannot make it work 😞
Thank you for responding,
Giuliana
FWIW, =JANUARY!F1 is not be a Function; rather it's simply a formula pointing to the F1 cell of a sheet named "January" and retrieving whatever value is in that cell, and placing it into the cell containing that formula. See the attached. You can change what's in cell F1 of the "JANUARY" sheet, and that value will appear in the yellow backgrounded cell of the next sheet.
What were you expecting it to do for you?
May I ask you a question? I'd like to know why you have a separate sheet for each month...often that's done because we approach the task as if Excel really was no more than a little more automated green ledger sheet--i.e., we use it to add and subtract to make keeping (for example) track of expenses on a monthly basis a bit easier.
In fact, if that's the kind of thinking behind your use, you could benefit from separating the input of expenses (by date, payee, budget category, amount) from a nice monthly report (the output).
That's just an example....and it's why I'm asking. There may be an entirely legitimate reason, but often it's a failure to understand how Excel can work to produce a nice report from a single input sheet covering all months (even multiple years).
Thank you for your input. First, I have to say that my excel knowledge is intermediate and I'm trying to become more proficient by practicing what I've learned in a class I attended. I do understand your point and have tried that approach (all the information in one sheet) before, but it is very confusing for me. I prefer to have the information separate for an easy access to each month and then prepare consolidated reports at the end of the year. The input is separated as you suggested on each sheet and while creating the worksheet I was able to use many of the tips and tricks I learned.
I am asking this question because I saw the instructor doing it, but failed to really understand how he did it :(. I'm now obsessed about it. 🙂
Again, thank you,
I'm not going to let you off that easily, so please forgive me for pushing back a bit here. The only reason you gave for continuing the course was that you find it confusing when everything is on one sheet. But I wasn't suggesting that everything be on one sheet; only the input. That is, only the "raw data."
Absolutely agree that it's helpful to have access to nicely arrayed monthly data--sometimes even having separate sheets for each month, sometimes having one sheet that dynamically displays the data for one month at a time (and sometimes that same sheet displaying the consolidated data for the year)...
So, may I ask, unless there's confidential information in your workbook, is it possible for you to upload it here to the techcommunity forum so that I or somebody else might be able to recommend an altogether different way to approach the task? If it does contain confidential info, perhaps a copy that's been stripped of confidential or personal info.
I don't know how to auto fill that way but what I did in a similar application was to put a number in the a1 cell in each sheet going from 1 - 12 and then I was able to use formulas throughout the rest of the sheet accordingly (it is a workbook with timesheets for the whole year so it autogenerated the calendar for each month accordingly). I made my number 'invisible' by doing white text on white background but you could also hide the col / row or pick a different cell. (for example another application has the company logo located on each sheet and I just selected a cell behind the logo)
Thank you. Did you numbered the a1 cell in each sheet automatically? What I had written down is the following =JANUARY!F1 (which I'm assuming is the cell where the initial value is), but I may not be applying it correctly.
Giuliana1960 no, as I mentioned I did not autofill or autocalculate. I just manually set the A1 cell in each sheet. Then in B1 I used a formula like =TEXT(DATE(2020,A1,1),"MMMM") to show the Month in text format. (I actually have a cell where I define the year for the whole workbook but that is something else)
