Is there an elegant way to get linked data in Google Sheets?

February 11, 2019 2:38 PM

Basically I have two sheets on one spreadsheet (Dashboard and Resources). One, Dashboard, is used to generate a drop down based on this criteria: =Resources!$B$2:$B. This works correctly and populates the drop down with the name of the values of that column from the Resources sheet and the user may select, "Gold," or =Resources!$B$23. I'd like to get the Price column in C, Resource!$C$23 based on user's selection. This does not work.

However, it appears there's no way to get that "Gold" is actually a reference to "Resource!$B$23" when on the Dashboard sheet, I can only get "Gold." Is there anyway more elegant way to solve this than just searching over the column in the second spreadsheet for "Gold."

I went to so far as to look at the Rest API and the remote value isn't even stored there:


{
"userEnteredValue": {
"stringValue": "Gold"
},
"effectiveValue": {
"stringValue": "Gold"
},
"formattedValue": "Gold",
"effectiveFormat": {
"backgroundColor": {
"red": 1,
"green": 1,
"blue": 1
},
"padding": {
"top": 2,
"right": 3,
"bottom": 2,
"left": 3
},
"horizontalAlignment": "LEFT",
"verticalAlignment": "BOTTOM",
"wrapStrategy": "OVERFLOW_CELL",
"textFormat": {
"foregroundColor": {},
"fontFamily": "arial,sans,sans-serif",
"fontSize": 10,
"bold": false,
"italic": false,
"strikethrough": false,
"underline": false
},
"hyperlinkDisplayType": "PLAIN_TEXT"
},
"dataValidation": {
"condition": {
"type": "ONE_OF_RANGE",
"values": [
{
"userEnteredValue": "=Resources!$B$2:$B"
}
]
},
"showCustomUi": true
}
},


So it apparently stores the range criteria (ONE_OF_RANGE), but not the referenced cell selected, only the text. I might have answered my own question, that Google itself doesn't store the referential data for the cell.

I'm just trying to avoid doing anything too complicated, and sort of went down a rabbit hole.

posted by geoff. to Computers & Internet (10 answers total)

I think what you want is a VLOOKUP. Here's a rundown of how to set it up in Sheets.
posted by basalganglia at 2:50 PM on February 11, 2019

You do want a vlookup in the cell next to the drop down.
The vlookup will look for the value in the cell next to wherever it finds the value “Gold”. I am not sure why you are worried about whether or not it’s going to a precise cell location; if that’s important to you could you expand on why? It seems unusual to me.
posted by bleep at 3:10 PM on February 11, 2019

Response by poster: @bleep, well in theory if Gold was listed twice it would return the first match I assume. Not a big deal and probably a bad reason for avoiding vLookup. A bigger reason is that Resources!B2:B which should mean all available rows doesn't work, I don't know how many rows there are. So then I guess I'm doing a row count somewhere and building out a dynamic query somehow.
posted by geoff. at 3:16 PM on February 11, 2019

Response by poster: My fault I didn't read how vLookup worked. I needed to specify the optional parameter "is_sorted" as false. I don't know what's up with that. In any case this will get ugly if there's a lot of rows but at that point I'm looking into probably not doing it in the spreadsheet.
posted by geoff. at 3:23 PM on February 11, 2019

I use vlookup in every spreadsheet I make and I make a lot of them. It’s really not that messy and sheets is fast enough to search b2:b every time. If gold is listed twice then you have to figure out a way for the user to tell you which value they want, there is no formula that can do that.
posted by bleep at 3:34 PM on February 11, 2019

Like if the user is interested in the price of gold in a certain date range you could have date inputs and then use the filter formula to grab all the matching values as long as there are dates in the resources tab.
posted by bleep at 3:35 PM on February 11, 2019

I'm a little confused about what your data looks like but I think instead of a vlookup you may want to be doing a pivot table with a filter field? Is it possible that that would get you closer to showing everything you want?
posted by brainmouse at 3:47 PM on February 11, 2019

Without knowing more about the structure of the data, it's hard to say. However, it sounds possible that your problem might be solvable with MATCH() function, which returns the position of a cell within an array based its value, and possibly in combination with OFFSET to retrieve the value of an adjacent cell.
posted by voiceofreason at 7:01 PM on February 11, 2019

This sounds like something Query should be able to help you with. The pull down makes it funky, but I’ve built plenty of google sheets that reference user inputted data in a cell to generate a Query that pulls from a different sheet or sheets.
posted by notyou at 7:29 PM on February 11, 2019

used to generate a drop down based on this criteria: =Resources!$B$2:$B. This works correctly and populates the drop down with the name of the values of that column from the Resources sheet and the user may select, "Gold," or =Resources!$B$23. I'd like to get the Price column in C, Resource!$C$23 based on user's selection.

The reason this isn't working as you expect is that the dropdowns are not implemented as a formula that generates a value for a cell, but as a data validation overlay that checks an existing value and optionally lets you enter a new one by selecting it from a list.

The actual data held in a cell to which you apply dropdown-enabled data validation is not a function result, just raw text or a raw number copied from the validation range by the dropdown mechanism but not linked to it in any way. You can see that this is the case by choosing something from the dropdown, then going back to the Resources sheet and changing one of the values within the validation range, then returning to Dashboard and looking at the cell with the dropdown: it will still be set to its most recent value and Sheets will have flagged it as containing invalid data.

In fact you can type anything you like into such a cell completely independent of the dropdown mechanism; but if you type something that doesn't occur within the validation range, Sheets will complain and revert the value to what was there before.

So the only way to associate the value in a dropdown-sourced cell with another value held in a column adjacent to the validation range is to start by looking up the data within that cell. And this, as you correctly note, means that you will almost certainly need to make each such value unique.

it sounds possible that your problem might be solvable with MATCH() function, which returns the position of a cell within an array based its value, and possibly in combination with OFFSET to retrieve the value of an adjacent cell.

This is pretty much exactly what vlookup() achieves in a single function call. From Sheets help:

VLOOKUP(search_key, range, index, [is_sorted])

Example
VLOOKUP(10003, A2:B26, 2, FALSE)

Summary
Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

search_key
The value to search for. For example, 42, "Cats", or I24.

range
The range to consider for the search. The first column in the range is searched for the key specified in search_key.

index
The column index of the value to be returned, where the first column in range is numbered 1.

is_sorted - [optional]
Indicates whether the column to be searched (the first column of the specified range) is sorted, in which case the closest match for search_key will be returned.

If you set is_sorted to True and the first column of your search range is not in fact sorted, you will get bogus results because Sheets will try to use a fast binary search for the lookup and that process only works on sorted lists.

For lists containing more than a few tens of items, binary search will go much faster. If the Resources sheet is something you're setting up yourself rather than something the user can update freely, making sure that $B2:$B is laid out in sorted order and setting is_sorted to True will make the search delay completely negligible.

Here's a Sheet that uses vlookup() in cell Dashboard!B4 to achieve the effect you're after. It's shared read-only to avoid vandalism, so you'll need to make a copy before being able to exercise the dropdown.
posted by flabdablet at 6:16 AM on February 12, 2019