Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way of "mirror" several cell areas on one sheet to others?
I have several areas with information I want in the exact same cells in
other sheets [in the same workbook]. I first ran a macrorecording, but of cource there is problems with that - - It take a lot of time and power - Difficult to do with worksheet_change or worksheet_deactivate without loop because you need selections in the different sheets and that make events. I have tried Function ='Sheet1'!B34 in cell Sheet2 B34 because it run on a more effective code and not on a vb overlap. In that case you need a =IF('Sheet1'!B34="";"";='Sheet1'!B34) to handel all empty cells. BUT it will be a lot of links and if you move 'Sheet1'!B34 it will cause a #Reference value in Sheet2 B34. All possible forms, formulas and formats should be mirrored! Folks, what should I do ???? I trust in you... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way of "mirror" several cell areas on one sheet to others?
Hi
"Imbecill" wrote in message ... I have several areas with information I want in the exact same cells in other sheets [in the same workbook]. I first ran a macrorecording, but of cource there is problems with that - - It take a lot of time and power - Difficult to do with worksheet_change or worksheet_deactivate without loop because you need selections in the different sheets and that make events. I have tried Function ='Sheet1'!B34 in cell Sheet2 B34 because it run on a more effective code and not on a vb overlap. In that case you need a =IF('Sheet1'!B34="";"";='Sheet1'!B34) to handel all empty cells. BUT it will be a lot of links and if you move 'Sheet1'!B34 it will cause a #Reference value in Sheet2 B34. Only when you delete it. The formula is adjusted automatically, whenever you rename the source sheet, and even when you move it to new file, save it and close. When you want the formula not to return error when source sheet is missing, then use =IF(ISERROR(IF('Sheet1'!B34="";"";='Sheet1'!B34)); "";IF('Sheet1'!B34="";"";= 'Sheet1'!B34)) but I don't see many situations where you'll need this. Or before ou delete the source sheet, convert links to values (PasteSpecial.Value) All possible forms, formulas and formats should be mirrored! With links, only values are returned. When there is a formula in source cell, the the value returned by formula is mirrored. About formats - do you really need that formats are changing automatically? Then you don't avoid a lot of VBA. Otherwise, to create a sheet as replica of original one, right-click on sheet's tab, select Move or copy', check 'Create a copy', determine destination and press OK. After this rename the new sheet (or save workbook) and clear all abundant (p.e. user entries) from new sheet(, and in your case insert links to original sheet). -- Arvi Laanemets (When sending e-mail, use address arvil<Attarkon.ee) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Wanted: Worksheet Tabs that "mirror" the first few columns of Pre | Excel Discussion (Misc queries) | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
I am trying to "mirror" a row in excel, help?? | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |