Posts by t33p33

BTW. I am currently using the below formula and it returns the right value in column B, but for the life of me, nesting this in a VLOOKUP is not working to get the value in column A.

=SMALL($B$2:$B$14,COUNTIF($B$2:$B$14,"<"&B5)+1)

• In Cell B5 the user enters a dollar amount
• Cell B6 returns a value from a table
• The table contains dollar amounts in column B and a simple number in column A

Goal is to enter a dollar amount, and then find the dollar amount in the table is equal to or next highest and return the value in the adjacent cell in column A

Example: If user enters 23000 in B5, B6 will return 2

B5 - Input Dollar Amount

B6 - Return Tier Number from Table Below

A B

1 15000

2 25000

3 500000

4 100000

5 150000

Re: Find a value in a row and column, return intersecting cell value

Any by the way, it works off a selected cell, because I actually put a static cell into the formula. The static cell contains the formula that actually selects which cell across the top I am looking for, allowing me to enter a single cell into the formula and bypass what you say is impossible entirely. You are correct. Not possible if you look at it straight on, but what I was asking for was help with both the creativity and formula. You were not helping in either. If you intend to help, don't just drop a note about something being impossible. Instead learn Excel, learn creating thinking about how to solve the problems and offer real solutions. It is amazing how those types of things are seen then as help.

Re: Based on selected call, return list all values in A:A and column of selected cell

Yes genius. I am well aware of time stamping. The reason that was changed is because as I continued to work on this I was halfway through solving it and updated the post so that anyone that actually knew what they were doing would be providing an answer to my question at the time, rather than the entire solution. The solution, which by the way, I figured out today and have working. Go pat yourself on the back and troll someone else, or learn Excel and actually offer some help.

Re: Based on selected call, return list all values in A:A and column of selected cell

Quote from skywriter;775614

I'm not sure how you would get a formula to work based on a selected cell.
I'm not a formula expert, but I would say that's probably not possible.
You are probably going to need code to do this.

You are not a formula expert, but respond to a post as if you know the answer, and that it isn't possible? What is the point?

This is possible. I have had a formula do this in the past. Actually multiple formulas that I had to copy down, which I am OK with. If I hadn't lost the dang spreadsheet that I had this working in before I would be in great shape but . . .

I have a spreadsheet where I need to find a call value that is the intersection of a date and a name

On Sheet 1 - Row 2 contain the cells with dates in them; customer formatted as d, do only show the day.
Column A contains names.

In a cell, on another sheet (Sheet2), I have a cell (A1) with the value of =today() to return today's date. It also contains the list of same names (starting in A2 to A182)

What I am looking to do is create a formula on Sheet 2 in column B, that takes the name in the same row in column A, and the date in cell A1, and finds the value in the cell that is the intersection of those on Sheet 1.

For example using the below table, the formula would look for Name 4 and January 14 and return "Birch"

[TABLE="width: 500"]

[TABLE="width: 864"]

[TD="class: xl92, width: 64, bgcolor: transparent"]

