Stop formula changing cell reference but change sheet number when dragged?

**tmtjamie** · 02-08-2010, 01:25 PM

Im Sure it is really simple but can't figure it out.

What do i need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?

Please Login or Register  to view this content.

**NBVC** · 02-08-2010, 01:30 PM

=INDIRECT("'"Sheet"&ROWS($A$1:$A1)&"'!D6")

copied down

**tmtjamie** · 02-08-2010, 02:37 PM

Thanks For Replying NBVC

Do i need to change anything on this code as the formula isn't working for me?

Sorry if it is obvious

**NBVC** · 02-08-2010, 02:42 PM

Yes, I added one to many quotes:

Try: =INDIRECT("'Sheet"&ROWS($A$1:$A1)&"'!D6")

**tmtjamie** · 02-08-2010, 03:22 PM

Perfect!!

Thank You

**M7A0S8T8E5R** · 01-15-2016, 06:16 PM

How would I use this same formula but to copy across? I have a spreadsheet that has Months in the tabs and a summary sheet. Each month of information is exactly the same so for instance in cell N820 I would want from Month1 cell D403's information. M820 I would want Month2 cell D403's information and so on, up to Month13. Any help would be fantastic.

Thank you.