using hidden characters
using hidden characters
(OP)
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.
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.





RE: using hidden characters
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
RE: using hidden characters
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?
RE: using hidden characters
In the worksheet code i would write something like:
CODE
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
RE: using hidden characters
CODE
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.
RE: using hidden characters
RE: using hidden characters
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.
RE: using hidden characters
yes, [A1] is the short form for Range("A1")
RE: using hidden characters
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))
CODE
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