Conditional Formula Requested

Greetings and thanks

I'm not sure exactly what you're asking as i've given as many examples as I can.

The desired result is it tells me the path i need to take to get the desired result

ie:Main Item 1 with Sub item 5 = Main Item 2 SC50 > Main Item 3 > Main Item 4 > Main Item 5

I'll be around for a few mroe hours so happy to sit here and give as much info as you need, thanks again

i submitted an example doc in the OP

basically it would have lists setup for "conditions" "sub item" "main item" etc and would query those lists for a result


i guess in plain terms im after: =lookup dropdownbox[for main item] then go to requested sub items to find out what you want to achieve, then go to that main item in conditions sheet, find its conditions [can either have 1 or 2 conditions], then seek out another main item that can have the same sub item with either of those conditions as direct, if non can have it as direct with those conditions, then find a main item with either of those conditions that can have it as indirect, then see if any main items with similar conditions to the new main item can have it as direct, if not then go tot he next one that can have it as indirect with a similar condition tot eh last main item. keep doing this until u find one that can have it as direct [which is the starting point] then report all necessary steps required to achieved your desired result from the found starting point


i can do this with html, but i have no idea if i can do it with excel. i just want it in excel so i can distribute it without having to host a site or make a flash file

in the example i gave you can't, only main item 4 can access sub item 15 directly. to get main item 2 paired with sub item 15 you would need to start with main item 4, then use its condition 7 to pair sub item 5 to main item 3, then use main item 3s condition 4 to pair sub item 5 with main item 2

i guess another way to look as it is data or video transfer.

but all the conditions are different forms of cord/cable and the main items are different hardware with different connection ports, so you cant plug a hdmi into dvi, but if you have dvi u can link the 2 and still achieve the same result (basically)

I guess some more simple examples would be transferring sub item 15 to main item 1.

Main item 1 can't directly have it, but indirectly it can receive it from main item 4 through condition 2

so a formula would go something like =lookup ([query what main item is-ok so its main item 1], lookup([query what conditions it has, ok so it has 2 and 3], lookup ([query what subitem u want attached, ok so its 15], lookup([query sub item 15 table and see what main items attach to it, so one directly which is main item 4], lookup ([do they share any conditions? yes, condition 2, great!], report in text (the main item, which is 4, the condition which is 2),if nothing was found that could possibly link the 2 post not available))))))))) etc.....

something along those sort of lines....



Main item 1 has condition 2 and 3 and is able to have sub item 1,2,3,4 directly, and all sub items indirectly
Main item 4 has condition 7 and 2 and is able to have sub item 13,14,15,16 directly, and all sub items indirectly

thanks mate, much appreciated.

Hi Napalm,

Welcome to excel forum.
I can't understand what you want??? Will you please explain your need in simple way?

Originally Posted by amit4blog

Hi Napalm,

Welcome to excel forum.
I can't understand what you want??? Will you please explain your need in simple way?

Originally Posted by Napalm13

i submitted an example doc in the OP

basically it would have lists setup for "conditions" "sub item" "main item" etc and would query those lists for a result


i guess in plain terms im after: =lookup dropdownbox[for main item] then go to requested sub items to find out what you want to achieve, then go to that main item in conditions sheet, find its conditions [can either have 1 or 2 conditions], then seek out another main item that can have the same sub item with either of those conditions as direct, if non can have it as direct with those conditions, then find a main item with either of those conditions that can have it as indirect, then see if any main items with similar conditions to the new main item can have it as direct, if not then go tot he next one that can have it as indirect with a similar condition tot eh last main item. keep doing this until u find one that can have it as direct [which is the starting point] then report all necessary steps required to achieved your desired result from the found starting point


i can do this with html, but i have no idea if i can do it with excel. i just want it in excel so i can distribute it without having to host a site or make a flash file

this is about as simple as i can explain it sorry. i don't know how else to say it sorry

Here is an example of a website with similar sort of setup i'm trying to achieve, not exactly, but similar.
http://waitingforseedot.appspot.com/
it has a main item selection, and 4 sub items, and i guess each one has conditions of some sort to find out what is compatible

Conditional Formula.xlsx

Hey mate,


Thanks again for putting in your time her,e it's very much appreciated.


Now i've got a couple of issues here. For starters, it doesn't seem to be following the conditions much. it is showing impossible paths and also not showing shortest possible paths. (maybe thats my fault for not asking for that)

I've uploaded a modified version of your worksheet with the new layout and changed the named lists to suit but have not touched any formulas.

Basically i want to to show shortest possible path (to help save on materials and time), as well as to not display impossible paths that could not happen due to incompatible conditions.

Examples: When i select Main item 1 and sub item 6, it shows the path MI 2 > MI 3 > MI 4 > MI 1, when that is not the shortest path, you can skip MI 4 in this instance.
Example 2: In the new worksheet, main item 5 cannot have sub items 5-8 ever. But when i select main item 5 and choose sub item 6, it shows a path, which isn't possible as its not displayed in the last sheets sub item 6 list. Yes the conditions can be met as su item 6 is directly linked to main item 2 which can in turn link to main item 5, but sub item 6 cannot be attached to main item 5 so it should not display as possible for sub item 6.

To help clarify it all a little bit, this spreadsheet will be for robotics parts(main items) that have many optinal parts(sub items) but most have different connectors(conditions) for the purposes of problem solving.

I'll be around for a few hours, so feel free to ask me to clarify anything,

Again, thanks so much for your time.

awesome! thats great. my only concern is with the red box on sheet 2, what is its purpose? if all that info already exists on the sub items tab why is it there?
this doc will have hundreds and hundreds of sub items, so that box wont be able to stay there like that

is there a way whatever is reading from it to just make it red from the sub item sheet instead?

Originally Posted by rajeshturaha

What condition are you given in Main item 5

sorry i realised i didn't upload it, its up now

i can see that if i select sub item 9, it goes through the process correctly when viewing it in the Working sheet. it says [Main Item 3] [ 4 ] [ 7 ] [ Main Item 2] it shouldnt be going to main item 2, it should be going down to Main Item 4, for some reason its going back up to 2 and then ending.

thanks again, much appreciated.

Originally Posted by rajeshturaha

Plz check , i have some correction in the attachment

that seems to be working now. the onyl thing is when i select a direct link, ie: Main items 5 with sub item 20, it shows #NUM instead of just Main Item 5