I need to copy a large line containing many formulas and replicate it to as
many as 1,000 additional rows. My problem is I know that the $ is used to
keep consistency in the cells (which I need) but I am looking to increment
the referencing sheets. For example: =+'DM-001'!$B$29 is my formula. The
'DM-001' is my worksheet name and I need to increment to 'DM-002' and so on
while maintaining the B29 cell. That part I've figured out. But when I copy
the rows I'd like worsheets to increment automatically--without my having to
manually enter them within every cell. I have about 20 columns on each row
to copy. Thank you.

You can build you sheet name inside and "indirect" function so that it
will automatically increment when copied down. For example:

=INDIRECT("DM-"&TEXT(ROW()+1,"000")&"!$a$29")

Use the number that is added row() to make the row of the formula on
the master sheet line up with the sheet number. You only need to build
the formulas like this if they reference the other sheet.

It seem like a lot of different worksheets if each of the 1000 or so
rows references a different worksheet as I have assumed.

Good luck.

Ken
Norfolk, Va



Emerson1988 wrote:
> I need to copy a large line containing many formulas and replicate it to as
> many as 1,000 additional rows. My problem is I know that the $ is used to
> keep consistency in the cells (which I need) but I am looking to increment
> the referencing sheets. For example: =+'DM-001'!$B$29 is my formula. The
> 'DM-001' is my worksheet name and I need to increment to 'DM-002' and so on
> while maintaining the B29 cell. That part I've figured out. But when I copy
> the rows I'd like worsheets to increment automatically--without my having to
> manually enter them within every cell. I have about 20 columns on each row
> to copy. Thank you.

Ken... thanks so much for answering. Unfortunately, you're dealing with a
moron on this end. :-)

Let me clarify my dilemma and see if the answer remains the same. I tried
using the formula you gave but am not sure what data in the formula i need to
change--other than cells around the $$ signs.

So... I have a row (and will have about 1,000) that references about 1,000
worksheets. Each row provides a linear summary of data contained on certain
lines in those "pretty" worksheets--perhaps 20 columns in all.

The sheet in question is my summary sheet where I know that certain cells on
every row remain static so I use the $$'s to ensure that. As I copy the row
on the summary sheet, I need the next row to increment by one. The naming
convention is the same. DM-001, DM-002, DM-003 and so on. When I copy the
row--with all those tedious formulas I've entered--everything is great but
the referencing sheets are NOT incrementing since they are "hardcoded" with
DM-001. The static data cells copy correctly. So I am simply (but maybe not
so simple) looking to copy one row and paste onto the next 999, having the
formulas preserved without my having to edit 999 rows x 20 cells.

You seem like an expert. I hope you have some patience, too. I so very
much appreciate your taking the time to help.

-Gary S.
Parsippany, NJ

"Ken" wrote:

> You can build you sheet name inside and "indirect" function so that it
> will automatically increment when copied down. For example:
>
> =INDIRECT("DM-"&TEXT(ROW()+1,"000")&"!$a$29")
>
> Use the number that is added row() to make the row of the formula on
> the master sheet line up with the sheet number. You only need to build
> the formulas like this if they reference the other sheet.
>
> It seem like a lot of different worksheets if each of the 1000 or so
> rows references a different worksheet as I have assumed.
>
> Good luck.
>
> Ken
> Norfolk, Va
>
>
>
> Emerson1988 wrote:
> > I need to copy a large line containing many formulas and replicate it to as
> > many as 1,000 additional rows. My problem is I know that the $ is used to
> > keep consistency in the cells (which I need) but I am looking to increment
> > the referencing sheets. For example: =+'DM-001'!$B$29 is my formula. The
> > 'DM-001' is my worksheet name and I need to increment to 'DM-002' and so on
> > while maintaining the B29 cell. That part I've figured out. But when I copy
> > the rows I'd like worsheets to increment automatically--without my having to
> > manually enter them within every cell. I have about 20 columns on each row
> > to copy. Thank you.
>
>