Continuous Formula for Pulling Data from Several Tabs

[SheilaYoder]

Hi, i'm working on a project for our organic feed business, and I've created a spreadsheet for our purchasing corn contracts. Each new contract is another sheet(tab) in the same spreadsheet. I've included a "Totals" sheet as well, that is pulling certain information from each of these contracts, using this formula: ='Contract1'!H9 Because i'm creating a column with all this info, i want it to pull from each of these contracts. Is there a way i can copy this formula down the column? I've tried using the normal select and pull down method, but it's just changing the formula to ='Contract1!H10 instead of ='Contract2'!H9 , ='Contract3'!H9 and so on. Is there any way to copy this formula (changing only the tab to pull from and NOT the specific cell) instead of doing each one seperately? (very time-consuming) Appreciate any help i can get asap! Thanks....

 

[Rijnsent]

Hi SheilaYoder,

Try the INDIRECT formula, maybe combined with the ADDRESS formula. E.g.:

The INDIRECT formula changes a text into a reference. So if my text would be "Sheet4!E16", the INDIRECT formula can "translate" that to the reference to Sheet4, cell E16 and pick up the value of that cell.
So I'd create a column header with your cell reference name, say "H9" (type it as text), and a row value with the sheet name, say "Contract1", "Contract2", etc.
The formula at the intersection of the headers would be =INDIRECT(A2&"!"&B1) -> the & sign basically "glues" the texts together, so a valid reference is created.

An alternative, if you just want to add up values of certain cells of the contracts, this kind of formulae also works, adding up one cell in 3 sequential sheets:
=SUM(Sheet2:Sheet4!B6)

Cheers,

Koen