OFFSET replacement to get rid of save prompt
Is there any other formula instead of OFFSET?
Because Excel prompts to save every time I close it even if I don't change anything.
Here's my formula: G5=OFFSET(G5,-1,0)+F5-E5
And if I use this formula, auto-update won't work in table: G5=G4+F5-E5
http://www.mrexcel.com/forum/excel-q...ve-prompt.html
http://www.ozgrid.com/forum/showthre...437#post712437
Last edited by RiP2; 05-07-2014 at 06:18 AM.
What exactly are you trying to do? If you have a regular formula that wont update, then you may have calc set to manual?
Last edited by FDibbins; 05-06-2014 at 05:38 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Maybe:
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I need something like this: http://office.microsoft.com/en-us/ex...003069811.aspxOriginally Posted by FDibbins
No, auto-update in table won't work while inserting a row between other rows because I shouldn't use "G4" in 5th row.
Formula:Please Login or Register to view this content.
Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!
Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
http://www.mrexcel.com/forum/excel-q...ve-prompt.html
Sorry, Done.
It doesn't still workFormula:Please Login or Register to view this content.
After adding a new row in 12th row, G12,G13,G14... will turn to: =F12-E12
Last edited by RiP2; 05-06-2014 at 06:03 PM.
Works for me. See the attached example.
If it doesn't work for you, I guess you'll have to find an alternative solution.
Seeing as you've expressed your gratitude so well, I'm out.
Good luck with finding a solution that meets your needs.
Regards, TMS
You're right, seems working because there's no H4 so should be ok.
Just wonder why it didn't work in my sample table last night
Then I tried OFFSET again and it didn't work too, so I found that it's my problem.
Thanks for the INDEX formula
Edit: Working great! Thank you very much
Last edited by RiP2; 05-07-2014 at 06:17 AM.
You're welcome. Thanks for the rep.
Your welcomeYou're welcome. Thanks for the rep.
Do you know if it's possible to use a smaller range than the whole G?
I tried this but it didn't work: =INDEX(MyTable[Column7],ROW()-1)+F5-E5
I guess you can, but why would you? It does the job and it's not actually using the whole column, just the cells it needs to. No performance hit. You could use use $G$1:$G1000, say, but why?
I think that MyTable[Column7] will be referring to the data in the column only, hence the count won't start from row 1, as $G:$G does.
Regards, TMS
Thanks again
Again, you're welcome.
Sorry, I forgot to ask. Why did you use N(), what's it?
=N(INDEX(G:G,ROW()-1))+N(F2)-N(E2)
It takes the numeric value of a cell. If the cell has text in it, it returns zero (0). That means the formula can be dragged up to row 2 where [FORMULA]=INDEX(G:G,ROW()-1)["/FORMULA] would be referring to row 1 ... the header row which will have text in it.
Not really necessary for the other too but I like to be consistent
Regards, TMS
Oh, because as soon as I entered it, I got no #VALUE error like the past
Before, I had to delete the other cells in that columns. Very useful!! Thank you very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks