1

Suppose I have a sheet like the one below (public link to the sheet), in which I have a list of teams (Red, Blue, etc), and a set of tasks for them each (collect data, agree rules, etc).

In the example you can see that the Red team have completed the tasks to earn Silver, whereas the Blue team have only completed those for Bronze.

A formula calculates whether each team has met the conditions for its Bronze/Silver/Gold medals.

Screenshot of Google sheet

The problem

I want to be able to look this up from another sheet, and know that Red are on Silver, and Blue are on Bronze.

The complication is that I might later want to add new tasks to a medal, or add a new medal, or even a new team, so I see two problems:

The sheet that looks up this information can't rely on position, but has to do it by name.

Even within this sheet, I need to find a way for the team to be able to discover its medal, which means something like "if all the checkboxes in the Bronze section are True", once again without being able to rely on their position.

What kind of technique would you adopt in this case?

3
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Jan 3 at 4:08
  • 1
    Your data layout is problematic as you rely on relative position to infer meaning. Each row should include all the information required to recreate the layout if it ever got resorted. If you want to prettify the data you should separate the data and the presentation of the data on different sheets. I'll try to show you what I mean when I have a moment later.
    – Blindspots
    Commented Jan 3 at 21:58
  • @Blindspots Thanks, you are completely correct, but it was while trying to find a solution that I discovered that named ranged exist - which I didn't before. Hence my answer below, I am sure many people don't. Commented Jan 4 at 12:22

1 Answer 1

0

The way to do this is by using named cells.

Google sheets can name both cells and ranges, and these can be used in calculations.

Named cells are in effect variables, that can be referenced by their name rather than position.

To name a cell: View > Show > Formula bar, then rename the selected cell from e.g. C18 to e.g. total_area.

To name a range: Data > Named ranges

This might be obvious once you know it, but if you don't even know that variables exist in Sheets, it might not even occur to you that there is no way to address a cell without referring to its position.

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jan 4 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.