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]

You can use this, where Cell A1 is the cell address result from the formula above
=FORMULATEXT(OFFSET(A1,1,0))

 

[trishcollins]

You can use this, where Cell A1 is the cell address result from the formula above
=FORMULATEXT(OFFSET(A1,1,0))

Thanks, worked like a charm!!!!

 

[trishcollins]

Thanks, worked like a charm!!!!

Spoke too soon. It's actually returning the formula for the cell "Count" which is the formula to get the name of the header, not the formula in the first row of the referenced table. I am sure this is an indirect thing

 

[Jeffrey Mahoney]

Yeah, you're right.
=FORMULATEXT(OFFSET(INDIRECT(H1),1,0))
Add the Indirect. H1 in this example the is the address provided from the formula above

 

[trishcollins]

Yeah, you're right.
=FORMULATEXT(OFFSET(INDIRECT(H1),1,0))
Add the Indirect. H1 in this example the is the address provided from the formula above

That did it! Thanks!!!!

 

[trishcollins]

Using that same formula, is it possible to return the Data Validation formula, as I am hoping to document the offset formulas I use in those as well. Here is the formula I used with your help above, so that if it's a range, then I just grab the formula from the range, if it's a table, I use the header to look up the cell address and offset by 1, but if there is no formula, but I have put in a drop down menu that has a formula (offset), is it possible to return that formula or list? That's why I had to use isna, for cells that don't have a formula, but where I am documenting the data validation rules for that data column.

=IF([@[Table or Named Range]]="Range",FORMULATEXT([@Count]), IF(ISNA(FORMULATEXT(OFFSET(INDIRECT([@Count]),1,0))),"No Formula",FORMULATEXT(OFFSET(INDIRECT([@Count]),1,0))))

 

[trishcollins]

sorry, here is the formula corrected:
=IF([@[Table or Named Range]]="Named Range",FORMULATEXT(INDIRECT([@Count])),
IF(ISNA(FORMULATEXT(OFFSET(INDIRECT([@Count]),1,0))),"No Formula",
FORMULATEXT(OFFSET(INDIRECT([@Count]),1,0))))

It is the test for "No Formula", I would like to get the data validation formula

 

[Jeffrey Mahoney]

I don't know of a way to get the Data Validation formula using a function, but this User Defined Function can. Add the code below to a Standard VBA Module. Reference the function in a Cell like:
=GetDVFormula(I3)

Function GetDVFormula(R As Range) As String
  GetDVFormula = R.Validation.Formula1
End Function

 

[trishcollins]

I copied and pasted the code into a module, then put this is a separate column to test. I am getting #VALUE as a result. Remember I need to do an offset, as the address I have is referencing the header, and I need to move down one row to get the correct address of the cell. Any suggestions on how to work around this?

=GetDVFormula(FORMULATEXT(OFFSET(INDIRECT([@[Table/Range Cell Reference]]),1,0)))