# Referencing a worksheet in a formula in Excel 2010

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

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)