Formula copy for December

[Learning Excel]

I got 12 worksheets updated daily (writting names of U.S. States), named as
the months.
I got another worksheet that feeds from those 12 worksheets(JAN,FEB,MARCH...)
Something like this ( I just used Nov. for obvious space reasons)
A B C D
jan feb nov dec

1 NY 501

2 GA 333

3 FL 612


This is my formula: COUNTIF(NOVEMBER!F2:F300"NY") in C1
COUNTIF(NOVEMBER!F2:F300"GA") in C2
-- COUNTIF(NOVEMBER!F2:F300"FL") in C3
Q: Can I copy from C to D and change NOVEMBER to DECEMBER each time without
doing it manually ( meaning writing DECEMBER one by one in D )?
Thanks for the answer or at least for the feedback.

Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

 

[Jean-Guy]

Hi,

One way is to first create a table, fist coumn type the numbers from 1 to
12, second column type the month names, now you can use a combination of
INDIRECT and VLOOKUP.

=COUNTIF(INDIRECT(VLOOKUP(COLUMN(A1),table,2,0)&"!F2:F300"),"NY")

when copied across you'll get the result from sheet Jan, Feb, Mar..etc..if
you want to start from November then change A1 to K1 or K:K, if you had a
table with all the states name then you could do the same for that too but
instead of the COLUMN function you'd need to use the ROW function ROW(A1) or
ROW(1:1)

HTH
Jean-Guy

 

[Learning Excel]

Cool! I have not try yet, but it makes a lot of sense.
Thanks Jean-Guy.

 

[T. Valko]

As long as the coulmn headers and the tab names are in the same format. That
is, both are short month names: Jan, Feb, Mar, or, both are long month
names: January, February, March.

=COUNTIF(INDIRECT(C$1&"!F2:F300"),$A2)

Where C1 = column header Nov and A2 = NY

Copy across then down as needed.

 

[Learning Excel]

Got to read it today but is very helpfull.
Thanks T. Valko, appreciate it.

 

[T. Valko]

You're welcome. Thanks for the feedback!