Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mirroring worksheets
I'm trying to mirror certain columns & rows from one worksheet to the next.
I have the formula that copies the cells from the first worksheet to the next, (='Sheet 1'!B10) but when rows are inserted on the first worksheet I'd like them to automatically be inserted on the next one...is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mirroring worksheets
One way ..
In Sheet2, Instead of using simple link formulas in A1, eg: =Sheet1!A1 with A1 then copied across / down Put instead in A1: =IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(Â*A1)-1,COLUMN(A1)-1)) Then just copy A1 across / down as required. This returns the same kind of links as the former but with the added flexibility that any new row insertions in Sheet1 (within the linked area) will now be reflected in Sheet2. Test it out and see for yourself. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Natalie" wrote: I'm trying to mirror certain columns & rows from one worksheet to the next. I have the formula that copies the cells from the first worksheet to the next, (='Sheet 1'!B10) but when rows are inserted on the first worksheet I'd like them to automatically be inserted on the next one...is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mirroring worksheets
Thanks Max, that answered my question...but I have just one more...
The two worksheets I have I obviously want the same info in the first two columns, (names of studies), and then the rest of the columns have different information on them that go with the study names (don't know if this is making any sense)...so if a row is entered on the first worksheet, what's happening is the rows are entered in the second worksheet (which is great), BUT the info in the rest of the columns is staying exactly where it was...and I need it to follow the first two columns, but not copy the info from the other worksheet. Am I making sense? "Max" wrote: One way .. In Sheet2, Instead of using simple link formulas in A1, eg: =Sheet1!A1 with A1 then copied across / down Put instead in A1: =IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(Â*A1)-1,COLUMN(A1)-1)) Then just copy A1 across / down as required. This returns the same kind of links as the former but with the added flexibility that any new row insertions in Sheet1 (within the linked area) will now be reflected in Sheet2. Test it out and see for yourself. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Natalie" wrote: I'm trying to mirror certain columns & rows from one worksheet to the next. I have the formula that copies the cells from the first worksheet to the next, (='Sheet 1'!B10) but when rows are inserted on the first worksheet I'd like them to automatically be inserted on the next one...is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mirroring worksheets
Maybe this will help me explain:
Sheet1: Column A Column B Column C Study #1 Company Name Company Study # Study #2 Company Name Company Study # Sheet2: Column A Column B Column C Study #1 Company Name Date Study #1 Report Received Study #2 Company Name Date Study #2 Report Received If I enter a row into Sheet1 I get this on sheet 2: Column A Column B Column C Date Study #1 Report Received Study #1 Company Name Date Study #2 Report Received Study #2 Company Name Does this make better sense? Any help is greatly appreciated! "Max" wrote: One way .. In Sheet2, Instead of using simple link formulas in A1, eg: =Sheet1!A1 with A1 then copied across / down Put instead in A1: =IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(Â*A1)-1,COLUMN(A1)-1)) Then just copy A1 across / down as required. This returns the same kind of links as the former but with the added flexibility that any new row insertions in Sheet1 (within the linked area) will now be reflected in Sheet2. Test it out and see for yourself. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Natalie" wrote: I'm trying to mirror certain columns & rows from one worksheet to the next. I have the formula that copies the cells from the first worksheet to the next, (='Sheet 1'!B10) but when rows are inserted on the first worksheet I'd like them to automatically be inserted on the next one...is this possible? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mirroring worksheets
Thanks Max, that answered my question
Good to hear that it answered your orig. posting .. ...but I have just one more... I'm out of suggestions for you on your new query. You might wish to try a fresh posting in .programming for a possible programming solution. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Natalie" wrote in message ... Thanks Max, that answered my question...but I have just one more... The two worksheets I have I obviously want the same info in the first two columns, (names of studies), and then the rest of the columns have different information on them that go with the study names (don't know if this is making any sense)...so if a row is entered on the first worksheet, what's happening is the rows are entered in the second worksheet (which is great), BUT the info in the rest of the columns is staying exactly where it was...and I need it to follow the first two columns, but not copy the info from the other worksheet. Am I making sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking or Mirroring workbook | Excel Worksheet Functions | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
Mirroring observations in Excel table along diagonal | Excel Discussion (Misc queries) | |||
Excel Mirroring Data | Excel Worksheet Functions | |||
Mirroring cells | Excel Discussion (Misc queries) |