Using a row value to reference a column name

[Mike_T]

I have a Calendar table with a list of Dates in Col1 and the rest of the columns are named after a Trainer (e.g. bob, sally, jack, joe, etc). Under each trainer is a value of “booked” or “available”.

I have a scheduling table (form). The user selects the trainer and I want the date column drop down menu to show only the dates the trainer selected is available.

This formula works fine to select only the dates the one trainer is available. How do I replace this column name with the value from the other trainer selection field?

SELECT(TrainerCalendar[Date], [Trainer_Name] = “Available”)

I basically want the [Trainer_Name] to be dynamic when the trainer is selected. Possible or should I restructure my table differently?

[LeventK]

Your sheet structure shall be different. You shall have a column for all trainer names (where trainer names are in rows not columns) and an enumlist column based on date showing the available dates for each trainer. Then you can refer to this Available Dates enumlist column. Let’s assume this kinda structured sheet have the name of Trainers. From any table, below expression will fetch the values of the available dates:
SELECT(Trainers[Available Dates], [TrainerName] = [_THISROW].[TrainerName]

[Mike_T]

Thank you for this solution. I don’t know why I was trying to avoid this. I guess I think that having a really tall column is going to slow things down. If I have a dozen trainers and want to show 6 months worth of dates (12x180 rows). Not that big of a deal really. Thanks again for ending my torture trying to over solve the simple!

[LeventK]

@Mike_T
You may also want to check this post by @GreenFlux

CRAZY CALENDAR HACK: Add Events Directly to a Timeslot, Pre-Fill Date/Time Tips & Tricks 💡

Ok, if the other attendees do not need access to the file, I wouldn’t store the link in the description. Try adding another table to store the files, and add a Virtual Column (Type=Ref) that relates it back to your calendar table. Then, as you’re entering a file, the Ref column should give you a list of Calendar events to link it. This will create a Related Files (REF_ROWS Virtual Column) in your calendar table that will show all the files assigned to each calendar event.

[Mike_T]

Wow! This is interesting indeed! Again, I’m incredibly impressed with the Appsheet community for their creativity and generosity. Every solution I read only makes me want to add more and more functionality to my apps. However, I also struggle to decipher someone else’s app and nomenclature and then translate it to my own use. Just had to write out my frustration/curiosity. Thanks again for passing along this link!

[LeventK]

You’re welcome @Mike_T, my pleasure to be helped of.