Update function in other sheets

**magicool** · 02-02-2010, 04:00 AM

Hello,
I know I can update the content of a cell on any sheet from other sheet using for example: =SHEET!$G$22. This works ok for text and numbers, but how to get the same result for a function?
Example: in sheet1, cell B3 I have =SUM(A5:A9). When I modify in sheet1, cell B3: =SUM(A1:A9) I need his formula to change on other sheets as well. How to do that?
Thank You!

**pike** · 02-02-2010, 04:04 AM

Hi magicool
Do you mean like....
=SUM(Sheet1!A1:A9)

**magicool** · 02-02-2010, 04:09 AM

Thank you for the reply!
No, the formula gets its references from the sheet where it is. All I need is to have the same formula on almost all my sheets in the cell that I want. When I edit the formula in one place (sheet) the formula should also change in the other sheets.

**pike** · 02-02-2010, 04:53 AM

the only way I can think of is something like......

=SUM(INDIRECT(ADDRESS(Sheet1!G1,Sheet1!H1) &":"& ADDRESS(Sheet1!G2,Sheet1!H2)))

where G1 G2 are the rows and H1 H2 are the columns

**magicool** · 02-02-2010, 04:59 AM

Thank You!
But I used SUM just as an example.
I tried a custom function:

Function GetFormula(Cell as Range) as String
   GetFormula = Cell.Formula
End Function

but it just displays the formula in the cell, not the result.
One of the formula is something like: =IF(ISERROR(VLOOKUP(A4,'S1'!$A:$C,3,0)),0,VLOOKUP(A4,'S1'!$A:$C,3,0))

**pike** · 02-02-2010, 05:18 AM

or possibly

Sub ptest()
For Each ws In worksheets
     ws.Range("E1").Formula = "=SUM(D1:D6)"
  Next
End Sub

**magicool** · 02-03-2010, 03:33 AM

Yes! This works, but it does the modification for all sheets. How to put only a range of sheets?

**pike** · 02-03-2010, 04:10 AM

Hey magicool
Try.. something like....

Sub ptestxxxxx()
Dim myarray,z!
myarray = Array("Sheet2", "Sheet4") 
For z = 0 To UBound(myarray)
     Sheets(myarray(z)).Range("E1").Formula = "=SUM(D1:D6)"
  Next
End Sub

**magicool** · 02-03-2010, 04:25 AM

Yes! That is awesome! Thank you so much sir!

**pike** · 02-03-2010, 04:28 AM

pike will do, your welcome and NO, thank you for the question

Update function in other sheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Re: Update function in other sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions