Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Sum Multi Tier Selection

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have two multi selection lists, one dependent on the first. A few questions,

How do I filter the second list based on the multiple selections from the first list
How do I then loop through the multiple selections from the second list against the same column on another sheet and sum values

Example:
List One is Org Level 1, and should allow for multiple selection
05
06
10
18
etc..

List Two is Org Level 2 and there is a one to many relationship to Org Level 1 - again, multiple selections allowed here
Org 1 of 05 has Org Level 2 of 0510, 0520, 0540 etc..

I am trying to sum active employee count based on the selected Org Levels - this data looks like the following:

Org Level 1 Column A Org Level 2 Column B Headcount

05 0510 23
05 0520 5
05 0540 4
06 0603 18

And so on...

So I want to loop through the multiple selections made in the second list, and sum headcount

Many Many Thanks!!!!


Similar Excel Video Tutorials

Helpful Excel Macros

Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
- This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Filter Data to Show Only the Bottom 10 Items in Excel - AutoFilter
- This Excel macro filters a selection of data in order to display only the bottom 10 items in the list or data set. This
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe

Similar Topics







Hello,
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.

The data looks like this,
Requirements Qty Resources Qty
Level 1 Sentinel 10 Level 1 Sentinel 105
Level 2 Sentinel 10 Level 2 Sentinel 167
Level 3 Sentinel 10 Level 3 Sentinel 156
Level 1 Banga 20 Level 1 Banga 401
Level 2 Banga 20 Level 2 Banga 307
Level 4 Banga 20 Level 4 Banga 0

i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.

Any help is greatly appreciated.


Hi,

I would like to print a selection of variables based on selections from a multiple selection list box.

Can anyone help, im happy to use other methods if easier.

Many Thanks.

PS. Even if I can transfer the selected values in cells that would be great.


Quite difficult to explain... but here goes.

I have a worksheet with various data in. Two of the columns are headed Client and Level. Naturally I have the autofilter on so I can look at a specific client / level.

I have a macro that when played it copies the drop down menu for the client autofilter into column i have designated. This work fine. What I need however is a list of the levels associated with that client. Visualise it like this

Col1. Col2.
CLIENT LEVEL
EDF Level 1
EDF Level 3
AIM Level 1
AIM Level 2
EDF Level 2
TRL Level 4

The current macro I have to list the clients will there just re-produce the list i.e. EDF, AIM, TRL. Going down a column.

I now want a macro to list just all the levels for (lets say EDF). i.e. Level 1, Level 3, Level 2.

Any thoughts? Thanks,


I recently placed a dutch bet on betfair, on three horses and decided to trade out of the dutch on all selections, resulting in a profit/loss of two positives and one negative profit on each of the selections. Is there a formula I could use to level that profit out across all selections, to show a total profit (or loss) across all selections in the race - ie green up. This isn't as easy as it sounds, because every "level profit" bet made on each selection effects the profit on each and every selection, so any calculations, have to take into account that further bets will be required to achieve a level profit. Hopefuly one of you maths wizzes can solve this one.


Hi all, wondered if someone could point me in the right direction:

I need Excel to return a value based on a simple formula involving a pre-defined number, which was determined based on four user-selected variables.

That is, I have a list that has, for example, five main categories, each of which have five subcategories, and so on, to a depth of four. Ideally, the user would select one item from the first category from a drop-down list, which would result in the contents of that category being displayed in the next column, and so on, until four context-based selections have been made. Based on these four variables, Excel would select a pre-defined number and use it in a simple formula. For example:

The user selects A from a list containing A, B, C, D. Based on that selection, the user can now make a second selection. After four selections, we arrive at A, 4, Q, 9. Next, Excel looks at a pre-defined [by me] list to determine that the number attached to that specific combination of selections is 0.05. Finally, Excel runs a formula, say (0.05/2)*4, and returns the final value.

I realize this is a bit complex, but as I said, if someone could get me started on the correct approach, I'd be very grateful.

Regards,
Joe in Mpls


Hi,

I need to set up an excel sheet with dependent data validation that is multiple levels deep.

ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.

Is this possible?

Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.

Really appreciate any help you can provide.


Hi,

I need to set up an excel sheet with dependent data validation that is multiple levels deep.

ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.

Is this possible?

Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.

Really appreciate any help you can provide.


