2

I have 12 tabs (sheets) in an Excel workbook ( they are 'Jan' 'Feb' 'Mar' and so on). On each of the tabs I have same structure/table. Say in each tab I have the earning value on the cell B2 and expense value on cell C2.

Now on a new tab, 'Cover', I want to create a table which will consolidate all the earning and expense values against each month.

Now, on the 'Cover' tab I have to tediously put =Jan!B2, =Jan!C2, =Feb!B2 and so on ...

Is there any way which can save my time by just some dragging ?

Improve this question

2 Answers 2

Reset to default
1

You have create one formula for each sheet (12 formulas). Then you can use copy&paste (or draging) to access the other cell the paricular formula applies to. If you need those cell on a different position on your evaluation sheet user Cut&Paste to move them around without changing it's reference cell on the monthly sheet.

Improve this answer
0
1

You can use INDIRECT function in order to get a reference of a cell starting from a string representation.

Let say that you have a sheet called "Jan" with value 10 in cell A2 and a sheet called "Cover" with value "Jan" in cell A1, then you can write in sheet "Cover" cell A2 the function =INDIRECT(A1 & "!A2") to get the value of cell A2 in sheet "Jan".

If you populate your "Cover" sheet with a row or a column (based on your needs) with the texts "Jan", "Feb" etc., then you can use the INDIRECT function in order to get values from your sheets and then drag as your needs.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .