Excel- copy down with sheet number increment?

**brierleystuartj** · 06-21-2012, 06:04 AM

currently i have this

='16th'!$E$16 but i need it to change the sheet (16th) to the next sheet (17th) as i copy formula down to get

='16th'!$E$16
='17th'!$E$16
='18th'!$E$16
and so on

research says use something like this

=INDIRECT("Sheet"&row()&"!A2")

but i cant implement my data eg sheet name and cell and get it to work ( as i dont know at what point in the formula it should go)

I am a novice and im a bit out of my depth but im at the end of the project, just need to do ths summary page so i have to finish it. All help is appreciated and please explain things in simple terms.

thanks again

**npamcpp** · 06-21-2012, 06:14 AM

If you want your formula to start with

='16th'!$E$16

then try

=INDIRECT(ROW(A16)&"th!E16")

which will convert to just that formula, regardless of which row you put it in. Then copy down and the next row will fetch the value in E16 on sheet "17th".

**Paul** · 06-21-2012, 06:17 AM

Hi Brierly, welcome to the forum. Perhaps this will work..

=INDIRECT("'"&ROW(A16)&"th'!$E$16")

**puru.sve** · 06-21-2012, 06:18 AM

Hi , You can use this formula ....

=INDIRECT("'" &16 + ROW()-22 & "th'!"&"$E$16")

change the Row() -22 as your row number .... means if you start putting formula from row 16 then use it as Row() -16 .

**brierleystuartj** · 06-21-2012, 06:55 AM

=INDIRECT(ROW(A16)&"th!E16") this did work for some sheets but is retrieving #REF! for some. Does this mean i have problems in cell E16.

Also the workbook records and calculates accounts for the month. Sheet 1 is a summary page and sheet 2 starting with 16th, then 17th and so on to 31. Then 1st,2nd so on. This is because the company account month runs from 16th.

I have #REF! in 21st, 22nd, 23rd and 31st. Then 1st to 15th,im guessing because of the way it starts with 16th.

Thanks for all suggestion so far and future help.

**npamcpp** · 06-21-2012, 09:26 AM

#Ref! means that a reference does not exist. Check if the reference that is created by the formula is a valid one in your file. For example, if you copy the formula down, it will evaluate to

='16th'!$E$16
='17th'!$E$16
='18th'!$E$16
='19th'!$E$16

All these sheets need to exist, otherwise you get a #Ref! error. If that does not explain it, please post a sample file.

**brierleystuartj** · 06-21-2012, 05:04 PM

Yes i didnt change the th in formula to st nd or rd. All is working well.

Should have this finished tommorow fingers crossed.

Thanks for all your help. Much appreciated. Im going to crack open a can of fosters in your honour.