Excel formula to return the formula from a table using the table header as a reference

[trishcollins]

I have successfully used this formula to get the exact address of a header in a table, which I can then use in a Hyperlink formula, to go directly to that header. What I am trying to do, is get the formula in the first row of the column under the same header, and display it in text form. The reason for this, is that allows me to document the formulas with definitions on another worksheet. The thing to remember is that I am checking first to see if the initial enter text is referring to a Table or a Named Range, as I treat Named Ranges differently. The portion in bold/italics is what I am concerned about. What do I change to get the contents of the next row, which is a formula, to return in text format.

=IF([@[Table or Named Range]]="Table",CELL("address",(INDIRECT([@[Table or Range Names]]&"[[#Headers], [" & [@[Header or Range Name]]&"]]"))),CELL("address",INDIRECT([@[Table or Range Names]])))

The result of this formula is: '[DND-SCN-Client Application Onboarding to SCN Tool-WIPv1.43.xlsm]Business Needs Matrix'!$K$1

I want the result of the new formula to display the formula in the first row below ($K$2) as text.

Trish

 

[Jeffrey Mahoney]

The version of GetDVFormula above is asking for a range. You are providing it the Formula inside the cell. I changed the UDF to read the string for the cell Address and allow you to specify the offset

Use it like:
=GetDVFormula([@[Table/Range Cell Reference]],1,0)
It will return the Data Validation Formula in the row directly below the range specified in [@[Table/Range Cell Reference]]

Function GetDVFormula(Adr As String, Optional R As Long, Optional C As Long) As String
  Dim SheetName As String
  Dim Exclamation As Long
  Dim Sht As Worksheet
  Dim Rng As Range
  
  Exclamation = InStr(Adr, "!")
  If Exclamation > 0 Then
    SheetName = Left(Adr, Exclamation - 1)
    If InStr(SheetName, "'") > 0 Then
      SheetName = Replace(SheetName, "'", "")
    End If
    Set Sht = Sheets(SheetName)
    Set Rng = Sht.Range(Mid(Adr, Exclamation + 1, 100)).Offset(R, C)
  Else
    Set Sht = ActiveSheet
    Set Rng = Sht.Range(Adr).Offset(R, C)
  End If
  
  GetDVFormula = ""
  On Error Resume Next
  GetDVFormula = Rng.Validation.Formula1
  On Error GoTo 0
End Function

 

[trishcollins]

This still returns #VALUE

 

[Jeffrey Mahoney]

Can you please show me what results or values you have in the column [@[Table/Range Cell Reference]]

 

[Jeffrey Mahoney]

This is how I am using it. I changed the UDF again to accommodate the filename when using =CELL("Address",[Reference])

Book2
	A	B	C	D
1	One	Two	Table/Range Cell Reference	DV Formula
2	1	2	[Book2]Sheet2!$I$2	=IF(TRUE=TRUE,1,2)
3	2	3	[Book2]Sheet2!$J$2	=IF(LEN("Jeff")=4,4,0)
Sheet1

Cell Formulas
Range	Formula
C2	C2	=CELL("address",Sheet2!I2)
D2:D3	D2	=GetDVFormula([@[Table/Range Cell Reference]],1,0)
C3	C3	=CELL("address",Sheet2!J2)

Function GetDVFormula(Adr As String, Optional R As Long, Optional C As Long) As String
  Dim SheetName As String
  Dim Exclamation As Long
  Dim Sht As Worksheet
  Dim Rng As Range
  Dim Bracket As Long
  Dim aStr As String
  
  Bracket = InStr(Adr, "]")
  If Bracket > 0 Then
    aStr = Mid(Adr, Bracket + 1, 100)
  Else
    aStr = Adr
  End If
  Exclamation = InStr(aStr, "!")
  If Exclamation > 0 Then
    SheetName = Left(aStr, Exclamation - 1)
    If InStr(SheetName, "'") > 0 Then
      SheetName = Replace(SheetName, "'", "")
    End If
    Set Sht = Sheets(SheetName)
    Set Rng = Sht.Range(Mid(aStr, Exclamation + 1, 100)).Offset(R, C)
  Else
    Set Sht = ActiveSheet
    Set Rng = Sht.Range(aStr).Offset(R, C)
  End If
  
  GetDVFormula = ""
  On Error Resume Next
  GetDVFormula = Rng.Validation.Formula1
  On Error GoTo 0
End Function

 

[trishcollins]

Can you please show me what results or values you have in the column [@[Table/Range Cell Reference]]

There is a formula in that cell which if it is a identified as a table in another column, looks up the header name from a different column to return the address: =IF([@[Table or Named Range]]="Table",CELL("address",(INDIRECT([@[Table or Range Names]]&"[[#Headers], [" & [@[Header or Range Name]]&"]]"))),CELL("address",INDIRECT([@[Table or Range Names]])))

The value in that cell is: '[DND-SCN-Client Application Onboarding to SCN Tool-WIPv1.43.xlsm]Business Needs Matrix'!$K$1

DND-SCN-Client Application Onboarding to SCN Tool

Table or Range Names	Table or Named Range	Sheet	Sheet Name	Header or Range Name	Table/Range Cell Reference
Business_Needs_Matrix	Table	C:\Users\collins.p\Documents\Work\DND\D4 Cloud Enablement\Secure Cloud Networking (SCN)\SCN Onboarding Tool\[DND-SCN-Client Application Onboarding to SCN Tool-WIPv1.43.xlsm]Business Needs Matrix	Business Needs Matrix	Mapped to Preferred Network Connectivity	'[DND-SCN-Client Application Onboarding to SCN Tool-WIPv1.43.xlsm]Business Needs Matrix'!$K$1

Trish

 

[Jeffrey Mahoney]

Are any of the references in the column "Table/Range Cell Reference" referring to other workbooks?

And if they are, are they closed or open. If they are closed, then the code is not going to work

 

[trishcollins]

Are any of the references in the column "Table/Range Cell Reference" referring to other workbooks?

And if they are, are they closed or open. If they are closed, then the code is not going to work

That makes sense. At least the ones that are the data validation formulas are open. I will simply put the logic in place, and add "offset" to my table/range list, so that I can then use "offset" to determine if I need to run the code or not. Just happened the first one I tested, was on a closed spreadsheet, hidden from users

 

[trishcollins]

thanks!!!