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

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.

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.

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.

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.