Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
I have two sheets (Sheet1 and Sheet2)
In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Seemed to work ok with CopyPasteSpecialPasteLink
"warrenshooter" wrote in message ... I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Another way...
Select both sheets before entering the formula. -- Jim Cone Portland, Oregon USA "warrenshooter" wrote in message ... I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Hi JLGWhiz,
Myabe I missed something but following your suggestion produced "=Sheet1!$A$5" in cell Sheet2!A5. I was after "=Average(Sheet1!A3,Sheet1!A4)" ...... not the value or the link Cheers "JLGWhiz" wrote: Seemed to work ok with CopyPasteSpecialPasteLink "warrenshooter" wrote in message ... I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Neat trick. I haven't seen that before.
Unfortunately the result in Sheet2!A5 was still "=Average(A3,A4)". I was after ="Average(Sheet1!A3,Sheet1!A4)" Cheers "Jim Cone" wrote: Another way... Select both sheets before entering the formula. -- Jim Cone Portland, Oregon USA "warrenshooter" wrote in message ... I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Try this
Sub CopyFormula() Dim shName As String Dim rngFormula As String Dim strFormula As String Dim iStart As Integer Dim strLeftF As String Dim strRightF As String Dim c As Variant Dim addr As String 'Get the activesheet name shName = ActiveSheet.Name & "!" On Error Resume Next 'get the cell formula For Each c In ActiveSheet.UsedRange If Not c.HasFormula Then 'do nothing Else addr = c.Address rngFormula = c.Formula 'change the formula iStart = WorksheetFunction.Find("(", rngFormula) strLeftF = Left(rngFormula, iStart) strRightF = Right(rngFormula, Len(rngFormula) - iStart) strFormula = strLeftF & shName & strRightF With Sheets("Sheet2").Range(addr) .Formula = strFormula End With Debug.Print c & vbTab & c & vbTab & strFormula End If Next c End Sub HTH Peter "warrenshooter" wrote: Neat trick. I haven't seen that before. Unfortunately the result in Sheet2!A5 was still "=Average(A3,A4)". I was after ="Average(Sheet1!A3,Sheet1!A4)" Cheers "Jim Cone" wrote: Another way... Select both sheets before entering the formula. -- Jim Cone Portland, Oregon USA "warrenshooter" wrote in message ... I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
I do not believe the logic that Excel uses will allow that result when
copying. Copy will either capture the value or the source of the value. I don't know how you could tell it to pick up the precedents for the value as part of the copy process. "warrenshooter" wrote in message ... Neat trick. I haven't seen that before. Unfortunately the result in Sheet2!A5 was still "=Average(A3,A4)". I was after ="Average(Sheet1!A3,Sheet1!A4)" Cheers "Jim Cone" wrote: Another way... Select both sheets before entering the formula. -- Jim Cone Portland, Oregon USA "warrenshooter" wrote in message ... I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
My result in Sheet2 was...
=AVERAGE(Sheet1!A3:A4) -- Jim Cone Portland, Oregon USA "warrenshooter" wrote in message ... Neat trick. I haven't seen that before. Unfortunately the result in Sheet2!A5 was still "=Average(A3,A4)". I was after ="Average(Sheet1!A3,Sheet1!A4)" Cheers "Jim Cone" wrote: Another way... Select both sheets before entering the formula. -- Jim Cone Portland, Oregon USA "warrenshooter" wrote in message ... I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Hi Everyone,
Thanks very much for all the responses. Some great ideas but didn't solve the problem. I actually start with the case below and need to make a copy of the formula into another sheet. It seems I need to 'decorate' the original references with the activesheets name. So some code will create "=Average(A3:A4)" to "=Average(Sheet!A3:A4).....it gets complicated when the original formula becomes "=Average(A3,A4,A5:A6)" or something similar. The results I want are the same as cut/paste without dependent formulas also changing their references. Cheers and thanks again for all the replies. "warrenshooter" wrote: I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Solve the problem.
Not the greatest code in the world but does the job Moves the src formula to a temp cell on the source sheet. Cut and Pastes it to a temp cell on dest cheet (this performs the source sheet referencing) Copy and Pastes the temp cell on the dest sheet to the dest cell (this updates the relative cell references) Cheers and thanks for everyones help. Sub CopyFormula() Dim srcformulaCell As Range Dim destformulaCell As Range Dim tempCell_WS1 As Range Dim tempCell_WS2 As Range Set srcformulaCell = Worksheets("Sheet1").Range("A5") Set tempCell_WS1 = Worksheets("Sheet1").Range("Z1") Set tempCell_WS2 = Worksheets("Sheet2").Range("Z1") Set destformulaCell = Worksheets("Sheet2").Range("A5") tempCell_WS1.Formula = srcformulaCell.Formula tempCell_WS1.Cut Worksheets("Sheet1").Paste Destination:=tempCell_WS2 Worksheets("Sheet2").Range("Z1").Cut Worksheets("Sheet2").Paste Destination:=destformulaCell End Sub "warrenshooter" wrote: I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tough one - Cell referencing in Excel/VBA
Select both sheets as Jim suggested, then enter this formula:
=Average(Sheet1!A3:A4) Regards, Per "warrenshooter" skrev i meddelelsen ... Solve the problem. Not the greatest code in the world but does the job Moves the src formula to a temp cell on the source sheet. Cut and Pastes it to a temp cell on dest cheet (this performs the source sheet referencing) Copy and Pastes the temp cell on the dest sheet to the dest cell (this updates the relative cell references) Cheers and thanks for everyones help. Sub CopyFormula() Dim srcformulaCell As Range Dim destformulaCell As Range Dim tempCell_WS1 As Range Dim tempCell_WS2 As Range Set srcformulaCell = Worksheets("Sheet1").Range("A5") Set tempCell_WS1 = Worksheets("Sheet1").Range("Z1") Set tempCell_WS2 = Worksheets("Sheet2").Range("Z1") Set destformulaCell = Worksheets("Sheet2").Range("A5") tempCell_WS1.Formula = srcformulaCell.Formula tempCell_WS1.Cut Worksheets("Sheet1").Paste Destination:=tempCell_WS2 Worksheets("Sheet2").Range("Z1").Cut Worksheets("Sheet2").Paste Destination:=destformulaCell End Sub "warrenshooter" wrote: I have two sheets (Sheet1 and Sheet2) In cell A5 in Sheet1 I have a formula "=Average(A3,A4)" I would like to copy this formula to cell A5 in Sheet2 but have the formula still reference Sheet1. In cell A5 in Sheet2 the formula should be "=Average(Sheet1!A3,Sheet1!A4)" If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no problem. Would love to hear the answer. It must be something simple. Warren |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a VB control on an Excel Cell | Excel Discussion (Misc queries) | |||
Tough One. Data on one cell seperated by comas. | Excel Discussion (Misc queries) | |||
Relative Cell Referencing in MS Excel | Excel Discussion (Misc queries) | |||
Tough one - Sum and IF with multiple referencing (array) | Excel Worksheet Functions | |||
referencing Excel sheet name in cell | Excel Worksheet Functions |