Cross referencing a cell with sheet number

Question

Have to start a complicated model of cross-referencing all cells in a Financial model. I need to a code to start my model in which the code detects where a cell value is coming from and refer to the sheet number of that worksheet.

i.e. There are two worksheets, sheet 1 and sheet 2. Value in Range ("B3") of sheet1 comes from Range ("C4") of sheet 2. Likewise value in Range ("B4") of sheet1 comes from Range ("E7") of sheet 2. I want the code to detect this reference, and in Range("B4") of sheet1 write i.e. Name of Sheet2. Not looking for vlookup though.

Thanks

I think you want the result in column C not B as this would overwrite the original reference, or is that your intent? — RetiredGeek
There article does that: VBA: Determine All Precedent Cells – A Nice Example Of Recursion. It retrieves the cells` Precedents by triggering their Range.NavigationArrows (which can be turned on manually from the formula tab) — TinMan

RetiredGeek RetiredGeek · Accepted Answer · 2020-06-28T15:32:37

Naqi,

I think this will do what you want or at least give you a good starting point.

Option Explicit

Sub FindSheetRefs()

   Dim rngStart As Range
   Dim lRowOfs  As Long
   Dim zRef     As String
   
   Set rngStart = Range("B3")
   lRowOfs = 0
   
   Do
      zRef = rngStart.Offset(lRowOfs, 0).Formula
      zRef = Left$(zRef, InStr(zRef, "!") - 1)
      zRef = Right$(zRef, Len(zRef) - 1)
      rngStart.Offset(lRowOfs, 1) = zRef
      lRowOfs = lRowOfs + 1
            
   Loop Until rngStart.Offset(lRowOfs, 0) = 0

End Sub 'FindSheetRefs

Sample output:

Note: The numbers in column B show where on the reference sheet the value resides, e.g. 34 = C4. I did this for quick referencing in testing.

HTH

P.S. I tried the code pointed to by TinMan on my sample and got this result in the VBA Immediate Window:

===
[Book11]Sheet1!$A$1 has no precedent cells.
===

Cross referencing a cell with sheet number

1 Answers