Is it possible to select multiple selections via a VBA macro? I know how to navigate through selections using a loop, however upon selecting the next selection, the previous selection disappears. Here is an example code I am using, I would like to select multiple selections offset by x rows, let's say 6:

Code:

'Starting Point
x = 1

Do Until ActiveCell = ""
Range(x & ":" & x).Select
x = x + 6
Loop


The above code works well, however how can I turn this into a multi-selection algorithm?


I'm looking for help in developing a formula in column 'A' that will number the task automatically according to the outline below. The diferent levels of tasks are in diferent columns, so I was hoping for a formula solution rather than having to maintain this manually. Thank you for any help you can offer on this.

A B C D E 1 5.1 Level 1 lask 2 5.1.1 Level 2 task 3 5.1.1.1 Level 3 task 4 5.1.1.2 Level 3 task 5 5.1.1.2.1 Level 4 task 6 5.1.1.2.2 Level 4 task 7 5.1.1.2.3 Level 4 task 8 5.1.2 Level 2 task 9 5.1.2.1 Level 3 task 10 5.1.2.2 Level 3 task 11 5.1.3 Level 2 task 12 5.1.3.1 Level 3 task 13 5.1.3.2 Level 3 task 14 5.1.4 Level 2 task 15 5.1.4.1 Level 3 task 16 5.1.4.2 Level 3 task 17 5.1.4.3 Level 3 task 18 5.1.4.4 Level 3 task 19 5.1.4.5 Level 3 task 20 5.1.4.6 Level 3 task 21 5.1.4.7 Level 3 task 22 5.2 Level 1 lask 23 5.2.1 Level 2 task 24 5.2.1.1 Level 3 task 25 5.2.1.1.1 Level 4 task 26 5.2.1.1.2 Level 4 task 27 5.2.1.1.3 Level 4 task 28 5.2.1.1.4 Level 4 task 29 5.2.1.2 Level 3 task


I'm setting up Dependent Lists with Data Validation, and I'm down to level 3 and just now discover that between level 2 and 3 - I need to work with numbers; meaning is at level 2 I have 920, then My dependent list should be 710,711,712; At level 2 if I have 972 then my dependent list should be 353,345,390.

