| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
| copy formula down a column and have cell references change within formula | New Users to Excel | |||
| Copy Formula Down Without Changing Entire Formula | Excel Discussion (Misc queries) | |||
| Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
| Copy formula so destination displays formula as text | Excel Discussion (Misc queries) | |||
Linear Mode
