use same formula of reference cell

Copper Contributor

I have 50 forms of data, the sheet that combines the data from all 50 tabs is called the database tab. Each form has 100 rows of data in the database tab. The first 100 rows relate to the 'template form', then rows 100-199 relate to form #1, rows 200-299 relate to form #2, and so on. 

 

The first 100 rows for the 'template form' has different calculation formulas in the rows. Rather than copy and pasting the template rows 50 times, I'd like to be able to add a formula that will reference the formula from the template form rows, so that I can drag down from rows 100 onwards. 

 

For example: 

in Row 101 - I'd like to reference formula in row 1... is there a function for this? I've tried googling around. 

 

I had tried the 'FORMULATEXT' and 'EVAL' function together, BUT since the calculation formulas have 'INDIRECT' functions referencing specific rows/ columns it doesn't work. 

 

Any tips?! Hope my explanation makes sense... 

 

 

1 Reply
Sounds like you could use the INDEX function to get the data you need. Suppose you put the row # into cell A1 of a Form sheet and you want to get data from column A of your DATABASE tab into a form, starting from cell A3:
=INDEX(DATABASE!A:A,$A$1+ROW()-2)
will give you a formula you can drag down and right. If you need the 50 rows from row 150 and on, just change cell A1.