Refreshing Linked Cells

[pricehopper]

Hi!

I have a workbook into which I want to insert worksheets.

There is a worksheet (CONSOLIDATED) which will consolidate the data from the other sheets which are inserted on a monthly basis.

An example formula (on CONSOLIDATED) is

='RED'!C24

Where RED is the worksheet that will be inserted.

Clearly - when there is no worksheet RED I receive an #REF! error.

However, when I insert a worksheet RED, I still receive the #REF! error - Is there some way that the cell will automatically update to reference the (newly inserted) worksheet and retrieve the required value?

Hope I've been clear

Thanks for any help!!


EDIT - Using Excel 2003

 

[Jon von der Heyden]

You can use the INDIRECT function:

=INDIRECT("RED!C24")

The following will return the value from the same cell address that the formula occupies, but from the 'RED' worksheet (supporting relative referencing):

=INDIRECT(ADDRESS(ROW(),COLUMN(),,,"RED"))

 

[pricehopper]

Thanks heaps Jon,

I've tried using the basic =INDIRECT("RED!C24")-

:copying your formula across
:writing the formula from scratch and clicking on the relevant cell in RED.

It doesn't seem to work for me either when the worksheet RED is already in the workbook or when RED is added later.

I still receive the #REF! error.

I'm afraid the latter suggestion didn't make perfect sense to me - I'm not entirely sure what I need to include to make it work and excel help was, well, not exactly much help...

Any suggestions!?

 

[DonkeyOte]

Inserting a sheet is not a Volatile action - you can however "trap" the event at workbook level and enforce a calculation.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Calculate
Next ws
End Sub

above to reside in ThisWorkbook in VBE - if you know the sheets to be calculated then restrict the above accordingly.

Use a standard handler to hide the ISREF as desired within the cell functions.

 

[DonkeyOte]

FWIW if you don't mind being prompted then you could avoid use of INDIRECT and perhaps adopt an approach of:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Cells.Replace "=","="
Next ws
End Sub

how practical ? debatable.