Afternoon Everyone!
I'm not sure if someone's already asked this, but I couldn't find anything on this particular problem.
I have a table, whose cells are referenced by a rather long formula in another part of the worksheet. Is there a way to move the table without messing up the formula that depends on it?
Try changing the cell references in the formula to absolute by highlighting the formula in the formula bar then pushing F4.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
Yeah, as long as the formula doesn't have any volatile function references (eg, there's no INDIRECT functions in there), then the formula should automatically update if you drag & drop the function.
If you need to do something more extreme (eg, delete the table and recreate it on another sheet or something), then you can do something else:
"comment out" the formula so it won't react to any changes, and then re-activate it. Just put a single-quote before the equals sign to force it to assess as a text string.
will have a text string containing "=SUM(range)" inside it, not the SUM function operating on the named range, "range".
Please Login or Register to view this content.
So you turn the function into a static text string, shuffle the function around and re-up the named ranges or whatever,
and then remove the single quote so it's assessed as a function instead of a text string.
That's my advice.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
Thanks guys, but I dont want to move the formula, I want to move the table the formula references.
For Example, say I have a table that occupies D4 to H4 down to H9 and I wat to move it to Q4:U4:U9; if I make the references in the formula that uses the table absolute, will they update automatically to the new cell locations when I move the table, and also, would I need to copy the table and delete the original or cut/paste the table to the new location?
Thanks for the help. I tried cutting and pasting while keeping the references relative and it worked fine. The referenced cell values updated to the new location without issue.
Glad you were able to get it working.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks