using hidden characters

[farnye]

Can I place a label in a cell, and have one of the characters remain hidden while viewed?

My situation is as follows;

On one sheet, I have a list of structures all labeled with numbers. On a seperate sheet, I type in the number I want, and use VLOOKUP to fill in the remaining results for each structure that I wish to print off.

My problem is that some structures were added and are labeled 14, 15, 15a, 16, 17, 17a, 18, ect. 15 and 15a are two different strucutres with different values, but when I pull them into my final sheet with the vlookup commands, I want them both to be displayed as 15

could I label my sturctures something like 15 and 15_ or 15* or 15# so that i can type 15* and vlookup would pull my data for 15*, but would be displayed as 15 and the * character would be hidden.

 

[palusa]

Hi,

did you try the simplest way using the LEFT function?

The formula =LEFT(VLOOKUP(A7,B1:E4,1,0),2) where A7 is the lookup value (e.g. 15a) and the range B1:E4 contain the data (starting with struct codes: 15, 15a, 15*, 15_ etc), should return you always 15 as code.

Does it make sense to your needs?

_LF

 

[farnye]

In your example, is there any way that I can type in 15* in cell A7, vlookup would recognize it for other cells, but only 15 will be displayed in A7?

I basically want to know if I can type out 15*, 15_, and only have the 15 show. Or is there a formating style that will only display the first 2 characters?

 

[palusa]

In this case you probably need to write some VBA lines.
In the worksheet code i would write something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 1 Then            ' if you are in Column A
    If ActiveCell.Value Like "*_" Or ActiveCell.Value Like "*a" Then
            MyVal = ActiveCell.Value
           ActiveCell.Value = Left(ActiveCell.Value, 2)
           [B7] = WorksheetFunction.VLookup(MyVal, [B1:E2], 2, 1)
           '
           ' other statements using Myval as lookup value
           '
    End If
  End If
End Sub

Hope it helps

_LF

 

[joerd]

Alternative to palusa's, I make the colorindex of the last characters equal to 2 (which is white):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = [B1] Then
       Target.Characters(Start:=3, Length:=10).Font.ColorIndex = 2
    End If
End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[farnye]

Thanks, those ideas both help.

 

[analogkid2digitalman]

Joerd / palusa

I have not seen the [B7] =... and ...= [B1] notation used before. Is that a direct cell reference, sort of a short cut to where a named range could also be used?

Thanks

Wheels within wheels / In a spiral array
A pattern so grand / And complex
Time after time / We lose sight of the way
Our causes can't see / Their effects.

 

[palusa]

A2D,

yes, [A1] is the short form for Range("A1")

 

[palusa]

A little bit more elegant approach could be:
1\ your input column will be A
2\ make column B hidden
3\ columns C, D etc. will contain the VLOOKUPs' data referencing the lookup value in COLUMN B (e.g. in D9 =+VLOOKUP(B9;$D$1:$H$4;2;0))

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 1 Then                     ' I am in Column A
    With ActiveCell
        If IsEmpty(.Offset(0, 1).Value) Then  ' to be sure i'm inserting a
                                              ' new record
            .Offset(0, 1) = .Value
            If IsHere(ActiveCell.Value) Then
                .Value = Left(.Value, 2)
            End If
       End If
    End With
End If

End Sub


Public Function IsHere(V) As Boolean
A = Array("_", "a", "b", "c", "/", "+")
'
' don't include * or # as end char
'
IsHere = False
For I = 0 To UBound(A, 1)
    If V Like "*" & A(I) Then
        IsHere = True
        Exit Function
    End If
Next I
End Function

Function IsHere is used to check the termination character. You can customize the terminators just adding or modifying the elements of array A.
Don't use the characters * and # as terminators, otherwise the function is always True.

HTH.

_LF