how to increment worksheet reference when copying formula

**forestar** · 02-26-2014, 12:56 PM

Hi, I'm having to copy formula on a tabulation sheet that compiles information from other worksheets, and I need to change the formulas in each row to refer to the successive worksheets. Right now I don't know any other way than changing the formula in each cell, and this is taking way too long.

Example:

One cell's formula: =COUNTIF('5'!$F$21:$T$50,TABULATION!C$5) In the next row down, I need it be: =COUNTIF('6'!$F$21:$T$50,TABULATION!C$5), then =COUNTIF('7'!$F$21:$T$50,TABULATION!C$5), and so on.

There was another post like this two years ago, but I didn't understant the answer:
http://www.excelforum.com/excel-gene...reference.html

Can anyone help?

Thanks

**ChemistB** · 02-26-2014, 01:21 PM

=COUNTIF(INDIRECT(ROWS($A$1:A1)+4&"!$F$21:$T$50"),TABULATION!C$5)
Does that work for you?

**Skibum346** · 02-26-2014, 01:28 PM

Hi Guys,

I have exactly the same problem except my increment is along columns. Helpfully my Tabs are named "1", "2" etc up to "420" and I REALLY don't want to have to manualy amend each formula.

The formula I have is to work out a percentage using all the entries in a particular column (Basically "Actual over Maximum" where the high score for the maximum is 2)

=SUM('1'!N:N)/(COUNTA('1'!N:N)*2)

I have tried a variety of concatenation tricks but every time the sheet ref ends up with quatarion marks around it in the formula which causes a "Value#" error.

The first column in the array (?) is column B and in row 1 I have a calculation that returns the correct number of the tab (i.e. for Column B, B1 formula result is 1). I had hoped to refer to this in some way as the sheet name.

Help!

I'll have a play with the response above but as mine is column progression rather than rows I suspect it won't work. See line above!

Kindest

Skibum