Sum Across Sheet Indirect Function

A

asalman07

Active Member
Joined
Jun 12, 2013
Messages
325
Ok, so i set myself up for a little bit of trouble. I have 3 Worksheets in the same workbook.

Worksheet 1 is called: IntExp_Q1
Worksheet 2 is called: IntExp_Q2_Q3_Q4_Best
Worksheet 3 is called: IntExp_Q2_Q3_Q4_Worst

I need to get a Full Year view on one worksheet.

On a 4th Worksheet called IntExp_Full_Year i need to set up a full year view by month.

I would like my new 4th Worksheet to Begin on Column D with Jun FY15 to Column O May FY15 for the Best Case Scenario and

Beginning Column Q with Jun FY15 to Column AB for the Worst Case Scenario.

Obviously for both Best CAse and Worst Case my Jun to Aug would come from the IntExp_Q1 tab.

Now my question: is there a way to just have one formula and copy across using maybe the indirect function?

Currently, my IntExp_Q1 sheet starts at column D with the month Jun FY15, column E is Jul FY15, and column F is Aug FY15. Both the IntExp_Q2_Q3_Q4_Best and Worst worksheets also start at column D with the month Sep FY15 and all the way across to column L to May FY15.

Note that Column C contains all my account descriptions like Revenue Expense items, etc.

My goal is to construct one formula that can bring me a whole year view on one sheet.

Hope that makes sense and thanks for you all your help.
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Anyone? Another caveat is that my column c accounts may not be all identically on same rows. Thanks
 
Upvote 0
Aladin Akyurek said:
Try to post the layout of IntExp_Full_Year by showing 5 rows from relevant columns, including the headers.
Click to expand...

Hi Aladin, thank you for your reply.

Actual</SPAN>Actual</SPAN>Guess</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>High Risk Scenario</SPAN>
Jun FY15</SPAN>Jul FY15</SPAN>Aug FY15</SPAN>Sep FY15</SPAN>Oct FY15</SPAN>Nov FY15</SPAN>Dec FY15</SPAN>Jan FY15</SPAN>Feb FY15</SPAN>Mar FY15</SPAN>Apr FY15</SPAN>May FY15</SPAN>
TOTAL OB VOLUME</SPAN>
TOTAL IB VOLUME</SPAN>
FIXED FTE</SPAN>
VARIABLE FTE</SPAN>
OT FTE</SPAN>
Total FTEs</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=12></COLGROUP>


This would be a sample layout... Thank you please let me know if i can explain in more detail.
 
Upvote 0
The headers Actual Actual Guess would match the headers from the IntExp_Q1 worksheet and the headers High Risk Scenario would match the headers from the IntExp_Q2_Q3_Q4_Worst Worksheet.
 
Upvote 0
asalman07 said:
The headers Actual Actual Guess would match the headers from the IntExp_Q1 worksheet and the headers High Risk Scenario would match the headers from the IntExp_Q2_Q3_Q4_Worst Worksheet.
Click to expand...

This layout shows A:M.

I guess TOTAL OB VOLUME (a row header) is in A3.

Now specify how the value for B3 should be computed?
 
Upvote 0
The layout is C:O, with column C housing row headers.

TOTAL OB VOLUME is on C6 on the Full_Year worksheet but could be in other row locations on column C in the abbreviated Q1, Best, and Worst worksheets.

in your reference of B3 which is actually D3, the formula should go to the IntExp_Q1 worksheet and find the Total OB Volume for the month of Jun FY15. However, once we get to G3 which is the High Risk Scenario column header, the formula should go to the Worst WS and find the Total OB Volume for the month of Sep FY15. Thanks hope this helps






Aladin Akyurek said:
This layout shows A:M.

I guess TOTAL OB VOLUME (a row header) is in A3.

Now specify how the value for B3 should be computed?
Click to expand...
 
Last edited:
Upvote 0
asalman07 said:
The layout is C:O, with column C housing row headers.

TOTAL OB VOLUME is on C6 on the Full_Year worksheet but could be in other row locations on column C in the abbreviated Q1, Best, and Worst worksheets.

in your reference of B3 which is actually D3, the formula should go to the IntExp_Q1 worksheet and find the Total OB Volume for the month of Jun FY15. However, once we get to G3 which is the High Risk Scenario column header, the formula should go to the Worst WS and find the Total OB Volume for the month of Sep FY15. Thanks hope this helps
Click to expand...

Does the D3 calculation involve only 1 sheet or all of the 3 sheets? What must C3 match in the target sheet(s)?
 
Upvote 0
Aladin Akyurek said:
Does the D3 calculation involve only 1 sheet or all of the 3 sheets? What must C3 match in the target sheet(s)?
Click to expand...

The D3 calculation only involves one sheet which is the IntExp_Q1 sheet. Column c has row headers which are identical in target sheets just not in the same row #s. C3 would be empty as my row headers begin on C6 which is total outbound volume which must match total outbound volume on any of the target sheets which would be exactly located on C47 on all target sheets. I'm sorry if I am not clear. I am responding by cell phone
 
Upvote 0
asalman07 said:
The D3 calculation only involves one sheet which is the IntExp_Q1 sheet. Column c has row headers which are identical in target sheets just not in the same row #s. C3 would be empty as my row headers begin on C6 which is total outbound volume which must match total outbound volume on any of the target sheets which would be exactly located on C47 on all target sheets. I'm sorry if I am not clear. I am responding by cell phone
Click to expand...

D3 involves just IntExp_Q1. What do we fetch (or sum) from IntExp_Q1?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
171
B-radK
B
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
208
Insurance Dave
I
A
  • Question
Indirect Function with a separate Workbook
Replies
6
Views
972
AntMac
A
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
1
Views
196
bobaol
B
D
  • Question
How to use concat and indirect to update a sheet range in a sum across the sheets desiginated
Replies
6
Views
292
Fluff
Fluff

Forum statistics

Threads
1,214,666
Messages
6,120,806
Members
448,990
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top