Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Drop Down/ Autopopulate
Hey!
I have a drop down menu with certain values (in my case, one of the drop downs is titled "Billable"). I am trying to figure out how I can create a formula which searches for all items labeled "Billable" and then pulls a range of data associated with that label and populates only that data into a new worksheet. Do you have any advice on how I can do this? Thank you so much for your help, Philip |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Drop Down/ Autopopulate
How about just using
datafilterautofilterfilter on "billable"copy/paste. Record a macro if often. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip" wrote in message ... Hey! I have a drop down menu with certain values (in my case, one of the drop downs is titled "Billable"). I am trying to figure out how I can create a formula which searches for all items labeled "Billable" and then pulls a range of data associated with that label and populates only that data into a new worksheet. Do you have any advice on how I can do this? Thank you so much for your help, Philip |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Drop Down/ Autopopulate
I'm sorry, the autofilter for billable doesn't truly perform the function
that I am looking for. Thank you for your help. Philip "Don Guillett" wrote: How about just using datafilterautofilterfilter on "billable"copy/paste. Record a macro if often. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip" wrote in message ... Hey! I have a drop down menu with certain values (in my case, one of the drop downs is titled "Billable"). I am trying to figure out how I can create a formula which searches for all items labeled "Billable" and then pulls a range of data associated with that label and populates only that data into a new worksheet. Do you have any advice on how I can do this? Thank you so much for your help, Philip |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Drop Down/ Autopopulate
Both Autofilter or a loop will work.
Sub useautofilter() 'SalesAidSoftware Dim lr As Long Dim mc As String Application.ScreenUpdating = False mc = "U" lr = Cells(Rows.Count, mc).End(xlUp).Row Range("a2:x" & lr).AutoFilter Field:=21, Criteria1:="Billable" Range("a3:x" & lr).Copy Sheets("Tab1 Destination").Range("a3").PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone Range("a2:x" & lr).AutoFilter Application.ScreenUpdating = True End Sub Sub copybillable() 'SalesAid Software Dim mc As String Dim i, lr, dlr As Long mc = "u" lr = Cells(Rows.Count, mc).End(xlUp).Row For i = 2 To lr With Sheets("Tab1 Destination") dlr = .Cells(Rows.Count, mc).End(xlUp).Row + 1 If UCase(Cells(i, mc)) = "BILLABLE" Then .Rows(dlr).Value = Rows(i).Value End If End With Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your workbook to my address below with these msgs and a complete explanation. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip" wrote in message ... I'm sorry, the autofilter for billable doesn't truly perform the function that I am looking for. Thank you for your help. Philip "Don Guillett" wrote: How about just using datafilterautofilterfilter on "billable"copy/paste. Record a macro if often. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip" wrote in message ... Hey! I have a drop down menu with certain values (in my case, one of the drop downs is titled "Billable"). I am trying to figure out how I can create a formula which searches for all items labeled "Billable" and then pulls a range of data associated with that label and populates only that data into a new worksheet. Do you have any advice on how I can do this? Thank you so much for your help, Philip |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Drop Down/ Autopopulate
Actually, I like this even better
Sub useautofilter() 'SalesAidSoftware Dim lr As Long Dim mc As String Application.ScreenUpdating = False mc = "U" lr = Cells(Rows.Count, mc).End(xlUp).Row With Range("a2:x" & lr) ..AutoFilter Field:=21, Criteria1:="Billable" ..Offset(1).Copy Sheets("Tab1 Destination").Range("a3").PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone Application.CutCopyMode = False ..AutoFilter End With Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Both Autofilter or a loop will work. Sub useautofilter() 'SalesAidSoftware Dim lr As Long Dim mc As String Application.ScreenUpdating = False mc = "U" lr = Cells(Rows.Count, mc).End(xlUp).Row Range("a2:x" & lr).AutoFilter Field:=21, Criteria1:="Billable" Range("a3:x" & lr).Copy Sheets("Tab1 Destination").Range("a3").PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone Range("a2:x" & lr).AutoFilter Application.ScreenUpdating = True End Sub Sub copybillable() 'SalesAid Software Dim mc As String Dim i, lr, dlr As Long mc = "u" lr = Cells(Rows.Count, mc).End(xlUp).Row For i = 2 To lr With Sheets("Tab1 Destination") dlr = .Cells(Rows.Count, mc).End(xlUp).Row + 1 If UCase(Cells(i, mc)) = "BILLABLE" Then .Rows(dlr).Value = Rows(i).Value End If End With Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your workbook to my address below with these msgs and a complete explanation. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip" wrote in message ... I'm sorry, the autofilter for billable doesn't truly perform the function that I am looking for. Thank you for your help. Philip "Don Guillett" wrote: How about just using datafilterautofilterfilter on "billable"copy/paste. Record a macro if often. -- Don Guillett Microsoft MVP Excel SalesAid Software "Philip" wrote in message ... Hey! I have a drop down menu with certain values (in my case, one of the drop downs is titled "Billable"). I am trying to figure out how I can create a formula which searches for all items labeled "Billable" and then pulls a range of data associated with that label and populates only that data into a new worksheet. Do you have any advice on how I can do this? Thank you so much for your help, Philip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel to autopopulate color.. | Excel Discussion (Misc queries) | |||
Drop down menu with emp name then autopopulate with supervisor... | Excel Discussion (Misc queries) | |||
Autopopulate a drop down list in Excel | Excel Discussion (Misc queries) | |||
Help with Excel - autopopulate a long table based on infor from an | Excel Discussion (Misc queries) | |||
Autopopulate with zero | Excel Worksheet Functions |