trishcollins
Board Regular
- Joined
- Jan 7, 2006
- Messages
- 71
I am trying to document all of my formulas, including any formulas used in the data validation source. I have several that use offset. I need the vba code to return the "source" of the data valuation based on a particular cell address. This is my preference to copying and pasting, because every time I make a change to the offset, I need to make a change to the documentation table, and would prefer it to be automatic.
The documentation is in a table that contains the type of cell "Table or Named Range", address of the cell "Table/Range Cell Reference", a description or the purpose of the formula, and the "Current Formula". The offset in the formula is used for cell types identified as a "Table" because the actual address of the cell, is the table header, and I am looking for the formula in the first row of the table below the header. The same would be true for the Source Validation formula, since it would be in the first row after the header. For cells identified as a "Named Range", there is no offset, I just want the formula of the cell at the exact address. At the moment, the formula in the "Current Formula" looks like this, with a placeholder for the Source Data Validation in the form of a text message "Data Validation Offset Formula":
=IF([@[Table or Named Range]]="Named Range",
FORMULATEXT(INDIRECT([@[Table/Range Cell Reference]])),
IF(ISNA(FORMULATEXT(OFFSET(INDIRECT([@[Table/Range Cell Reference]]),1,0))),"Data Validation Offset Formula",
FORMULATEXT(OFFSET(INDIRECT([@[Table/Range Cell Reference]]),1,0))))
The bold/italic portion of the formula is what I am using so far to avoid an error for the cells that do no contain a formula, but where I know there is an offset formula for data validation. I would like to keep the isna for future testing future formulas, but would like to replace the "Data Validation Offset Formula" text with another if statement that says, if there is no formula in the cell itself, then check to see if a source data validation formula exists, and either return the offset formula, or an error message that one does not exist. Keeping in mind that I am happy to do this in VBA, I just need to be able to populate the entire "Current Formula" table column.
Trish
The documentation is in a table that contains the type of cell "Table or Named Range", address of the cell "Table/Range Cell Reference", a description or the purpose of the formula, and the "Current Formula". The offset in the formula is used for cell types identified as a "Table" because the actual address of the cell, is the table header, and I am looking for the formula in the first row of the table below the header. The same would be true for the Source Validation formula, since it would be in the first row after the header. For cells identified as a "Named Range", there is no offset, I just want the formula of the cell at the exact address. At the moment, the formula in the "Current Formula" looks like this, with a placeholder for the Source Data Validation in the form of a text message "Data Validation Offset Formula":
=IF([@[Table or Named Range]]="Named Range",
FORMULATEXT(INDIRECT([@[Table/Range Cell Reference]])),
IF(ISNA(FORMULATEXT(OFFSET(INDIRECT([@[Table/Range Cell Reference]]),1,0))),"Data Validation Offset Formula",
FORMULATEXT(OFFSET(INDIRECT([@[Table/Range Cell Reference]]),1,0))))
The bold/italic portion of the formula is what I am using so far to avoid an error for the cells that do no contain a formula, but where I know there is an offset formula for data validation. I would like to keep the isna for future testing future formulas, but would like to replace the "Data Validation Offset Formula" text with another if statement that says, if there is no formula in the cell itself, then check to see if a source data validation formula exists, and either return the offset formula, or an error message that one does not exist. Keeping in mind that I am happy to do this in VBA, I just need to be able to populate the entire "Current Formula" table column.
Trish
