Hello,
I asked a simlar question on May 27th ('Extracting data from multiple worksheets') and was able to use the suggested formula successfully.
Since then, I have expanded my project from consolidated income statements (IS, ie. the highest level available) to lower level deptartment IS.
The attached sample file is comparable to the real data that I am using. Each month is on its own worksheet, department names are listed and the IS format does not change from month to month. However, as on the '2014 03' worksheet a new department (Dept3) was added above Dept 2; thus the relative position of Dept2 was altered in the worksheet. The Summary worksheet that I created contains two 'helper' rows of numbers which are used in the embedded Match formula.
Cell B8 of the Summary worksheet contains the formula that I have been trying to modify. I have been trying to embed a some type of referrence formula into the Match formula so that the reference will change from worksheet to worksheet (ie cell B9 should contain "....MATCH($A$5,'2014 02'!$A$1:$A$4000,0)....."). I think I could use the combination of INDEX and ADDESS again though I do not know how to include a range of cells ($A$1:$A$4000) as the Column and Row references.
Cell C8 contain an attempt using the Concatenate formula (this returns a #VALUE error, though the Concatenate formula itself seems fine).
I hope that this is clear enough.
Thanks in advance for any help.
I asked a simlar question on May 27th ('Extracting data from multiple worksheets') and was able to use the suggested formula successfully.
Since then, I have expanded my project from consolidated income statements (IS, ie. the highest level available) to lower level deptartment IS.
The attached sample file is comparable to the real data that I am using. Each month is on its own worksheet, department names are listed and the IS format does not change from month to month. However, as on the '2014 03' worksheet a new department (Dept3) was added above Dept 2; thus the relative position of Dept2 was altered in the worksheet. The Summary worksheet that I created contains two 'helper' rows of numbers which are used in the embedded Match formula.
Cell B8 of the Summary worksheet contains the formula that I have been trying to modify. I have been trying to embed a some type of referrence formula into the Match formula so that the reference will change from worksheet to worksheet (ie cell B9 should contain "....MATCH($A$5,'2014 02'!$A$1:$A$4000,0)....."). I think I could use the combination of INDEX and ADDESS again though I do not know how to include a range of cells ($A$1:$A$4000) as the Column and Row references.
Cell C8 contain an attempt using the Concatenate formula (this returns a #VALUE error, though the Concatenate formula itself seems fine).
I hope that this is clear enough.
Thanks in advance for any help.