Looking to modify existing formula that works to allow for use of a drop down header and then lookup the corresponding column based on the year portion of the drop down.
So in Column Q starting at row 2, I have the following:
Where KeyGalEq and KeyPCN are defined named ranges. K2 holds the numerical data for a specific year as indicated by the text header in K1, in this case “Net Issue 2009”. I made the header at Q1 a dropdown list that displays one of three choices for different years eg. “2008 Usage”, “2009 Usage”, “2010 Usage”. I need to have the text in these headers so I can’t just use numbers.
I would like the formula above to be able to extract the number portion in Q1 and compare it to the “year” within text headers in J1, K1, and L1 and then choose the corresponding column from which the data will be used. So the K2 in my original formula may be J2 or L2 depending on the year chosen.
I cannot use Excel Jeanie or similar program at work due to administrative rights etc. so I hope the explanation above will be sufficient to get someone started. Also I am not sure how to get around the fixed column portion of ISBLANK(K2) and ISTEXT(K2) check at the beginning of the formula. I am willing to loose that if I can get the other part of the formula to do what I would like.
Any assistance would be appreciated.
So in Column Q starting at row 2, I have the following:
Code:
=[SIZE="2"]IF(OR(ISBLANK(A2),ISBLANK(K2),ISTEXT(K2)),"",IF(OR(ISBLANK(INDEX(KeyGalEq,MATCH($A2,KeyPCN,0),1)),ISTEXT(INDEX(KeyGalEq,MATCH($A2,KeyPCN,0),1))),"No Equiv Gallonage",IF(ISERROR(INDEX(KeyGalEq,MATCH($A2,KeyPCN,0),1)),"No Equiv Gallonage",K2*INDEX(KeyGalEq,MATCH($A2,KeyPCN,0),1))))[/SIZE]Where KeyGalEq and KeyPCN are defined named ranges. K2 holds the numerical data for a specific year as indicated by the text header in K1, in this case “Net Issue 2009”. I made the header at Q1 a dropdown list that displays one of three choices for different years eg. “2008 Usage”, “2009 Usage”, “2010 Usage”. I need to have the text in these headers so I can’t just use numbers.
I would like the formula above to be able to extract the number portion in Q1 and compare it to the “year” within text headers in J1, K1, and L1 and then choose the corresponding column from which the data will be used. So the K2 in my original formula may be J2 or L2 depending on the year chosen.
I cannot use Excel Jeanie or similar program at work due to administrative rights etc. so I hope the explanation above will be sufficient to get someone started. Also I am not sure how to get around the fixed column portion of ISBLANK(K2) and ISTEXT(K2) check at the beginning of the formula. I am willing to loose that if I can get the other part of the formula to do what I would like.
Any assistance would be appreciated.
