Using UDF from Name Manager

[BDrew]

My formula, including an UDF to convert a Column number to a Column Name works when placed on the sheet but returns #VALUE! error when named ("Test")in the Name Manager

in cell AO205, Test=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205)

=Test returns #VALUE!
=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205) returns the text value in cell AO154 because Build!$AH205 holds the value 154


Owing to the size of the application, the formulas are stored in the name manager and changed to values only using VBA which is limited to 255 characters in any formula applied.

I know this is being caused by the Column Function being unable to function from the Name Manager but I am looking for a work around.

Any ideas welcome?

Brendan

Code follows

UDF
Public Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

 

[BDrew]

This becomes relative and can be copied across columns or down rows:

Test=INDIRECT("'Build'!" & LEFT(ADDRESS(ROW(),COLUMN(),4),MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(ROW(),COLUMN(),4))),FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(ROW(),COLUMN(),4))))-1) & Build!$AH205)

Thanks,Robert.

Dominic, thanks for your contribution, particularly the explanation for why my UDF errors on the Column & Row functions.

I will try yours too

Brendan

 

[Trebor76]

That's great Domenic

I just think you need the sheet reference so that the formula can work on any tab throughout the workbook like so:

=INDIRECT("'Build'!"&ADDRESS(Build!$AH205,COLUMN(Build!AO204),4))

Regards,

Robert

 

[BDrew]

I also set the Option enable Circular References iteration 2 times to permit the formula to handle the occurrence on the row specified in Build!$AH205. There are bits of the formula that ensure no conflict due to the circular reference on those rows but without the iterations the formula will not complete.

The resulting formula is copied about 2,000 times then converted to "values only" so as not to hog all the RAM while processing a further 63 columns.

Since the formulas are written using VBA and stored in the Name Manager, once the process runs, only results show but the formula can be applied to any cell for audit purposes without the other 1,999 instances.

Happy Days!

Thanks Gentlemen for your contributions.

Brendan