Forum Discussion

Aoife Ryan's avatar
Aoife Ryan
Copper Contributor
May 11, 2017

= function

Hi,

 

I am trying to referance a cell within a column to another cell. The column contains a list that I need to chop and change and often delete individual cells with it. If I delete a cell the problem is I get a REF! error in the cell I want the text to appear in. Is there any way I can get the functions to update after I have deleted a cell in the column without having to go individually back to every cell I have a function in.

 

Formulas & Functions
Need Help
  • Yury Tokarev's avatar
    Yury Tokarev
    Steel Contributor
    May 11, 2017

    Hi Aoife,

     

    you might be able to use lookup functions, such as VLOOKUP or INDEX/MATCH in case you have a reference column for the purposes of lookup. Are you able to post an example of your data and the formula cell(s) so I can have a look?

     

    Thanks

    Yury

    • Aoife Ryan's avatar
      Aoife Ryan
      Copper Contributor
      May 11, 2017

       

      This is basically what I want to do but with more sensitive information that I don't want to post here. So I have different sections that I want th information in F to be in but I need to alter F as I get more/new info so I would like if Side A and Side B automatically updated to correspond to what is in Column F. At the moment I am getting the Ref error. I tried VLookup and match with no success

      • Yury Tokarev's avatar
        Yury Tokarev
        Steel Contributor
        May 12, 2017

        Hi Aoife,

         

         

        I agree with Detlef that you need to have a reference column for 'Site'. 

         

        I have attached one of the possible solutions to the problem. Essentially, you would need to find an nth match of the Site name in the Animals/Sites table. You can achieve it with a combination of the INDEX, SMALL, IF and ROW functions array entered.

         

        If we add site names in column G, and assume that the Site A range is fixed to B2:B9, you can array enter the following formula in that range (select the range, press F2, then ctrl+shift+enter):

         

        =IFERROR(INDEX($F1:$F8,SMALL(IF($G1:$G8=B1,ROW($F1:$F8)-ROW(INDEX($F1:$F8,1,1))+1),ROW(1:8))),"") .

         

        Then you can copy and paste the formula into the Site B range (D2:D9).

         

        A better solution would be to place the Animals/Site values into an excel table. If the table name were tblAnimals, then the formula in column B would look

         

        =IFERROR(INDEX(tblAnimals[Animal],SMALL(IF(tblAnimals[Site]=B1,ROW(tblAnimals[Animal])-ROW(INDEX(tblAnimals[Site],1,1))+1),ROW(1:8))),"")

         

        In this case, any new items in the lookup table would automatically flow through to the respective site up to the last row of the array range.  

Resources