Hi,
ActiveWorkbook.Names(vInput2).Delete
I hope it works
jindon
Hi,
ActiveWorkbook.Names(vInput2).Delete
I hope it works
jindon
Hi,
If ActiveCell.Column=2
is it?
jindon
Hi,
if the data in col.B rows 2 to 53 with heading in row 1
=INDEX(B2:B53,MAX(IF(B2:B53<>"",ROW(B2:B53))-1),1)
holding down Ctrl+Shift keys and press Enter to get out of the formula bar
will return the value in the "bottom-most non-blank cell" within the range.
jindon
Hi,
Here's how it works
Private Sub Worksheet_Change(ByVal Target As Range)
'trigger to run a macro
With Target
If .Count <> 1 Then Exit Sub
'if number of cells selected is not 1, then do nothing and end macro
If .Column <> 3 And .Row < 15 And .Row > 25 Or .Value = "" Then Exit Sub
'if activecell is not in col.C, between row 15 to 25, then do nothing and end macro
If WorksheetFunction.CountIf(Range("d15:d25"), .Offset(, 1).Value) > 1 Then
'if countif(D15:D25,corresponding value in col.D to active cell) is greater than 1,
MsgBox "Invalid Code"
'Message Box stating "Invalid Code"
.ClearContents
'clear the active cell value
.Select
'activate the cell again
Exit Sub
ElseIf WorksheetFunction.CountIf(Range("e15:e25"), .Offset(, 2).Value) > 3 Then
MsgBox "Invalid Club"
.ClearContents
.Select
Exit Sub
End If
End With
End Sub
Display More
Hi,
I'm not sure if the attached is what you desire.
Hi, Phill
Sorry, I don't quite understand what your friend is trying to do.
Can't you just use
WorksheetFunction.HLookup?
c.offset(Range(gA2h).value=worksheetfunction.hlookup(c.myrange,.....
??
jindon
Hi,
Probably, conbination of INDEX and MATCH function would fit.
where lookup table area is C1:G5
=INDEX(C1:G5,MATCH(A2,C1:C5,0),MATCH(A1,C1:G1,0))
Or
I'm little bit comfused. what the question means is like
=HLOOKUP(A1,C1:G5,A2+1,FALSE)
=INDEX(C1:C1:F5,A2+1,MATCH(A1,C1:G1,0))
=INDIRECT(LOOKUP(A1,{"andrew","bob","john","mike";"e","f","d","c"})&A2+1)
=OFFSET(INDIRECT(ADDRESS(1,LOOKUP(A1,{"andrew","bob","john","mike";"5","6","4","3"}))),A2,)
??
jindon
Good!
Looks everything's fine.
jindon
Hi,
If you are not familier with what firefytr suggested, here's another solution.
I'm not sure if you are familier with the formula though.
Assuming:
Sheet names are Sheet1 and Sheet2 respectively
ID column in both sheets are in column A and has heading in 1st row
Extract desired data in Sheet3
With Sheet3
1) Set lookup table in range E1:G3
E1:0, F1: Sheet1!, G1:0
E2:=COUNTA(Sheet1!A:A), F2:Sheet2!, G2:=E2-1
E3:=COUNTA(Sheet2!A:A)+E2,
2) extract all the ID in columnA
in cell A2
=IF(ROW()<MAX(E:E),INDIRECT(VLOOKUP(ROW(A1),$E$1:$G$2,2,TRUE)&"a"&ROW()-VLOOKUP(ROW(A1),$E$1:$G$2,3,TRUE)),"")
3) display row number if value in col.A is unique
in cell B2
=IF(AND(A2<>"",COUNTIF($A$1:A1,A1)=1),ROW(),"")
4) extract unique ID
in cell C2
=IF(ROW(A1)-1<COUNT(B:B),INDEX(A:A,SMALL(B:B,ROW(A1)),1),"")
then select A2:C2 and drag down as you desire
copy entire columnC and pastespecial/value
jindon
Hi,
OK. what you got is exactly the same result as
in cell F23
=if(E12="","",E12)
simple as above!
=INDIRECT("E"&ROW()-12)
also returns the same value
Check excel help carefully and it is easy to understand.
Good Luck!
jindon
By the way, here's VBA code
Paste code in sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
with target
if (.column<>5)+(.count<>1) then exit sub
.offset(12,1).value=.value
end with
end sub
Hi Garcia,
Would you like to upload smaple file?
By formula
data in Col.A and sorted ascending order before insert formula
B1:
=IF(ISERROR(--LEFT(A1,COUNT(-LEFT(A1,COLUMN($1:$16))))),ROW()+9^10,--LEFT(A1,COUNT(-LEFT(A1,COLUMN($1:$16)))))
holding down Ctrl+Shift keys and Enter to get out of the formula bar
then drag down
then sort by Col.B
will give you what you desire, I hope
Jindon
If the data begin from cell A1
B1:
=IF(COUNTIF(A$1:A1,A1)=1,ROW(),"")
then drag down to the last row of the data in col.A
C1: displays unique records
=IF(ROW(A1)<COUNT(B:B),INDEX(A:A,SMALL(B:B,ROW(A1)),1),"")
then drag down
copy entire columnC and paste special/values
Jindon
Here's vba codes
Paste all the code onto sheet module
'-----------------
Public lastA As Long
Private Sub Worksheet_Activate()
Dim i As Long
Application.EnableEvents = False
With Range("n1")
.FormulaArray = "=MAX(IF((A1:A65535<>"""")*(A1:A65535<>0),ROW(A1:A65535)))"
.Font.ColorIndex = 2
End With
lastA = Range("n1").Value
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate()
Dim lastM As Long, lastAA As Long, i As Long
Application.EnableEvents = False
If Range("n1").Value = 0 Then GoTo e
lastAA = Range("a65536").End(xlUp).Row
For i = lastAA To 1 Step -1
If (Cells(i, 1).Value <> 0) * (Cells(i, 1).Value <> "") Then
lastAA = Cells(i, 1).Row
Exit For
End If
Next
Select Case lastAA
Case Is = lastA
If IsEmpty(Cells(1, 13)) = True Then
Cells(1, 13).Value = _
Range("A" & lastAA).Value
GoTo e
Else
lastM = Range("M65536").End(xlUp).Row
Cells(lastM, 13).Value = _
Range("A" & lastAA).Value
GoTo e
End If
Case Is > lastA
If IsEmpty(Cells(1, 13)) = True Then
Cells(1, 13).Value = _
Range("A" & lastAA).Value
GoTo e
Else
lastM = Range("M65536").End(xlUp).Row
Cells(lastM + 1, 13).Value = _
Range("A" & lastAA).Value
GoTo e
End If
Case Else
GoTo e
End Select
e:
lastA = Range("n1").Value
Application.EnableEvents = True
Exit Sub
Application.EnableEvents = True
End Sub
Hi, Garcia
If that a case, data may be "TEXT".
can you check inserting following formula
=istext(b194)
it it returns TRUE then it is text, therefore cannot be calculated.
I mean it must be converted to the form to be able to calculated.
Regards,
Jindon
Hi,
Try
Sub tab_name()
Dim ws As Worksheet
For Each ws In Sheets
ws.Name = ws.Range("d3").Value
Next
End Sub
Hi,
in cell F25
=IF(OFFSET(F25,-12,-1)="","",OFFSET(F25,-12,-1))
and drag down as you want
hope it works for you
Hi,
=IF(MOD(ROW(A1),4)=1,B6-B5+IF(B6<B5,1))
I guess you want
=IF(AND(B5<>"",MOD(ROW(A1),4)=1),B6-B5+IF(B6<B5,1)),"")
jindon
If cell A1 of sheet1 has a validation dropdown, then
try
cell in sheet2
=IF(SHEET1!A1="","",LOOKUP(Sheet1!A1,{"AT","BT","MT";"After Taxes","Before Taxes","Monthly Taxes"}))
jindon
Solution by formula
if original data begins from A1 and sorted then
B1:displays duplicates
=INDEX($A$1:$A$200,SMALL(IF(COUNTIF($A$1:$A$200,$A$1:$A$200)>1,ROW($A$1:$A$200)),ROW(A1)),1)
Ctl+Shift+Enter to get out of the formula bar
C1:displays non duplicate
=INDEX($A$1:$A$200,SMALL(IF(COUNTIF($A$1:$A$200,$A$1:$A$200)=1,ROW($A$1:$A$200)),ROW(A1)),1)
Ctl+Shift+Enter to get out of the formula bar
then drag down both cells
jindon