Sum Across Sheet Indirect Function

[asalman07]

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.

 

[asalman07]

D3 involves just IntExp_Q1. What do we fetch (or sum) from IntExp_Q1?

Aladin,

that's correct, there isn't anything to sum it's just a lookup. I just want to grab the data from there.

To add a bit more information which may or may not be relevant. My target sheets have about 170 row headers on column C and row headers on the target sheets are identically setup but my full year sheet will have less row headers and obviously as stated before not in the same order as my target sheets. Thanks!

 

[Aladin Akyurek]

Aladin,

that's correct, there isn't anything to sum it's just a lookup. I just want to grab the data from there.

To add a bit more information which may or may not be relevant. My target sheets have about 170 row headers on column C and row headers on the target sheets are identically setup but my full year sheet will have less row headers and obviously as stated before not in the same order as my target sheets. Thanks!

In which column do we find Actual and Jun FY15 in IntExp_Q1?

 

[asalman07]

In which column do we find Actual and Jun FY15 in IntExp_Q1?

Column D.

 

[Aladin Akyurek]

Column D.

Posted in D3, does the following formula...

=INDEX(IntExp_Q1!D:D,MATCH(C3,IntExp_Q1!C:C,0))

get what you are after? What this formula does is to match C3 against the C column of IntExp_Q1 and fetch the corresponding value from the D column of IntExp_Q1.

 

[asalman07]

Posted in D3, does the following formula...

=INDEX(IntExp_Q1!D:D,MATCH(C3,IntExp_Q1!C:C,0))

get what you are after? What this formula does is to match C3 against the C column of IntExp_Q1 and fetch the corresponding value from the D column of IntExp_Q1.

Aladin sorry for the delayed reply. Yes I have used this before and I believe it will work but my issue is when I copy it over to column G I need the formula to go to the worst worksheet and fetch the sep fy 15 column.
thanks hope I am more clear now

 

[Aladin Akyurek]

Aladin sorry for the delayed reply. Yes I have used this before and I believe it will work but my issue is when I copy it over to column G I need the formula to go to the worst worksheet and fetch the sep fy 15 column.
thanks hope I am more clear now

Insert an additional row in front of the current first row. Enter for the each header field the sheet name that is relevant. D1 should now house IntExp_Q1, for example.

The formula for D4 (formerly D3), which you can copy down and across:

=VLOOKUP($C4,INDIRECT("'"&D$1&"'!C:D"),2,0)

You can copy this, but it probably needs to be adjusted regarding the INDIRECT bit.

 

[asalman07]

Insert an additional row in front of the current first row. Enter for the each header field the sheet name that is relevant. D1 should now house IntExp_Q1, for example.

The formula for D4 (formerly D3), which you can copy down and across:

=VLOOKUP($C4,INDIRECT("'"&D$1&"'!C:D"),2,0)

You can copy this, but it probably needs to be adjusted regarding the INDIRECT bit.

Thanks I think we are on the right track. I noticed when I copy that formula across one column it is still picking up Jun FY 15 number. It did not pick up Jul FY 15 which is what I would like. You did mention I may need to adjust the indirect bit but can you help on this?

 

[asalman07]

Aladin I think I am getting it to work by adjusting the indirect part. Thanks again you are unbelievable!!

 

[Aladin Akyurek]

Thanks I think we are on the right track. I noticed when I copy that formula across one column it is still picking up Jun FY 15 number. It did not pick up Jul FY 15 which is what I would like. You did mention I may need to adjust the indirect bit but can you help on this?

The adjustment must involve the C:D bit and the column offset.

Not sure but if those 3 sheets are structured the same way, maybe:

=INDEX(INDIRECT("'"&D$1"'!"&CELL("address",D:D)),MATCH($C4,INDIRECT("'"&D$1&"'!C:C",0))

On the other hand, having an appropriate formula for each cell and copy that down would be adequate and faster than an INDIRECT solution.