[TD="class: xl78, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]E
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]F
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]G
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]H
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]I
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]J
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]K
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]L
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]M
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]N
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]O
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]P
[/TD]
[TD="class: xl78, width: 64, bgcolor: transparent"]Q
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl95, bgcolor: white"][/TD]
[TD="class: xl93, bgcolor: #92CDDC, colspan: 16"]January
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl96, bgcolor: white"][/TD]
[TD="class: xl91, bgcolor: yellow"]1
[/TD]
[TD="class: xl90, bgcolor: yellow"]2
[/TD]
[TD="class: xl90, bgcolor: yellow"]3
[/TD]
[TD="class: xl90, bgcolor: yellow"]6
[/TD]
[TD="class: xl91, bgcolor: yellow"]7
[/TD]
[TD="class: xl90, bgcolor: yellow"]8
[/TD]
[TD="class: xl90, bgcolor: yellow"]9
[/TD]
[TD="class: xl90, bgcolor: yellow"]10
[/TD]
[TD="class: xl90, bgcolor: yellow"]13
[/TD]
[TD="class: xl90, bgcolor: yellow"]14
[/TD]
[TD="class: xl90, bgcolor: yellow"]15
[/TD]
[TD="class: xl90, bgcolor: yellow"]16
[/TD]
[TD="class: xl90, bgcolor: yellow"]17
[/TD]
[TD="class: xl90, bgcolor: yellow"]20
[/TD]
[TD="class: xl90, bgcolor: yellow"]21
[/TD]
[TD="class: xl90, bgcolor: yellow"]22
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]Name 1
[/TD]
[TD="class: xl83, width: 64, bgcolor: navy"][/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl82, width: 64, bgcolor: fuchsia"]sunset 11-1
[/TD]
[TD="class: xl82, width: 64, bgcolor: fuchsia"]sunset 11-2
[/TD]
[TD="class: xl86, bgcolor: fuchsia"] sunrise
[/TD]
[TD="class: xl84, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]trio
[/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl76, width: 64, bgcolor: transparent"][/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]Name 2
[/TD]
[TD="class: xl83, width: 64, bgcolor: navy"][/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]etwo
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]kick
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]kick
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]kick
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]kick
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]kick
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]kick
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]tent kick
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin nb
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]Name 3
[/TD]
[TD="class: xl83, width: 64, bgcolor: navy"][/TD]
[TD="class: xl85, bgcolor: yellow"]Nina
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"] trans 10-2 pm/
[/TD]
[TD="class: xl85, bgcolor: yellow"]trans
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl85, bgcolor: yellow"]Nina / trans
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]hood
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]Name 4
[/TD]
[TD="class: xl83, width: 64, bgcolor: navy"][/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]lieu
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]Birch
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]Birch
[/TD]
[TD="class: xl79, width: 64, bgcolor: lime"]Birch
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]lieu
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]lieu
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]maritime
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]Name 5
[/TD]
[TD="class: xl83, width: 64, bgcolor: navy"][/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
[/TD]
[TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]tent blink
[/TD]
[TD="class: xl89, width: 64, bgcolor: #339966"]moby
[/TD]
[TD="class: xl89, width: 64, bgcolor: #339966"]moby
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]Name 6
[/TD]
[TD="class: xl83, width: 64, bgcolor: navy"][/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
[/TD]
[TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]tent blink
[/TD]
[TD="class: xl89, width: 64, bgcolor: #339966"]moby
[/TD]
[TD="class: xl89, width: 64, bgcolor: #339966"]moby
[/TD]

[TD="class: xl92, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]Name 7
[/TD]
[TD="class: xl83, width: 64, bgcolor: navy"][/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]vacation
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl81, width: 64, bgcolor: gray"]admin launch nb
[/TD]
[TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
[/TD]
[TD="class: xl87, width: 64, bgcolor: #0066FF"]Bell
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl77, width: 64, bgcolor: yellow"]Media
[/TD]
[TD="class: xl88, width: 64, bgcolor: #B1A0C7"]tent blink
[/TD]
[TD="class: xl89, width: 64, bgcolor: #339966"]moby
[/TD]
[TD="class: xl89, width: 64, bgcolor: #339966"]moby
[/TD]

[/TABLE]
[/TD]

[/TABLE]

I've thought of using some combo of index/match and maybe vlookup, but hitting a wall.

I have two sheets in Excel. The first sheet contains a table of exported time sheet data.

On the time sheet data sheet I have a column with the time type and another column with a numeric value representing the hourly rate. There will be duplicate values in the first column. The numeric column will have the same value for each time type, or it may have a 0. An example is below:
[TABLE="width: 500"]

Time Entry Type

Rate

Standard Time

100

Standard Time

100

Premium Time

150

Standard Time

100

Premium Time

150

Standard Time

0

Standard Time

0

Premium Time

0

[/TABLE]

On the second sheet there is a table with the unique values found in the Time Type column on the first sheet. There are no duplicates.
[TABLE="width: 500"]

Time Entry Type

Rate

Standard Time

Premium Time

[/TABLE]

I would like to create a formula, or a macro, that looks for the Time Entry Type from the second sheet, find it in the first sheet, and return the non-zero value in the rate column. In the above example this would mean looking for Standard Time and returning the rate of 100 in the Rate column on sheet two.

I am completely stuck. I can't even offer something to get started. Any help you can offer would be greatly appreciated!

I have a spreadsheet with multiple columns of information. On another sheet I would like to automatically see a filtered view of all rows that have a matching date in column H.

I have attached a sample file of the data.

I am really struggling on this one. Any help would be greatly appreciated!

Re: Macro - Filter Pivot Table multiple does not contain

Also speed it up by turning off manual updates

Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")


With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End with


ActiveSheet.PivotTables(1).ManualUpdate = True


With PTitle


    .ClearAllFilters
    For i = 1 To .PivotItems.Count
        If InStr(1, .PivotItems(i), "Hosting", vbTextCompare) > 0 _
        Or InStr(1, .PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
        Then
            PTitle.PivotItems(i).Visible = False
        End If
    Next i
End With


With Application
    .ScreenUpdating = true
    .Calculation = xlCalculationAutomatic
End with


ActiveSheet.PivotTables(1).ManualUpdate = false
End Sub

Re: Macro - Filter Pivot Table multiple does not contain

Solved! No need for Power pivot and definitely possible. If anyone else out there would like to have multiple lable filters here you go:

Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
Application.ScreenUpdating = False
With PTitle
    .ClearAllFilters
    For i = 1 To .PivotItems.Count
        If InStr(1, .PivotItems(i), "Hosting", vbTextCompare) > 0 _
        Or InStr(1, .PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
        Then
            PTitle.PivotItems(i).Visible = False
        End If
    Next i
End With
End Sub

Re: Macro - Filter Pivot Table multiple does not contain

Herbsds7, Thanks for the input.

I now have a modified script as follows, but seem to be erroring out on the line

[PTitle.PivotItems(i).Visible = False/CODE]


[CODE]
Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
Application.ScreenUpdating = False
With PTitle
    .ClearAllFilters
    For i = 1 To PTitle.PivotItems.Count
        If InStr(1, PTitle.PivotItems(i), "Hosting", vbTextCompare) > 0 _
        Or InStr(1, PTitle.PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
        Then
            PTitle.PivotItems(i).Visible = True
        Else
            PTitle.PivotItems(i).Visible = False
        End If
    Next i
End With
End Sub

Re: Macro - Filter Pivot Table multiple does not contain

Also tried this with the same issue of only filtering out the first value and then erroring out.

Sub Filter()
ActiveSheet.PivotTables("PivotTable3").PivotFields("Project Title"). _
PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:= _
"Hosting"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Project Title"). _
PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:= _
"Infrastructure"
End Sub

I am trying to write a macro that will filter a pivot table. I want to filter out anything that contains one of two values; "Hosting", and "Infrastructure". I am getting an error on the following macro. It will only filter out the first value. I have tried multipe versions and cannot seem to find a way to filter out anythign containing two different values. Any assistance would be appreciated.

Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
With PTitle
.ClearAllFilters
.PivotFilters.Add xlCaptionContains, , "Hosting"
.PivotFilters.Add xlCaptionContains, , "Infrastructure"
End With
End Sub

I error out when I get to the second filter. I can get this to work with one filter, but not to filter out two different values.

Cell A2 has the formula =today()

Cells AE2 hasthe formula =EDATE(A2,1)
AF2 has the formula =EDATE(A2,2)
AG2 has the formula =EDATE(A2,3)
ETC.,

Column I contains dates that projects will be completed. The dates are formatted: mmm yyyy

I have other cells that will calculate the number of months between two dates using the formula =MONTH(I9-AE2)

This formula works, unless the months are the same in column I and the column, AE, AF, AG, etc.
If I9 contains the date Dec 2011 and AE2 contains December 2011 (or any other months that are the same) it will result in a #NUM error.

If I manually type in the dates without using a formula in the AE, AF, AG, etc columns it wil work OK. If the dates are from different months the formula will also work.

Any ideas on what is going on here and how to get this to work without the error?