Forum Discussion

Jordan Miller's avatar
Jordan Miller
Copper Contributor
Aug 21, 2018

Help With A Formula

Hello,

 

I have a question that I can not find the answer to anywhere.  I have a table to map out different things at work.  I have the first three rows frozen to display the name of each column.  In the second frozen row, I have left a blank cell to summarize where I am currently at.  I am looking for a formula to take the information given in a cell and display it in one of the frozen cells.  I've included a picture to hopefully clarify what I mean.  I am wanting a solution so when I type an "x" into O:44 it will display the content from P:44, Q:44, R:44 and so forth into the cells in the frozen row (P:2, Q:2, R:2).  I am hoping there is a way to do this so that in two weeks when I type an "x" into 0:45, the content will then automatically display.  

 

Basically I have a huge list of dates and what my annual leave will be and I want a formula that will let me know where I am at based on putting an "x" in a different column.

 

Thanks!

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 21, 2018

    Hi Jordan,

     

    If in your column O you put "x" one by one without skipping them in the middle, you may calculate how many "x" you have in column and take result by offset from first row with data (#4). E.G for P2 it will be

    =OFFSET(P$4,COUNTIF($O$4:$O$1000,"x")-1,)

    and drag it to the right.

  • vijaykumar shetye's avatar
    vijaykumar shetye
    Brass Contributor
    Aug 22, 2018

    Hi Jordan Miller,

    Enter the following array formula in cell P2.

    =INDEX(P$4:P$2000,-3+MAX(IF($O$4:$O$2000="x",ROW($O$4:$O$2000),0)),1)

    The result will be the cell from column P, corresponding to the last entry of x in column O.

     

    If you use the below Array Formula, you will be able to get the result from column P, corresonding to the last, second last or any other entry of x in column O.

    =IFERROR(INDEX(P$4:P$24,-3+LARGE(IF($O$4:$O$24="x",ROW($O$4:$O$24),0),2),1),"-")

    Change the last 2 to 3, if you want the third last entry, instead of the second last.

     

    The formulas will work even if the entries of x are not in each cell of column O.

    Change the cell references as required.

    Array Formulas are entered using Control + Shift + Enter, instead of Enter.

     

    Do let me know if this is what you wanted to do or if any changes are required.

    • Jordan Miller's avatar
      Jordan Miller
      Copper Contributor
      Aug 22, 2018

      Thank you very much for your help.  However, when I copy and paste the formula I get #VALUE! and can't figure out why.

      • vijaykumar shetye's avatar
        vijaykumar shetye
        Brass Contributor
        Aug 22, 2018

        Hi Jordan Miller,

        One possibility could be that you have entered the Array Formula using Enter, instead of Control + Shift + Enter.

        Other possibilities could include incorrect reference range or incorrect data.

         

        Try entering the  Formula with Control + Shift + Enter or else attach a sample file, with test data in place of the actual data.

         

        I have attached the sample file for your reference. Formulas are in P2 and S2.

        Do let me know once the problem is solved or you still face some difficulties.

         

        Vijaykumar Shetye,

        Panaji, Goa, India