Search results

Hi, Try this approach. In the sample file there are 2 defined names and you can set the data validation by userform. 0. step - Press the SHOW USERFORM button 1. step - Select the Defined Name in the combobox 2. step - Set the range of data validaton in textboxes (i.e. from C1 to C2) 3. step -...

Hi, I attached a sample file. You can find in column A some fruits, I created a defined Name by Name Manager(called: Listofculinaryfruits) with the following formula that will give you a dynamic list with the exact count: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). If you want to add more...

Hi, Is this formula what you need: =COUNTIFS(C:C,">1",B:B,"<>"&I7)?

Hi, Create a name by Name Manager and put the following formula in the Refers to box: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). And use this name as data validation source.

Hi, Another approach.

Hi, One more proposal.

Hi, Could you please describe it in details?

Hi, Check this is Ok for you!

Hi, Check this proposal.

Hi, Is this chart Ok for you?

Hi, Maybe this is that formula what you are looking for? =IF(AND(OR(D40="Open",D40="Monitoring"),VALUE(M40)<=VALUE(TODAY()-5)),"Y","")

Hi, Is this VBA proposal help for you? Sub CopyPasteFilteredData() Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Sheet2").Range("A1") End Sub

Hi, Check out this approach.

use the code in my first post, Schade1/2 "jump" to A1 if you are in O9 or S9 (or out of the lists)

or if you click in O9 or S9 the Schade1 or Schade2 "jump" to A1

maybe try this... if you click in cell M2 or M3 the "Schade2" is invisible Private Sub Worksheet_SelectionChange(ByVal Target As Range) Check1: With ActiveSheet.Shapes.Range(Array("Schade1")) If Not Intersect(Target, Range("O2:O20")) Is Nothing Then .Visible = True...

Hi, Is this what you are looking for? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Check1: With ActiveSheet.Shapes.Range(Array("Schade1")) If Not Intersect(Target, Range("O2:O20")) Is Nothing Then .Visible = True .Left = ActiveCell.Offset(0, 1)...

Or this: =LEFT(B3,FIND(" ",B3&" ")-1)

Hi, Is it OK for you?

Hi, Check out this INDEX-MATCH array proposal.

Hi, Is it help for you? I used INDEX-MATCH instead of VLOOKUP because faster and more flexible. Option Explicit Sub FindValue() Dim Lastrow As Long Dim SourceSheet As Worksheet Dim SourceLastrow As Long Set SourceSheet = Worksheets("Data") With SourceSheet SourceLastrow =...

I assume you will have issue with the numbers which are stored as text try this: Option Explicit Sub ReplacePound() Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).Select Selection.Replace What:="##", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False...

if you would like zero (0) as replacement then use this: Selection.Replace What:="##", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False if you would like to change the range then modify the last column of selected...

another approach with COUNTIFS and MAX functions

Hi, Try this macro: Option Explicit Sub ReplacePound() Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row).Select Selection.Replace What:="##", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False...