0

Is there a way to name a range so that it doesn't automatically change when rows are added in between?

Ex: Range1 = 'Sheet1'!K5:K36 Add a row between rows 6&7 and now this automatically changes to Range1 = 'Sheet1'!K5:K37

When i go into the named range sidebar, i've tried to change the formula to say: Range1 = 'Sheet1'!$K$5:$K$36
but the $ go away immediately upon hitting ok

| improve this question | |
1

You can do this using the indirect function. If cell A1 contains the first row of the range (5 in your case) and A2 contains the last (36), if you are looking for the sum of the range you can use the following formula:

=SUM(INDIRECT("'Sheet1'!k"&A1&":k"&a2))

If you add more rows to your range, the formula will not change its results.

| improve this answer | |
  • I cannot use a function to name a range, but this did give me the idea to pull the range to another sheet using a query for only certain cells (or use the indirect function). Then i'll name the pulled data's range. This should work. Thanks! – Thomas Pribicko Nov 27 '18 at 16:53

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.