Posts by Carim

Re: ADD picture

Hello,

Glad you could fix your problem ...

Thanks ... for your thanks :smile:

Re: Tracking overtime in excel

Hello Sarah,

When it comes to displaying negative times ... just change the date system used by your workbook .. to the 1904 date system in the Excel Options Advanced menu ...

HTH

Re: ADD picture

Hello,

Your sheet Photos can hold as many pictures as you want ... as long as you give them a name starting with the word Photo ...

Regarding the code itself, you can test the following ... with your own adjustments ...

Sub AddPic()
    Const FName As String = "C:\Documents and Settings\My Documents\My Pictures\Homard.jpg"
    Dim Image As OLEObject
    Set Image = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Image.1", Link:=False, _
        DisplayAsIcon:=False, Left:=12.75, Top:=183, Width:=39, Height:=35.25)
    Image.Object.Picture = LoadPicture(FName)
End Sub

HTH

Re: ADD picture

Hello,

It looks like this file from Boisgontier is already working fine ...

Do you need to modify it ?

Re: SUM Specific Date with Different Sheet

Quote from great.liberty;760630

wow... it works just the way I want it.
Thank you very much Carim.

Hi,

Glad you could fix your problem ...

Thanks a lot ... for your thanks :smile:

Re: Identifying a unique value based on specific arguments

Hi,

Have you had a go with a pivot table ...?

Re: SUM Specific Date with Different Sheet

Hello,

You could try following ...

=SUM(INDIRECT("Sheet1!"&TEXT(ADDRESS(ROW(Sheet1!A7),MATCH(Sheet1!$BX$5,Sheet1!$E$6:$BS$6,0)+4),0)):INDIRECT("Sheet1!"&TEXT(ADDRESS(ROW(Sheet1!A7),MATCH(Sheet1!$BY$5,Sheet1!$E$6:$BS$6,0)+4),0)))

HTH

Re: Autoloop through the auto filter copy and paste each result....

Glad it is helping you out ... :smile:

Indeed, you can tweak it to save individual workbooks ...

Thanks ... for your thanks ...

Re: if a match then bring back data

Hi,

In cell C2 ... you could test following

=INDEX('Product Info'!$A$2:$F$4,MATCH(A2,'Product Info'!$A$2:$A$4,0),MATCH(B2,'Product Info'!$A$1:$F$1,0))

HTH

Re: Autoloop through the auto filter copy and paste each result....

Hi,

As an initial step, you can use following code ... to explode your data ...

Sub ExplodeData()
Dim lr As Long
Dim ws As Worksheet
Dim vcol As Integer
Dim i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                Credit : Kutools for Excel                   '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Adjust the next three instructions to your situation :      '
'     vcol is the Column Number to be used                    '
'     ws should show the name of your Data worksheet          '
'     title should show the columns from your Data worksheet  '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


vcol = 1
Set ws = Sheets("Data")
title = "A1:H1"


Application.ScreenUpdating = False
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"


For i = 2 To lr
On Error Resume Next
  If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
  ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
  End If
Next i
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
  For i = 2 To UBound(myarr)
    ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
    If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
      Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
    Else
      Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
    End If
      ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
      Sheets(myarr(i) & "").Columns.AutoFit
  Next i
ws.AutoFilterMode = False
ws.Activate
End Sub

HTH

Re: conditional formatting

Quote from hemanth babu;759800

thank you.....
issue solved....

Glad you could fix your problem ...

Thanks ... for your thanks :wink:

Re: conditional formatting

Debra has written a step-by-step explanation ...

http://blog.contextures.com/ar…ing-from-different-sheet/

HTH

Re: conditional formatting

Quote from hemanth babu;759794

" you cannot use references to other worksheet or workbooks for conditional formatting criteria ".

Hi,

To overcome this ... you only need to create a name for the range ... and use the name in your criteria formula ...

HTH

Re: conditional formatting

Hi,

Based on your sample workbook, could you explain precisely what you are after ... :smile:

Re: Sumif and Offset

Hello,

You can have the following formula in cell O170

=SUMIF('Sheet 2'!$B$11:$B$244,Sheet1!B170,OFFSET('Sheet 2'!$D$11:$D$244,4,0))

Then, you can copy this formula all the way down till your last row O453

HTH

Re: Conditional Formatting multiple and or criteria

Quote from Lorrie3292;756253

Thank you Carim! I never could have figured this out without you! Most appreciated and it works great!

Hello Lorrie,

Quite pleased you could solve your problem ...:smile:

Thanks .... for your thanks ...:wink:

Re: Sumif and Offset

Hello MS.X,

Difficult to guess the structure of your Excel file ...

Would you mind attaching your workbook ...:wink:

Re: Formula to find the last cell having number as 1

Quote from jainny143;756332

THANK YOU. it is working with few modification

Thanks ... for your thanks ... :smile:

Re: Formula to find the last cell having number as 1

Hello,

Not totally sure to understand your latest request ...

Attached is a proposal to get the Max from the High Values (column C) within the range defined by the two formulas in cells N1 and S1 ...

HTH

Re: Formula to find the last cell having number as 1

Quote from jainny143;756026

hello,

yeah its working great and thank you for helping me.

Glad you could fix your problem ...

Thanks ... for your thanks ...:wink: