I have two tables as shown below, tables A & B. I would like to use the data in A to create a stacked bar chart, however I would like the information in table A to change depending on what what status is in Table B Project status. If the Project status in B is e.g 5.Production, then I would like all project positions before5.Productionto be at 100% which is 8 (as I have 8 positions) and all project positions after 5.Production to be 0. So the function will basically be showing next steps from 1 upto 8. If we are at step 1 then everything after step 1 should be register 0 as in not yet done, and if we are at step 5 it means steps 1-4 have been fully completed but steps 6-8 are at 0.Table A

Project status Project positions Total project steps

1.Wachten.......................8................................8

2.In ontwerp.....................8................................8

3.Tergoedkeurin................8................................8

4.Gelanceerd....................8................................8

5.Production.....................0................................8

6.Afgett...........................0................................8

7.Deellevering..................0................................8

8.Geleverd.......................0................................8Table B

Project # Project status

150565 5.Production

message edited by JoeMuz

First, a posting tip: If you click on the

blue lineat the end of this post you will find the instructions on how to format example data so that it is easier for us to read. In the future, please use that method. Don't forget to use Column letters and Row numbers as shown in the example. Thanks!As for your question, if you start with this layout, then formula below should work for you:

A B C 1 Project status Project positions Total project steps 2 1.Wachten 8 8 3 2.In ontwerp 8 8 4 3.Tergoedkeurin 8 8 5 4.Gelanceerd 8 8 6 5.Production 8 8 7 6.Afgett 0 8 8 7.Deellevering 0 8 9 8.Geleverd 0 8 10 11 Project # Project status 12 150565 5.ProductionPut this in B2 and drag it down:

=IF(MATCH($B$12,$A$2:$A$9,0)>=MATCH(A2,$A$2:$A$9,0),8,0)

This formula uses the MATCH function to determine the position in the array (A2:A9) of the value in B12. If the position is Greater Than or Equal To the position of the "status" in Column A, then an 8 is returned.

Please note that the MATCH function does not return the

Rownumber of the lookup_value, it returns theposition in the lookup_array.e.g. 5.Production is in Position 5 of the lookup_array (A2:A9) even though it is in Row 6 of the worksheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thank you Derby Dad! The pointers on how to clearly post are handy, I'm still new at this so I'll def take it on board with my next posts. The MATCH function works well for all Project positions between 1-7 thanks, just 1 slight hitch in the end. When my value in B12 from the example above = 8.Geleverd, then the formula for everything else (1-7) goes to #N/A instead of saying 8. When B12 = 8.Geleverd, all Project positions should also be 8, thus all Project steps complete.

Thanks!

You must have done something different because I do not have that problem. When B12 = 8.Geleverd, all I see is eight 8's in B2:B9.

Are you sure that your

lookup_arrayis correct? Are you sure that thelookup_valueis an exact match? (Try copy/paste from your table to B12). One possible cause (the most common) of a #N/A from a lookup function is when thelookup_valueis not found in thelookup_array.You might want to try the Evaluate Formula feature on the Formula ribbon. This will allow you to single step through the formula and see exactly what portion is returning the #N/A error.

Assuming you are using the exact setup I use in Response #1, post the exact formula you are using and I'll test it in my worksheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You were right DerbyDad, found the error when I looked carefully at the formula in the cell. All clear & working, thanks again!

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History