Okay...I am such a super noob when it comes to excel. So please flame me if you must oh epic Excel Gods but have mercy on this pitiful fool for not having the necessary brain power to work through this on my own.
Here is what I am doing.
I can't yet add images or I would. If the admin will allow I would be happy to upload a word document with the snapshot of what I am doing. Since it is proprietary info from my company I don't want to attache the excel because I am trying my best to respect my company NDA but also get help to improve an internal process asset that is not working as good as it could.
SO:
I have four columns set up (Supplier A)
1. Material Supplied - Under this column I give the total cost of the good they are supplying.
2. Labor Supplied - This column has the labor cost to install the material supplied.
3. Sub-Total - This column has Columns 1&2 added.
4. Substitution - This column has a drop down selection box with a YES or NO answer.
Then I have that same four column set up repeated only difference is this is a different supplier. So again:
Four Columns Set up (Supplier B)
1. Material Supplied - Under this column I give the total cost of the good they are supplying.
2. Labor Supplied - This column has the labor cost to install the material supplied.
3. Sub-Total - This column has Columns 1&2 added.
4. Substitution - This column has a drop down selection box with a YES or NO answer.
Then the NEXT COLUMN is SUB-BUDGET Column which I want to do the following and I have figured it out except one key element.
Currently I am running the MIN(Column 3, Column 7) and it returns the lowest price cost for Labor + Material.
However SOMETIMES there are substitutions on the materials, meaning they are not the specs called for but a substitute which we MAY or MAY NOT choose to use. To show this I have set up conditional formating, so when the Substitution column shows a NO answer the cell remains as is, but when it shows a YES answer it is filled with the color black, and the font is changed to white to allow us to easily see what is a substitute.
MY ASK:
I want to do a MIN(Column 3, Column 7) BUT EXCLUDE the substitutes almost as if they were a 0 sum exclusion. Meaning I do not want to have the MIN applied to any column 3 or column 7 item which is filled black, with white font because it is a substitute and it can not be auto filled into the Sub-Budget column until a submittal request is made to the Designer/Architect who can approve and we can change this at a later date.
The end result is to get the Sub-Budget column (Column 9) to fill with the Minimum value between Column 3 & 7 UNLESS the Column is filled with black and font is white.
Can anyone help me?
IF so you will have to go very in detail, step by step because I know almost nothing about the back end, macro, creating new functions side of Excel. So an insane amount of patience and detail will be necessary to lead me through this.
Thank you to whoever endeavors to help me here!
Hi David,
There's nothing to prevent you uploading an image or Word doc to this post, it would be better to provide an Excel workbook though.
Trying to understand your data layout and then recreating it can take time and lead to mistakes when we are trying to work out a solution. Even just some sample data you make up is better than nothing. Try to give us an accurate representation of how your data is laid out, and the types of data we are dealing with.
With regards the solution, it may not require VBA. Conditional formatting may work, or something else. At this stage I'm not sure as I don't fully understand the problem yet.
Regards
Phil
Hello,
Not sure if the example in the attached file is good enough, but it is at least simple and do show what you want to see.
Br,
Anders
Ok I have attached the file and this should make it easier.
Columns T & U are the Materials & Labor for Fergusson Appliances.
Column V is the Sub-Total of these Columns.
Columns X & Y are the Materials & Labor for Earth Elements Appliances.
Column Z is the Sub-Total of these Columns.
Columns W & AA are the SUBSTITUTION Columns. This is important to understand why this is here. When a client wants a Gaggenau refrigerator or Electric Grill or Cooktop, they want what they want they don't want something almost as good. However not all appliance suppliers can supply a Gaggenau. Sometimes they can supply a close SUBSTITUTE. I like to show that as an option but it should NOT be calculated in the budget because it is a substitute.
Now then how this works is if the item is not a substitute then I have conditional formatting paired with a MIN function that auto-highlights in GREEN the lowest price Sub-Total price of columns V & Z.
The problem is that I can't use MIN on the substitutes. Often times the Substitutes will be the least expensive option but they are not really an option because they are not what the client wants. They are substitutes.
So then what I want to do is to have the MIN function IGNORE anything where the Substitute column is marked with a YES.
This will auto fill the cell color in green which is not a substitute and is also the lowest price. So this way if I have three suppliers, and one has a substitute and it is the lowest price, it is not included, so the other two are the suppliers which are then calculated to know which one is BOTH 1) what the client wants exactly, and, 2) the least expensive option and is then cell filled with green.
Ultimately there is more I would like to do to this worksheet as well.
In the AR & AS & AT & AU columns I would like the lowest priced items that are not substitutes to be repeated here and auto populated but that is a whole other project for a whole other day.
So you know I spent hours and hours and hours trying to work through this problem. Somewhere out there is someone who can do this in 4 minutes or less and its killing me.
Philip Treacy said
Hi David,There's nothing to prevent you uploading an image or Word doc to this post, it would be better to provide an Excel workbook though.
Sorry I must not have seen that option earlier! I have attached now so you can see what I am working on!
Not sure if I attached the file or not...Oh there it is! Ok it is the same file I just uploaded twice I guess. Sorry!
Hello,
A simple solution is to use a formula with nested IF's. Check out the attached file and see if it is what you want.
