Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date range help
I have a workbook with financial information for our fiscal year. Each tab =
each dept with their monthly financial information. I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value). This is not a problem but the problem is when I pulled the fiscal year to date summary. I have the data keyed in from April to June, but I only want to see the FYTD from April to May. How do I set up the formula for this so that it only runs the FYTD summary based on the range it's given? Thanks, ml |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date range help
ml wrote:
I have a workbook with financial information for our fiscal year. Each tab = each dept with their monthly financial information. I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value). This is not a problem but the problem is when I pulled the fiscal year to date summary. I have the data keyed in from April to June, but I only want to see the FYTD from April to May. How do I set up the formula for this so that it only runs the FYTD summary based on the range it's given? Thanks, ml Hi, Please show us a small sampling of your layout and your current attempt at FYTD. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date range help
The sample below is the snapshot of each dept financial information. Each
tab = each dept. (A) (B) (C) (D) (E) 1 Expense FYTD April May June 2 Budget =sum(c2:e2) 189,723 193,123 182,908 3 Actual =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value) - here's the setup Month: May (hlookup value) (A) (B) (C) (D) (E) 1 Expense Dir Total Dept 1 Dept 2 Dept 3 2 Budget - May =sum(c2:e2) 189,723 193,123 182,908 3 Actual - May =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 5 Budget FYTD =sum(c5:e5) ? ? ? 6 Actual FYTD =sum(c6:e6) ? ? ? The report is generating monthly, but from time to time we'll have director who will come back asking for their previous month(s) report because they might have lost it (not unusual). Here is what I want to accomplish... I want to set up the formula for this so that it only runs the FYTD summary based on the range it's given? In other word, if they want to see April-May FYTD figure, I'll be able to pull that only. Right now, my summary sheet pulled April to June data. I have tried sumif and sumproducts and it didn't work. Whew... ml "smartin" wrote: ml wrote: I have a workbook with financial information for our fiscal year. Each tab = each dept with their monthly financial information. I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value). This is not a problem but the problem is when I pulled the fiscal year to date summary. I have the data keyed in from April to June, but I only want to see the FYTD from April to May. How do I set up the formula for this so that it only runs the FYTD summary based on the range it's given? Thanks, ml Hi, Please show us a small sampling of your layout and your current attempt at FYTD. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date range help
I would add three new rows to each department's worksheet:
(A) (B) (C) (D) (E) 1 Expense FYTD April May June 2 Budget =sum(c2:e2) 189,723 193,123 182,908 3 Actual =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 5 Budget FYTD =sum($c2:c2) 6 Actual FYTD 7 Variance FYTD The formula in C5 should be filled right and down and will provide running FYTD values for each metric. You can fetch the FYTD values in the same manner as the monthly values using HLOOKUP. Hope this helps! ml wrote: The sample below is the snapshot of each dept financial information. Each tab = each dept. (A) (B) (C) (D) (E) 1 Expense FYTD April May June 2 Budget =sum(c2:e2) 189,723 193,123 182,908 3 Actual =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value) - here's the setup Month: May (hlookup value) (A) (B) (C) (D) (E) 1 Expense Dir Total Dept 1 Dept 2 Dept 3 2 Budget - May =sum(c2:e2) 189,723 193,123 182,908 3 Actual - May =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 5 Budget FYTD =sum(c5:e5) ? ? ? 6 Actual FYTD =sum(c6:e6) ? ? ? The report is generating monthly, but from time to time we'll have director who will come back asking for their previous month(s) report because they might have lost it (not unusual). Here is what I want to accomplish... I want to set up the formula for this so that it only runs the FYTD summary based on the range it's given? In other word, if they want to see April-May FYTD figure, I'll be able to pull that only. Right now, my summary sheet pulled April to June data. I have tried sumif and sumproducts and it didn't work. Whew... ml "smartin" wrote: ml wrote: I have a workbook with financial information for our fiscal year. Each tab = each dept with their monthly financial information. I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value). This is not a problem but the problem is when I pulled the fiscal year to date summary. I have the data keyed in from April to June, but I only want to see the FYTD from April to May. How do I set up the formula for this so that it only runs the FYTD summary based on the range it's given? Thanks, ml Hi, Please show us a small sampling of your layout and your current attempt at FYTD. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date range help
Thanks for your suggestions. The summary sheet is provided to their
directors only. Each Dept Mgr will get their individual monthly report (no FYTD reported). Is there another way to handle this? thanks, "smartin" wrote: I would add three new rows to each department's worksheet: (A) (B) (C) (D) (E) 1 Expense FYTD April May June 2 Budget =sum(c2:e2) 189,723 193,123 182,908 3 Actual =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 5 Budget FYTD =sum($c2:c2) 6 Actual FYTD 7 Variance FYTD The formula in C5 should be filled right and down and will provide running FYTD values for each metric. You can fetch the FYTD values in the same manner as the monthly values using HLOOKUP. Hope this helps! ml wrote: The sample below is the snapshot of each dept financial information. Each tab = each dept. (A) (B) (C) (D) (E) 1 Expense FYTD April May June 2 Budget =sum(c2:e2) 189,723 193,123 182,908 3 Actual =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value) - here's the setup Month: May (hlookup value) (A) (B) (C) (D) (E) 1 Expense Dir Total Dept 1 Dept 2 Dept 3 2 Budget - May =sum(c2:e2) 189,723 193,123 182,908 3 Actual - May =sum(c3:e3) 192,153 194,295 194,102 4 Variance =b2-b3 2,430 1,172 11,194 5 Budget FYTD =sum(c5:e5) ? ? ? 6 Actual FYTD =sum(c6:e6) ? ? ? The report is generating monthly, but from time to time we'll have director who will come back asking for their previous month(s) report because they might have lost it (not unusual). Here is what I want to accomplish... I want to set up the formula for this so that it only runs the FYTD summary based on the range it's given? In other word, if they want to see April-May FYTD figure, I'll be able to pull that only. Right now, my summary sheet pulled April to June data. I have tried sumif and sumproducts and it didn't work. Whew... ml "smartin" wrote: ml wrote: I have a workbook with financial information for our fiscal year. Each tab = each dept with their monthly financial information. I also have tab called Summary sheet that pulls the all depts info to that Summary sheet with the monthly data (based on my hlookup value). This is not a problem but the problem is when I pulled the fiscal year to date summary. I have the data keyed in from April to June, but I only want to see the FYTD from April to May. How do I set up the formula for this so that it only runs the FYTD summary based on the range it's given? Thanks, ml Hi, Please show us a small sampling of your layout and your current attempt at FYTD. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
Sum If range of dates date range, sum totals | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |