Forum Discussion
Solved
I Don't Get Named Ranges
I just don't get named ranges & how to use them.
Let's make this simple: Let's say I have numbers in Column A, and let's say I have a formula in Column B that references a cell from Column A. For example:
A5 might contain "100", and
B5 contains "A5+10". (So, B5 contains a value of "110".
Let's say that I want to name the range in Column A. Let's call it "MyColA". I know how to name the range (Formula - Define Name).
My question: What does the formula in Cell B5 look like? I figure I'll have to use "MyColA" in there somewhere, but how do I specify Row 5 in the B5 formula?
Hi Jon,
You may select the range in column B where to enter formula, in formula bar =MyCol+5 and Ctrl+Shift+Enter (array formula)
3 Replies
Hi Jon,
You may select the range in column B where to enter formula, in formula bar =MyCol+5 and Ctrl+Shift+Enter (array formula)
Thanks you, Sergei. That seems too easy, but I got to admit that it works.
I have a follow-up question. In my simple example, and in your sample table, there are only a few rows. In reality, I have nearly 2,000 rows. I can cobble together the formulae for the first row of data. Is there a simple way of filling the remaining rows with the formulae, other than a cut & paste of a honkin' big range?
Hi Jon,
If with named range you may select all your rows in column B, includes first ones with formulas, and just press Ctrl+Shift+Enter.
Alternatively, without named range, you may add into B1 the formula =A1+5, after that select entire range in column B (let say B1:B2000) and press Ctrl+D to populate the formula down.
