Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a formula from one line to another
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a formula from one line to another
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a formula from one line to another
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying text with more than one line | Excel Worksheet Functions | |||
copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
VBA for idenitfying text and copying it to a new line | Excel Programming | |||
Copying line from one sheet to another based on given criteria | Excel Worksheet Functions | |||
Copying a line | Excel Programming |