Formula copy for December
 

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.

Formula copy for December
 

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" wrote:

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.

Formula copy for December
 

Cool! I have not try yet, but it makes a lot of sense.
Thanks Jean-Guy.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Jean-Guy" wrote:

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" wrote:

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.

Formula copy for December
 

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.

--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in message
...
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.

Formula copy for December
 

Got to read it today but is very helpfull.
Thanks T. Valko, appreciate it.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in message
...
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.

Formula copy for December
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in message
...
Got to read it today but is very helpfull.
Thanks T. Valko, appreciate it.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in
message
...
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.