how can i make a summary sheet on excel with only certain cells of all sheet?

Question

I've got a workbook with a number of sheets (which is constantly increasing) with details for apartment bookings. Now I need the first sheet to become a summary where a number of cells from every sheet is reported. I know how to bring all required cells to the summary for the first booking sheet but is there are formula which will increase the sheet number?

I explain:
in cell A2 of sheet 1 which I named 'summary' I have: =Sheet2!B1
in cell B2 of sheet 1 I have: =Sheet2!B3
in cell C2 of sheet 1 I have: =Sheet2!B7
etc
What I would like to do is enter a formula for the rest of the summary sheet where I will see the following result:
in cell A3 of sheet 1: =Sheet3!B1
in cell B3 of sheet 1: =Sheet3!B3
in cell C3 of sheet 1: =Sheet3!B7
etc.
The number of worksheets continues to increase so it should be unlimited if possible

Answer 1

Replace your Sheet1 Row 2 formulas with something like this



A2 =INDIRECT("Sheet" & Row(A2) & "!B1")

B2 =INDIRECT("Sheet" & Row(A2) & "!B3")

C2 =INDIRECT("Sheet" & Row(A2) & "!B7")



You can then select cells A2 to C2 and Drag\Copy them down and the formulas will reference the same cells on each sheet.



INDIRECT converts a text string into a cell reference.

The ROW(A2) function returns the row number of cell A2 which is 2. The A2 will change to A3, A4, A5 etc. as you copy the formulas down. ROW(A2) is basically a counter that increases as you copy the formulas down the columns.



So you are "building" a reference to different sheets based on the row number.

Answer 2

whilst doing considered one of those artwork, I assign the backside row of each and every worksheet by means of fact the totals to be carried to the precis sheet. i'm assuming which you're taking the comparable records from each and every worksheet and summarizing it interior the comparable cellular for all sheets on the precis sheet. The formulation would be like this for each entire being introduced forward from each and every sheet to the precis sheet: =SUM('First Sheet:final Sheet'!$A$1048576) =SUM('First Sheet:final Sheet'!$B$1048576) those 2 formulae will entire the cells A1048576 and B1048576 in all sheets between First Sheet and final Sheet which includes First Sheet and final Sheet. you could upload as many sheets as you like between the 1st Sheet and the final Sheet and the formulae on the precis sheet won't want any modification to accommodate this. i exploit this for financial ledgers the place there are a number of worksheets interior the ledge for distinctive categories. My precis sheet is often as much as this factor such by means of fact the final transaction. Works like a charm.

Answer 3

Quite possible, you need to use the current column in the formula to reference a sheet.



=INDIRECT(ADDRESS(1,2,1,1,

CONCATENATE("Sheet",

TEXT(COLUMN(),"#"))))



I put it on 3 lines to make sure it shows here, but it is just a single cell formula.



Check out the functions and their help to understand, but that should do it. Change the parameters in the ADDRESS formula to reference the exact cell in the target sheet you want.

Answer 4

I think you want a formula to change the sheet number/reference, I would imagine that's impossible.



If you have time and very few friends, consider PHP/MySQL.