up vote 0 down vote favorite

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

share|improve this question
up vote 0 down vote

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.

share|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 at 16:53

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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