Posts by Oeldere

Re: Nested IFs alternative for Excel 2003

Make a table in your sheet with the values.

After that use VLookup to find this values in the created table.

Re: Automatically create hyperlink to file without knowing extension

crossposthttp://"http://www.excelforum.com/excel-programming-vba-macros/902028-automatically-hyperlink-to-file-without-knowing-extension.html"

http://www.excelforum.com/excel-programming-vba-macros/902028-automatically-hyperlink-to-file-without-knowing-extension.html

Re: Formula to calculate amounts from column if the same text matches between 2 sheet

If you have the data listed in an horizontal way, you have to use Hlookup instead of VLookup.

=Vlookup(M33;'Currency Calculator'!1:1048576;M,0)*N33

Your code above need to be changed (if you have your data in a vertical way) in something like below.

See the red text.

=Vlookup(M33;'Currency Calculator'![COLOR=#ff0000]A[/COLOR]1:[COLOR=#ff0000]M[/COLOR]1048576;[COLOR=#ff0000]13[/COLOR],0)*N33


I don't see the red text in my post so i posted it without color.
[CODE]=Vlookup(M33;'Currency Calculator'!A1:M1048576;13,0)*N33

Re: Formula to calculate amounts from column if the same text matches between 2 sheet

try this one

N1 = Vlookup(M1;sheet2!A1:b20;2;0)*L1

in which

M1 = USD

Sheet2!a1:b20 is the table with the currency in it (and in column B the value to count with)

2 is the second column

0 is the value for exact math (1 is the other option).

Re: highlighting

Sub color()
  Dim ar       As Range
  With Sheets("Declaratie resultaat")
    .AutoFilterMode = False
    With .Range("a1").CurrentRegion
      .Offset(0, 0).Resize(, .Columns.Count + 11).Interior.ColorIndex = 0
      .Offset(0).Resize(.Rows.Count - 1).Columns(4).Interior.ColorIndex = 0
      .AutoFilter 4, "*" & "cable tie"                                                   'this name gives the color
      If .Columns(4).SpecialCells(xlVisible).Count > 1 Then
        For Each ar In .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Areas
          ar.Offset(, -3).Resize(, 14).Interior.ColorIndex = 26
    Next
    End If
    End With
    .AutoFilterMode = False
  End With
End Sub

Re: Consolidation of multiple worksheets to one

Smallman

Thanks for the reply. There no ofference on my site though. :razz:

I will add in my footer that English is not my main language.

Re: Consolidation of multiple worksheets to one

Smallman

It's my solution. I made an extra column so i could use only that value (total) in a pivot table.

sylwester.cz

I have excel 2007 so I don't work with slicers.

I don't know if it's possible for excel 2010 or excel 2013.

Re: Consolidation of multiple worksheets to one

See the attached file.

I used the macro below to fill the consolidated file.

After that I made an pivot table.