I'm trying to take cells for a given date (e.g. "Jun-14", "Jul-14" through "Jun-16") and create a formula that assigns a title above each column that aligns with the end of a fiscal quarter (Sep-14 brings up FY14Q1, Dec-14 brings FY14Q2... Sep-15 brings FY15Q1) but that doesn't create a title if the month doesn't align with the end of a fiscal quarter. Is there any "If" function that could produce this?
Thanks so much!
Hi
with the month ending dates starting in A2, in A1 copied across
Formula:
Please Login or Register to view this content.
Add this table somewhere and name it Qtrs
1 Q3 -1
2 Q3 -1
3 Q3 -1
4 Q4 -1
5 Q4 -1
6 Q4 -1
7 Q1 -1
8 Q1 -1
9 Q1 0
10 Q2 0
11 Q2 0
12 Q2 0
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
This in A1 and dragged across will also do it, and you don't need a table elsewhere with the Qtr names:
Formula:Please Login or Register to view this content.
Ah, amazing. Thank you! So, I'm compiling a table of data under these month-labeled columns, but want to be able to take the table and export a table that only includes the columns that correspond with an end-of-fiscal-quarter date (export the column under "sep-14", "dec-14","mar-15",etc. but not the other months, and have the columns now placed under the respective fiscal quarters "FY14Q1","FY14Q2", "FY14Q3",etc.). Is there any way of directly producing a new table with this format? Sorry for all of the questions - I'm a newbie.Originally Posted by gak67
This in A1 and dragged across will also do it, and you don't need a table elsewhere with the Qtr names:
Formula:Please Login or Register to view this content.
It would be easier to answer if you could upload a sample sheet (click on Go Advanced in the bottom right and then click on the paperclip), but it seems like you should copy the sheet you have and then delete the columns you don't need. Or alternatively, select the columns you need from your current sheet and copy them to a new sheet.
I'm away from my files for a while, but the goal is to produce a working model with which monthly data can be inserted into a file and then a quarterly report (which just takes the columns under the months that correspond to ends of fiscal quarters) can be quickly and routinely exported to make a summarized table that only includes those columns under the "FY14Q1" formatted headings. Is there a way to make this process easily repeated? Sorry if I'm doing a poor job of explaining!
There are ways of doing it, but would need the sample file to give a definitive answer. You could either write some VBA code (or since you're a newbie, get someone here to write some) to copy only the columns you want, or alternatively you could use either INDEX/MATCH or OFFSET/MATCh function combos.
Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.
Thanks again! I attached a really simple recreation of what it looks like and what I'm trying to do.
Last edited by kirby5050; 06-04-2014 at 12:14 PM.
If you can remove the parentheses from the quarter names in the top table this will work (in C11 and copied to other cells):Formula:Please Login or Register to view this content.
If you can't remove the parentheses this will work:Formula:Please Login or Register to view this content.
Adjust the ranges to suit your real data.
Amazing - thank you so, so much again. Is there any better way to express gratitude other than "adding reputation"?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks