Variables as column names in lookup
I am trying to use a lookp funtion to return a value from a table of operator schedules, There are several types of schedules that I have created as rows in the OperatorSchedules table. Each Row of the table has a name field and then 42 columns (one for each day of the six week rotation), Day 1, Day 2... Day 42. In the values in the day columns have an X if the operator is off work, an N for night shift, D for Day shift, etc.. If I use this formula it returns the correct information. LookUp(OperatorSchedules,ScheduleName="Board A", 'Day 35') When I try to make it dynamic so that I don't have to make individual screens for each month, using a variable in the column name field it returns the text value of the variable. LookUp(OperatorSchedules,ScheduleName="Board A", _firstDayInSchedule) I have a formula (below) that calculates the difference between a reference date (fxDayOne -10/9/2024, which corresponds to the day that the Day 1 column in the DB aligns with the actual schedule) and the start of the month and then appends "Day " to it. Here is that formula. I have tried with and without the Char(39) values in the formulas and both return the plain text value. Anyone know of a way to make the lookup see the variable value as a column name and not plain text? If( DateValue(_firstDayOfMonth) > DateValue(fxDayOne), Set(_dateDifference, -1 * DateDiff( DateValue(_firstDayOfMonth), fxDayOne )); Set( _firstDayInSchedule, Char(39) &"Day " & Mod(_dateDifference,42)&Char(39) ), Set(_dateDifference, DateDiff( DateValue(_firstDayOfMonth), fxDayOne )); Set( _firstDayInSchedule, Char(39) & "Day " & 42-_dateDifference+1&Char(39) ) );