At level 1 - My Data Lists are all text (am able to create named ranges = to text name -- in conjuction with the -INDIRECT(A1) reference.. (Got all this from Contextures.com web site)

Now I've hit a Brick Wall - I am unable (of course) to name a range 920 or 972.
Is there any type of "work-around" on this?


TIA,

Jim


Hey all...I'm trying to identify the Ranges of certain line items in an outline-like list so I can use the ranges formulas to roll-up data from lower levels to higher levels...and I just can't wrap my head around how to go about it...arrays? If..Then? Do...loops? Where the counters change? I'm doing circles...
For example:

PV Level
__RG Level
____RR Level
____RR Level

__RG Level
____RR Level

PV Level

I'm trying to roll up in 2 ways. First, RR Level data gets rolled up to its parent RG Level. Second, each RGL Level gets aggregated and rolled up to its parent PV level.

I'm currently tyring to move down the list vertically with a Do...Loop with the intent of capturing the ranges at each level as I move down and then write the appropriate formula to the parent level any time a line type switches from RR to RG or from RR to PV.

I can identify each line by its Type (PV, RG, RR, blank).
If it sounds confusing...its because I am

Any suggestions much appreciated...
Thanks,
-E


I have a multi-select listbox. For simplicity lets say that its populated with A thru Z plus 'Group'. Group appears first in the list and is supposed to be the combination of A, C, G, K, and T from the list. Basically Group is there because this combination of selections is very common so its easier for the user to select Group than all 5 selections each time.

I have a code (below) that will identify a selection, run a series of formulas based on that selection then move to the next selection. The code also can identify if 'Group' is selected.

Does anyone know what codes to use so that when Group is selected, the model will run as if A, C, G, K, and T where selected in addition to the rest of the listbox selections?

Hope that's not too complicated. Thanks!


VB:

With lbCounty[INDENT]For lbr = 0 To .ListCount - 1[/INDENT][INDENT]NextColumn = Worksheets("Example").Cells(3, Columns.Count).End(xlToLeft).Column[/INDENT][INDENT][INDENT]If .Selected(lbr) Then[/INDENT][/INDENT][INDENT][INDENT][INDENT] 'if Group selected[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]If .List(lbr) = "Group" Then[/INDENT][/INDENT][/INDENT][INDENT]'enter code here that will run formulas for A, C, G, K, T[/INDENT][INDENT][INDENT][INDENT]Else[/INDENT][/INDENT][/INDENT][INDENT]'normal code for all other selections[/INDENT]


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




I need help with understanding how to add a third dependent list. The attached spreadsheet has the following tabs:

form = this shows the 3 levels that I need. Data Validation has already been defined for Levels 1 and 2.

values = values for each of the 3 levels

Guidelines/instructions I've come across have been helpful until it gets to explaining the 3rd level. I get that each name list has to be unique. One set of instructions suggested combining the entries in the first two columns, which didn't make sense. I then tried an 'if' calculation but the end result wasn't the full list -- just the first line entry. So I'm close but have hit a wall in figuring this out.

Any suggestions?


I need to put together a worksheet that assists users in selecting a valid account for posting activity. Prior to viewing a list of acceptable account numbers (with full descriptions), the user must 1st select from 4 other drop downs.

Column A has 2 possible selections
Column B has 2 possible selections
Column C has 8 possible selections
Column D has 20 possible selections
Column E has 36 possible selections

Column A and B are not dependant on each other
Column C is dependant on the combined selections in Col A and B
Column D is dependant on the combined selections in Col A, B and C
Column E is dependant on the combined selections made in Col A, B, C and D

I've read many posts that reference cascading drop down lists. I have not been able to find a post that 1) addresses having more than 2 dependant lists, nor 2) avoiding the need to create dependent lists (using the Name and Indirect function) for each possible combination

The Data I'm building off of is not static though major changes are not expected to occur often.

I'm pretty lost here. I'd greatly appreciate any help.

Thanks


Hello,

I have been running macros from single selection list boxes without a problem.

I now would like help to learn the ability to run multiple macros from a multi selection list box.

So for instance there are 5 selections in the list box. The end user would select 3 of them and then it would run 3 macros associated with each of 3 itmes selected.

Any help would be much appreciated on this one,

Cheers,

JC.


I would like to have a formula to add the numbers in one column based on the outline_level in another column. The issue at hand is I have an outline level that starts with 1, then 2, then 3. I will have one level 1, multilple level 2's, with lots of level 3's under each level 2.

Example:
Column B Column K
Outline_Level Hours
1 total of all level 2's
2 total of level 3's under this level 2 (not to include the next level 2)
3 hours
3 hours
3 hours
3 hours
2 total of level 3's under this level 2 (not to include the next level 2)
3 hours
3 hours
3 hours

I have a couple of formulas already for the level 1 and level 2, but cannot figure out how to stop at the next level 2.

SUMIF(B9:B613, 2, K9:K613) (sums all the level 2's for the level 1 effort)

SUMIF(B10:B614, 3, K10:K614) (sums all the level 3's under the level 2 effort)

Any help would be greatly appreciated.


Hello,
I am wondering if there is a way to have a multi-level pick list in Excel.
In other words, I have a main category as a pick list in column A, then a
more detailed category as a pick list in column B. I would like to make it
so that the pick list in column B is limited based on the selection from the
main category pick list in column A.

Thanks in advance,

Dan





Hello,

I have 3 columns in my spreadsheet (Name,Level, rate). Level represents an employees position and rate is his pay rate.These 3 columns form an employee list.
It might be decided that another employee of a certain level is needed in which case the new level is entered into the list, but the pay rate is kept blank and some generic name like "new" or "vacant" is used for name.
So the list would like like this:


Name-Level-rate:
--------------------
Tom - 2 - $100
Jan - 3 - $200
Dave - 2 - $100
New - 2 -

I would like to write a formula that reads the list of levels (in my case c5-c71) and only counts the number of employees who are already hired, and not the new proposed positions. So in my above example i want the result of my code to be 2.
This is how i tried to do it, column C represents the LEVEL, and column D represents the RATE:

Quote:

=COUNTIF($C$5:$C$71,IF($C5=2,IF(ISBLANK($D5),TRUE,FALSE),FALSE))

I want it to not count the entry if the RATE is blank.
Above code isnt working though.

Hope i didnt break any forum rules.
Cheers in advance!


How do I reference a number based on the name of the table its under?

If you need me to Download Colo's HTML Maker, I will have to do it after I reboot my computer,

Code:

Barracks	
Level 23	units per day
Sp      	971
Sw      	665
Ax      	751
	
Level 24	
Sp      	1041
Sw      	702
Ax      	800
	
Level 25	
Sp      	1094
Sw      	745
Ax      	847


I want to look up by level. If level 24, I want to return either spear, sword, or axe.


Hi all,

I'm trying to create a spreadsheet with two lists which are dependent on each other. However there are 3 levels of information, the 1st level is decided by a simple IF formula which gives the 1st list with information from the 2nd level. What I'm having problems with is creating a formula to create the 2nd list from the 3rd level.

I have attached a sample Spreadsheet to better explain this. The spreadsheet I'm working with has a lot more data but still only 3 levels.

Thank you for any help

Tommy


Hello,

I have a list of names with a Level added:

A2= Bobby Goes to the Store Level 2.3 FCU 2
A3= Grandma's Cookies Level 1 FCU 8
A4= Goodbye Randy Level 9.6 FCU 5

Is there any way I can pull just the Level text out of it and place in it column B?

Example:

A2= Bobby Goes to the Store Level 2.3 FCU2
B2 = Level 2.3

A3= Grandma's Cookies Level 1 FCU 8
B3= Level 1

Do you know of a formula I could use?

Thanks for your help!!!


No Level Charge (Includes suture removal, packing removal & test results) (10) Left Without Being Seen (11) Level I (0 - 10)
(Includes Scheduled Rechecks) (3, 26, 32) Level I w/Additional Service Level II (20 - 30) (4, 27, 33) Level II w/Additional Service Level III (40 - 60) (5, 28, 34) Level III w/Additional Service Level IV (70 - 90) (6, 29, 35) Level IV w/Additional Service Level V (100+) (7, 30, 36)
I'm trying to find a formula based on the above Levels and it meeting the point system criteria. Example: If H10 is between 0 and 10 for Level one, it will put an "X" in that box. I know this is going to turn out to be something simple but novice here. Thanks in advance


Hi

I have a 3 level dependency dropdown structure that I need you help with please.

I am able to achieve my goal up to the second level but can't seem to get the third level to work.

Looking at the attached sample file you will see that columns A,B,C represents a typical 3 level structure for my categories.

Columns L,M,N is where I've applied the Data Validations. The user should only be able to select an option in the dropdown based on the previous level's selection.

Most of the formulas that I've used was based on the article which I found he http://www.contextures.com/xlDataVal13.html

I'm sure you will be able to see what it is I'm trying to achieve based on the sample file but please tell me if I did not explain myself well enough.

Thanks for you help.

I am undertaking an exercise where I have a list of rooms, and within those rooms I need to input a series of data of between 0 and say 20.

For example, I will start off on Level 1, which contains 10 apartments and within those apartments, contains 5 rooms.

I thought the easiest way to input the data is to list Column A as follows

Level 1 Apt 1 Bedroom 1
Level 1 Apt 1 Bathroom 1
Level 1 Apt 1 Kitchen
Level 1 Apt 1 Bedroom 2
Level 1 Apt 1 Bedroom 3

and so on... so I have a list of all the areas.

Then within each Room, will be a series of 'snags' which correspond to numbers so in the row next to the location, I can enter the numbers, in column B, C, D, E... etc.

This will save me having to type out all the rooms, and some will have 3 snags and some will have 10 snags etc..

They key thing is, once i'm done entering the data - I need to be able to transpose the spreadsheet so it will go from this

Level 1 Apt 1 Kitchen ......... 2...........2.........4

into

Level 1 Apt 1 Kitchen...... 2
Level 1 Apt 1 Kitchen...... 2
Level 1 Apt 1 Kitchen...... 4

Without too much effort. Can this be done?


I need to do a multi-level lookup to link two records.
I have a list of books (new edition) in Sheet1 that I need to lookup in another list of books (previous edition) in Sheet2.

1st level is matching the 'TITLE' of the book (col B)
2nd level is matching the 'Author' of the book (col C)
3rd level is matching the 'Publisher' of the book(col D)
4th level is matching the 'Edition' of the book(col E) (match is true if the numbers are equal OR if new_edition_number = prev_edition_number +1)

For each book in Sheet1, if a relevant match is found on Sheet2, I need to mark it in col F ('yes'/'no') and also return the corresponding value stored in col F of Sheet2

Also List in Sheet2 runs deep (abt 100,000 entries), so faster lookup wud b helpful. Please help....thanks!