Forum Discussion

ThomT25's avatar
ThomT25
Copper Contributor
Apr 16, 2024

Is an IF statement the best solution to display the desired data?

Hello,

I have limited Excel skills, and this is my first post. I volunteered to create a more user-friendly view of data for people not used to viewing spreadsheets. Here is some sample data:


I would create a 2nd worksheet called View Choice. When the person selects their choice, 1st, 2nd, 3rd, (in cell B6) I need to display the data for that choice:


The dataset would include choices 1-10. I must display about 25 fields for each choice consisting of short text, numbers, and dates. I only need to display one choice at a time.

Is using an IF statement in each field the most efficient way to do this...

=IF(B6="1st", INDEX($B$2, 1, 1), IF(B6="2nd", INDEX($C$2, 1, 1), IF(B6="3rd", INDEX($D$2, 1, 1), "Not Specified")))

...or is there a better solution?

Thank you in advance for any guidance you can provide.
Thom T.






  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    ThomT25 Attached an example of how you could tackle this, using Data Validation, named ranges and an INDEX/MATCH function.

     

    By using named ranges you don't have to worry about where the data sits in your workbook as Excel will find its location by its name.

     

    See if you adopt these methods in your own workbook. Come back if you get stuck and provide a link to your file on Onedrive, Dropbox or similar, giving full access. Then it will be easier to help you further.

    • ThomT25's avatar
      ThomT25
      Copper Contributor

      Riny_van_Eekelen 

      THANK YOU - Your solution worked perfectly until I found out the worksheet structure differs from the sample I provided. I spent the morning trying to adapt your solution to the new structure, with no success  Here is the old vs. the new:


      Could you update your formula for 1 cell (Location) to work with the new structure? I think once I see it, I can apply it to the other cells. Thank you - Thom

      PS - I'm new to Office365 and can't figure out how to attach my Excel file from the cloud. 

      • ThomT25 

        What you need to do that's to replace in Name Manager references for categories and choices (reference for categories becomes one for choices and the opposite). Formulae are the same.

        and exchange 2nd and third parameters in INDEX formula.

Resources