0

I am using Excel 2010. Let's say I have a workbook that contains 13 worksheets. The first worksheet is named "Summary" and the remaining 12 are the months of the year (January-December). In "Summary" I want to ask the user to choose a month. Then, I use '=DATE(YEAR(E1),MONTH(E1)+1,1)' to increment to the following month. That's done a total of 2 times so user enters 06/01/2016 and they get June, July, and August. Now, I want to take figures from each of the 3 named months and sum them on the "Summary" worksheet. The problem I am running into has to do with using the INDIRECT() formula and the fact that the Months cells aren't formatted as Text but as Date (actually Custom Dates).

How would I go about referencing the June, July, and August worksheets in a formula if the data in the cells isn't Text? Something like =B1!H3 if I'm trying to access =July!H3. Hopefully this makes sense. If not, let me know and I can try to clear it up. Thanks!

  • I'm not understanding exactly what the problem is. Can you add some detail to your question? What are the relevant cell contents? What result do you get from your formula? What is the INDIRECT formula you're trying to use? – fixer1234 Feb 12 '16 at 0:22
1

Here's one solution:

Let's say User enters their date in E1.

  • F1 contains =DATE(YEAR(E1),MONTH(E1)+1,1)
  • G1 contains =DATE(YEAR(F1),MONTH(F1)+1,1)
  • H1 contains =DATE(YEAR(G1),MONTH(G1)+1,1)

(I shortened the month names for this next part)

You can use the CHOOSE function to provide a list of choices - one of which will be selected based on the number specified by the first argument. As an example, let's say you want to get the value of A1 from one of the named worksheets:

  • F2 contains =CHOOSE(MONTH(F1), Jan!$A$1, Feb!$A$1, Mar!$A$1, Apr!$A$1, May!$A$1, Jun!$A$1, Jul!$A$1, Aug!$A$1, Sep!$A$1, Oct!$A$1, Nov!$A$1, Dec!$A$1)
  • G2 contains =CHOOSE(MONTH(G1), Jan!$A$1, Feb!$A$1, Mar!$A$1, Apr!$A$1, May!$A$1, Jun!$A$1, Jul!$A$1, Aug!$A$1, Sep!$A$1, Oct!$A$1, Nov!$A$1, Dec!$A$1)
  • H2 contains =CHOOSE(MONTH(H1), Jan!$A$1, Feb!$A$1, Mar!$A$1, Apr!$A$1, May!$A$1, Jun!$A$1, Jul!$A$1, Aug!$A$1, Sep!$A$1, Oct!$A$1, Nov!$A$1, Dec!$A$1)

and we'll say if you want the sum in I2 simply do

=SUM(F2:H2)

or you can write one gigantic function if you do not want to show the number for each month:

=SUM(CHOOSE(MONTH(F1), Jan!$A$1, Feb!$A$1, Mar!$A$1, Apr!$A$1, May!$A$1, Jun!$A$1, Jul!$A$1, Aug!$A$1, Sep!$A$1, Oct!$A$1, Nov!$A$1, Dec!$A$1), CHOOSE(MONTH(G1), Jan!$A$1, Feb!$A$1, Mar!$A$1, Apr!$A$1, May!$A$1, Jun!$A$1, Jul!$A$1, Aug!$A$1, Sep!$A$1, Oct!$A$1, Nov!$A$1, Dec!$A$1), CHOOSE(MONTH(H1), Jan!$A$1, Feb!$A$1, Mar!$A$1, Apr!$A$1, May!$A$1, Jun!$A$1, Jul!$A$1, Aug!$A$1, Sep!$A$1, Oct!$A$1, Nov!$A$1, Dec!$A$1))

(absolute references optional)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.