Forum Discussion
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.
ThomT25 Sorry, I went a bit too quick earlier. The MATCH arguments were in the wrong order to begin with. And obviously we can't refer to the same cell called 'selection' in all areas. The attached works now and I trust you can get it to work on your end.
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.
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.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.
