How to make a function read a string as a named range on Google Sheets

Question

Let's say I have a long list of twelve different names in one column and a value to the right column for each name. I already assigned a value to each of the twelve names by using the Named Range in Google Sheets, so I want to create a third column which adds the two columns together like A1+B1. The problem I am having is that when I put that in for the formula, I get an error message that I can't add a string and an integer together, so Google Sheets doesn't recognize when I reference a cell that I assigned a number to that string.

Here is an example of what I want my Sheet to do:

A1: Mike  

(I put the named range Mike = 10)

B1: =20

C1: =(B1+A1). And I want it to give me 30.

If I do this it works:

C1: =(B1+Mike) then it recognizes Mike as equal to 10.

So I want Google Sheets to recognize A1 as Mike then know that Mike is a defined range which is equal to 10. But as of now Google Sheets only acknowledges A1 as a string named "Mike."

I hope I made myself clear. Thank you so much for helping me.

Please define the desired result-- best way is to show various examples of A1 and B1 and desired C1. For when both are numbers, or 1num & 1string, or 2 string, or ... how many variations are in the data? — donPablo, Jul 18 '18 at 23:29
@I'-'I Hey thank you so much. I don't want to show the value of 10 in A1 so the indirect function works great! — Liam Anderson, Jul 19 '18 at 01:32
@I'-'I please post your comment as an Answer so that this will no longer be Unanswered — donPablo, Jul 21 '18 at 03:41

score 4 · Answer 1 · answered Jul 21 '18 at 09:40

4

You can use INDIRECT:

C1:

=B1+INDIRECT(A1)

answered Jul 21 '18 at 09:40

TheMaster

45,448
6
62
85

How to make a function read a string as a named range on Google Sheets

1 Answers1