0

I have different spreadsheets (on the following photo, from 1 to 25); They have the same layout ( Photo here ).

I want to create a table with the E7 cell of each spreadsheet (A table of every "Total").

I tried this by extanding the formula...sounds good, doesn't work.

mewX Max
  • 31
  • 1
  • 8

2 Answers2

0

Put this in the first cell:

=INDIRECT("'" & ROW(1:1) & "'!E7")

And copy down.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

If you put the following formula in the first 25 rows of your target sheet, it should do what you're trying to do:

=INDIRECT(ADDRESS(7,5,1,1,ROW()))

ADDRESS is formatting an address based on the parameters provided.

  • 7 is the row
  • 5 is the column (Column E)
  • 1 says use an absolute reference $E$7
  • 1 says us the A1 style address, not the R1C1 style.
  • ROW() says use the sheet having the same name as the row where the column appears. So, in row 1, it will use sheet named "1". In row 8, it will use the sheet named "8"

INDIRECT says to get the value from the provided address.

If you need to put the list in a set of rows farther down the sheet, just use ROW()-N where N is one less than the number of rows from the where your list starts. So, if your list of totals starts in row 3, use ROW()-2.

StoneGiant
  • 1,400
  • 1
  • 9
  • 21