trying to generate a dynamic drop down list

boi-alien

New Member
Joined
Oct 6, 2006
Messages
5
I currently have a worksheet that contains a large amount of data with account titles and total charges to each account. What I've defined certain threshold levels that the user can use to generate a dynamic drop down list.

I'm trying to read through the spreadsheet and return the appropriate account if the threshold is met.

I have

=IF('GP 2004-2006'!AO3>Charts!$E$39,'GP 2004-2006'!D3&" "&'GP 2004-2006'!E3,"none")

Where Charts!$E$39 is the threshold value. I'm using this same formula down the range of total values, as you can imagine, I'm getting a lot of account numbers as well as the word none

What I really want is to somehow search through the totals and return all the values that are greater than the user defined threshold.

I've never done any VB or macro work before. Is it possible by using pure Excel without macros or VB to accomplish this? Or will I have to create a macro for something like this?

Any help would be greatly appreciated.

Thanks,

Yun
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
One way without code is to add a Auto-Filter to the column.
Select the column label
Excel Toolbar: Data - Filter - Auto Filter
Click the Data Column Label dropdown and select "Custom"
Then select Greater Then or whatever and the value to use.
To restore all the data, from the Label DropDown select "All"
 

boi-alien

New Member
Joined
Oct 6, 2006
Messages
5
Unfortunately the users aren't very excel savvy, I don't want them to have to go in and do an auto-filter. Is there a way to automate it so the user never has to touch anything other than selecting their threshold value?

Thanks for the suggestion though.

Hi Joe,

Sorry I'm somewhat of a VB newbie so bear with me

The cells where I want the filter applied would be column AO
The data that I want returned would be in columns D and E.

Would it be possible for this macro rather than just doing a simple filter to generate a list and print this list to a different set of cells?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
For a code solution we need to know which cell will get the filter amount and what Range has the data and if you want the code to be automatic or to only work with a hot-key or button. Also if and when you want the full data view restored?


Sub hideRBCells()
Dim myLastR&, BRng As Variant

myLastR = Range("B65536").End(xlUp).Offset(1, 0).Row

Set BRng = Range(Cells(1, 2), Cells(myLastR, 2))

Application.ScreenUpdating = False

For Each Cell In BRng
If Cell.Value = "" Then Cell.EntireRow.Hidden = True
Next Cell

Application.ScreenUpdating = True
End Sub

Sub myShowRB()
Columns("B:B").EntireRow.Hidden = False
End Sub
 

boi-alien

New Member
Joined
Oct 6, 2006
Messages
5

ADVERTISEMENT

Hi Joe,

Sorry I'm somewhat of a VB newbie so bear with me

The cells where I want the filter applied would be column AO
The data that I want returned would be in columns D and E.

Would it be possible for this macro rather than just doing a simple filter to generate a list and print this list to a different set of cells?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Just as easy.
Once again where would the cell that gets the user threshold be located?
We can make it so the code puts up a box to get the data or make it check for a value change in a trigger cell somewhere on the sheet.

The new data range of values will need to go to some range, we need to know where you want this new list to be as well?
 

boi-alien

New Member
Joined
Oct 6, 2006
Messages
5

ADVERTISEMENT

The user threshold would be located in cell E39

I would like the new list to start at cell J2

Again pardon my lack of knowledge, do I just put the sheet into view code mode and paste the code you've provided to me?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
OK, this code should go in the Sheet Module for the Sheet named: Charts

Right-click its Sheet Tab and select View-Code and paste a copy of this in the Editor window. Hit the Upper most Right Close "X" to return to the sheet.

This code will automatically, upon a new value being added to Charts cell: E39, Search the data in AO3 to the bottom of its data on Sheet: "GP 2004-2006" for any value Greater Than Or Equal to this test value. If True then the values in Columns D & E of the GP 2004-2006 are pasted to a list in Column "J" from J2 down.

After this list is made it is named with a Range Name = to: myResults

This way you can use this new list in any formula by its Range Name!


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet: "Charts"
Dim myVal$
Dim myLow&, myBot&, myLstR&
Dim cell As Object
Dim myRng As Range, myResults As Range

If Target.Address <> "$E$39" Then Exit Sub
If Target.Count > 1 Then Exit Sub

Sheets("Charts").Select
'Get test value!
myLow = Sheets("Charts").Range("E39").Value

Sheets("GP 2004-2006").Select
Sheets("GP 2004-2006").Range("J2:J65536").ClearContents

myBot = Sheets("GP 2004-2006").Range("AO65536").End(xlUp).Row

'Start in Cell AO3 looking for values!
Set myRng = Sheets("GP 2004-2006").Range("AO3:AO" & myBot)

For Each cell In myRng
'Test for filter value and make new list!
If cell.Value >= myLow Then
myVal = cell.Offset(0, -37).Value & " " & cell.Offset(0, -36).Value

If Sheets("GP 2004-2006").Range("J2").Value = "" Then
Sheets("GP 2004-2006").Range("J2").Value = myVal
Else
Sheets("GP 2004-2006").Range("J65536").End(xlUp).Offset(1, 0).Value = myVal
End If
End If
Next cell

'Get new list!
myLstR = Sheets("GP 2004-2006").Range("J65536").End(xlUp).Row

If myLstR < 2 Then myLstR = 2

Set myResults = Sheets("GP 2004-2006").Range("J2:J" & myLstR)

'In any Formula or Range Reference you can refer to the filtered list by
'the name: myResults

On Error GoTo myErr
ActiveWorkbook.Names("myResults").Delete

myErr:
ActiveWorkbook.Names.Add Name:="myResults", RefersTo:=myResults
End Sub
 

boi-alien

New Member
Joined
Oct 6, 2006
Messages
5
thank you so much joe, I'm going to test this tomorrow, I really appreciate all your help.
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top