i am an architect with an extremely large and unwieldy spreadsheet. the spreadsheet is set up with the row headings as a list of drawing numbers issued for my project and the column headings a list of all of the document issuances (with dates). each time there was a document issuance (such as asi #1 on 1.1.08), i marked an "X" in the cell that corresponded to each drawing that was released in asi #1. following this logic - asi #1 is column B (cell B1), drawing numbers column A (cell A2), first "X" would be B2. this set up allowed me to see (when reading vertically) all of the drawing numbers issued in a particular asi and it also lets me see (when reading horizontally) how many times each drawing was issued.
i want to establish a column that will tell me the last issue of each drawing such that, at a glance, i can see that drawing number 100 was last issued in asi #5 and drawing number 101 was last issued in asi #2.
i set up an if formula that worked, but only for 8 columns, then it said the formula was too large. the formula i set up was designed to check the rightmost column (last asi issue) and see if there was an "x" in the cell, if so it input the asi #, if not it checked the next cell to the left to see if there was an "x" in the cell, if so it input the asi #, if not it checked the next cell to the left and so on. - =IF(I2="X",$I$1,IF(H2="X",$H$1,IF(G2="X",$G$1,IF(F2="X",$F$1,IF(E2="X",$E$1,IF(D2="X",$D$1, IF(C2="X",$C$1)))))))
i know that excel is very powerful, and can almost read my mind at times...but i cannot get tha result i am after. am i doing something wrong in the if function? is there a better function to use? please help!!!
thanks in advance for your time and your suggestions.
i want to establish a column that will tell me the last issue of each drawing such that, at a glance, i can see that drawing number 100 was last issued in asi #5 and drawing number 101 was last issued in asi #2.
i set up an if formula that worked, but only for 8 columns, then it said the formula was too large. the formula i set up was designed to check the rightmost column (last asi issue) and see if there was an "x" in the cell, if so it input the asi #, if not it checked the next cell to the left to see if there was an "x" in the cell, if so it input the asi #, if not it checked the next cell to the left and so on. - =IF(I2="X",$I$1,IF(H2="X",$H$1,IF(G2="X",$G$1,IF(F2="X",$F$1,IF(E2="X",$E$1,IF(D2="X",$D$1, IF(C2="X",$C$1)))))))
i know that excel is very powerful, and can almost read my mind at times...but i cannot get tha result i am after. am i doing something wrong in the if function? is there a better function to use? please help!!!
thanks in advance for your time and your suggestions.