Data Validation that can "Refresh"?

[MattG]

I'll try to make a redacted version of the sheet I'm working on and attach it in a bit, but I wanted to see if anyone can help me with a workaround.

I help schedule games for a sports league on their different fields. I have each row as a date, and there are different columns for different fields. On a second sheet, I made a "Team List," and I was just going through and plugging in teams, let's say Teams 1, Team 2, etc., through Team 8.

I then go through and put the teams on the first sheet in fields where they will have practices and games. I have in each cell a data validation with a dropdown that shows a list that links to my team list. Team 1, Team 2, Team 3 etc.

As we are finalizing our registration, we have coaches and sponsors. So on my team list row 2 say "Team 1" currently, but I am changing the text to say "Team 1 - Smith - Car Dealer Sponsor"

I would really like the sheet that shows everything to update with the new text that is in that cell. But it currently does not.

Is there a better way for doing this that would do that as I go?

[Bernie Deitrick]

You could do that by creating names, based on a table. For example, using this two column table:

Team1 Actual team 1 name
Team2 Actual team 2 name
Team3 Actual team 3 name
Team4 Actual team 4 name

Select the table, use the Formulas tab of the ribbon, and choose "Create from selection" in the Defined names sub-section, and choose "Left column" as the option. Then when you want to use Team1's name somewhere, use

=Team1

and

Actual team 1 name

will appear.

Then, if you go to that table and change "Actual team 1 name" to "Team 1 Sponsored by Bernie the Excel Guy" then anywhere you have =Team1 will show "Team 1 Sponsored by Bernie the Excel Guy"