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 pr...
Formulas & Functions
Need Help
Yury Tokarev's avatar
Yury Tokarev
Iron 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
      Iron 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.  

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

        Thank you so much for all the help

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      May 11, 2017

      Hello Aoife

       

      Your approach/setup is bad.

      You don't want a direct reference but some kind of lookup formula.

       

      You must have some criteria why "Cat" would be on "Side A".

      Put "Side A" in column G if the item belongs to "Side A". The same goes for "Side B".

       

      And with an appropriate formula you do the lookup.

Resources