1

I saw this question and its accepted answer, but I can't wrap my head around how/if it might be used for my situation.

I'm pulling data dynamically from several APIs so the data may be different at any moment, including the actual placement of the data on my sheet. I want to define various cells within the returned data, as named ranges. But without knowing for certain exactly what cell each piece of data will reside in at all times, named ranges don't work well for this. Ideally I could place say a vlookup in a named range, but you can't use formulas at all in named ranges, in Google Sheets.

I don't update the formulas that often, but when I do my calculations break if the named ranges aren't correct anymore. I would have to use vlookup in dozens of formulas on multiple sheets and it just makes the formulas huge and ungainly, when a named range keeps them incredibly tidy and easy to understand. GAS is fine with me as long as I know what to do with it.

Is there any way to get to the same effective result using a named range? I reference these named ranges in many places throughout my workbook, which is why I wanted to use named ranges in the first place... it makes all those other formulas much easier to read & debug.

EDIT: I hope I can provide a bit more of an example here. A hypothetical set of data might be:

ABC   123456
DEF   987654
GHI   246802
ABC   537910

But the next time I pull the data, it could look like this:

XYZ   135791
ABC   123456
LMN   086420
DEF   987654
GHI   246802
ABC   537910
LMN   776655

So now my original 3 values are still there, but they have moved in their rows. A vlookup would make this very easy since it will always adapt to wherever the target value is located, and return the adjacent value. But I need each one of those adjacent values to be its own named range, that will always be correct regardless of where the data moves to.

Also there are often multiples of the key value in the data, and a given formula elsewhere in the sheet needs to pull the first one, or second, or third, etc.

  • The row position can change, see the example data I just added. A named range would allow me to just use that name in formulas on other sheets easily and make those formulas also easy to read when I work with them. But since I don't know vertically where the data will be, I need a dynamic named range of some sort. – Jonathan van Clute Oct 31 '17 at 22:57
  • The column containing the alpha is F, the column containing the numeric, is G. But if I have no way to be sure that XYZ will always be in a specific row, how can I reference it from a formula anywhere without naming it? – Jonathan van Clute Oct 31 '17 at 23:01
  • How frequently are you pulling the data the APIs? How do you that? What are the actual columns used for the key and the value to get retrieved? Why you don't want to use VLOOKUP? Is it OK to use Google Apps Script? – Rubén Nov 1 '17 at 0:58
  • I pull data from a number of different blockchain wallets (etherscan.io, others), and I can't always know what's going to be returned (lately there have been lots of "air drops" of new coins just being shoved into people's wallets). I don't update them that often, but when I do my calculations break if the named ranges aren't correct anymore. I would have to use vlookup in dozens of formulas on multiple sheets and it just makes the formulas huge and ungainly, when a named range keeps them incredibly tidy and easy to understand. GAS is fine with me as long as I know what to do with it. – Jonathan van Clute Nov 1 '17 at 1:02
2

In Google Sheets, named ranges works with "static" ranges, not formulas.

I'm thinking on two approaches, both using Google Apps Script.

One aproach is to use a custom function, the other is to create a script that update the named ranges.

Custom Function

With a custom function we could use Google Apps Script to extend the built-in spreadsheet function library, in this case to use custom function name in a similar way that a named range could be used but it would have the logic to findout where are located the values after the last import.

Example:

The follwing custom function looks for a value on column A, then return the value on the same row from column C.

/** 
 * My Named Range
 *
 * @customfunction
 */
function myNamedRange(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');
  var colA = s.getRange(1, 1, s.getLastRow(),1);
  var values = colA.getValues();
  var search_key = 'C';
  var index = values.join('').indexOf(search_key);
  var data = s.getDataRange().getValues(); 
  return data[index][2];
}

It could be used in a formula as follows:

=ISTEXT(myNamedRange(A1))

NOTES:

  • Some built-in functions like SUMPRODUCT, doesn't accept custom function as arguments.
  • Custom functions results are cached and recalculated when the spresheet is opened or when its parameters changes. On the above example it's assumed that the value of A1 will change every time an import occurs.

Script to update named ranges

The Class Spreadsheet has several methods to handle named ranges that could be used to create, update and delete named ranges.

  • Thanks but I'm afraid I don't understand how this relates to what I'm looking for? I can't put that formula in a named range... – Jonathan van Clute Oct 31 '17 at 4:10
  • @JonathanvanClute: Rather than use it in a named range, use it instead of a named range. I updated my answer. If my answer is still unclear for you, add more details on the question like an example of formula that you want to use. – Rubén Oct 31 '17 at 18:31
  • I'm afraid I don't follow at all what this would be used for. I get that it's a GAS that returns a value on the same row as the value given to the function, but I don't see how that's related to what I'm asking about. I need to look up one value somewhere on the sheet, and return a value adjacent to it (what a vlookup would do). The problem is that I have no way of being sure where in the sheet, the first value will appear. I'll know the column, but not the row. I hope that clarifies my question. – Jonathan van Clute Oct 31 '17 at 22:28
0

I've come up with a solution which is acceptable to me. I created a secondary table that fetches the data I need to turn into a named range, by using a nested vlookup and indirect, like so:

=vlookup(J22,indirect("F"&row(vlookup(I22,A:G,COLUMN($F$1),false))+1):G91,2,false)

This question was originally attempting to find a more direct solution so I simplified my example data, but the final data is a bit messier. I needed to first locate a string that I knew would never move (which is in column A), then figure out its row number and look one down to set the start of the range for a second vlookup, which then retrieves the actual piece of data I need.

This is in turn made into a named range, and now no matter where my data moves itself vertically as the API returns change, my named ranges don't break. Working like a charm!

-3

Google Sheets simplifies Dynamic Named Ranges by adjusting the "height" of the column according to the countA of the items in the column. All you have to do is use the formula 'Sheets1'!A:A on your named range panel - as opposed to writing the specific range like 'Sheets1'!A1:A10. By putting the entire height of the column as your Named Range, Google sheet will output only those cells that have values inside, empty cells will be left out and in effect, you have created for yourself a dynamic range :). Enjoy!

  • Thanks but I'm afraid that's not what I was looking for. A dynamic named range means I would be able to specify the contents of the range based on some sort of formula. For instance, a named range that consisted of only cels containing the letters "ABC". Simply specifying that the named range be ALL the values in the entire column, doesn't get me what I was posting about. Please re-read the OP and if you have any suggestions that solve the actual problem, I'd love to hear them! – Jonathan van Clute Jan 2 '18 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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