LinkBack URL
About LinkBacks
I want to have the formula in a cell always constant. I know I can use "$B$5". But when I enter some value into that particular cell the formula is getting erased.
Can someone please help me with this?
I want to have the formula in a cell always constant. I know I can use "$B$5". But when I enter some value into that particular cell the formula is getting erased.
Can someone please help me with this?
A cell can only contain a formula OR a value.
It cannot contain both.
Jonmo1 is correct - you can only have either a formula or a value in cell B5. What are you trying to achieve?
Trish in Oz
-------------
A problem well defined is a puzzle half solved
If you attach a sample of your workbook it will be easier to find a solution.Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
http://www.excelforum.com/faq.php?fa...b3_attachments
Hi Tuph and Jonmo1 thanks for your response,
I'm trying to create three columns which are start date, end date and the count.
I need to have them dynamic where when one of the values gets updated I want the other two to update themselves.
For example: Start Date End Date No of Days
1/1/2014 1/10/2014 10
1/10/2014 1/15/2014 5
and so on...
If I change the end date 1/15/2014 then the no of days should update itself or If I change the no of days then, the dates should get updated depending on the change.
Please help!
Thank you
that sounds like a simple datedif formula under # of days (assume it is in C2) =datedif(a2,b2,"d") and it will recalculate.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
You're not going to be able to do that with formulas. It will require VBA code.Originally Posted by balu.soln4biz
It's one thing to calculate the number of days based on the 2 date entries.
But to then also have the Date's update if you change the number of Days (replace the formula by hand), that won't work.
Is a VBA solution useable in your book?
If so, can you give more details.
Which date (start or end) should change when you change the 'No of days' value ?
And which version of XL are you using?
One way, perhaps:
Start C1: =IF(COUNT(B1:B3)<>2, "", IF(ISNUMBER(B1), B1, B3-B2)) Days C2: =IF(COUNT(B1:B3)<>2, "", IF(ISNUMBER(B2), B2, B3-B1)) End C3: =IF(COUNT(B1:B3)<>2, "", IF(ISNUMBER(B3), B3, B1+B2))
Enter two of three values in B1:B3.
Entia non sunt multiplicanda sine necessitate
The start date can be constant.. but the end date need to change when we change the number of days.. and vice the number days need to get updated when we change either of the start or end dates.
I'm using 2013 Excel..
If you can help me fix this that would be very helpful.
Capture.JPG
Try this code
Right click your sheet's tab, view code
Paste the following
Please Login or Register to view this content.
Last edited by Jonmo1; 06-04-2014 at 02:22 PM.
I have tried this code but I wasn't able to see the macro once after I save it.. Its very weird. Have you tried the macro in your excel?
Try this code
Right click your sheet's tab, view code
Paste the following
Please Login or Register to view this content.
It's not a macro that you would run from the macros menu (it won't be listed there).
It's an Event procedure that runs automatically when you change the values in the cells.
Make sure you follow the steps I posted
Right click your sheet's tab
Select View Code
Paste the code there.
Now when you change the values in columns B C or D, the appropriate values will be automatically adjusted.
That's working!! Amazing! Thank you very much Jonmo1.
You're welcome.
Jonmo its working fine but I have a small problem.
If you saw my screenshot, end dates on each row are related to the start dates on the immediate next rows. So when any of the columns are updated, its supposed to change the rest. But in this case other rows are static so when one of the rows is updated the others are going to be wrong if not updated!
You said..
I would put a formula in B3 and filled down
=D2
And change the code to
Please Login or Register to view this content.
Yes, the first start date is constant and I have done the same but ( b3=D2).. When I change the number of days, its changing the end date on the same row but the number of days and end date on the next row( and also the following rows) is not being updated. so the data looks incorrect!
Sorry, but it's making my head hurt trying to think about how to handle all that.
I think you're making this harder than it has to be.
I honestly think you should re-think the purpose of the sheet.
And pick ONE, make it so you can
Either:
1: Change the end date, and make the number of days (and next row start date) adjust via a formula.
OR
2: Change the number of days, and make the end date (and next row start date) adjust via a formula.
Not both.
Jonmo1 really appreciate your help, Imagine my situation here and its not the entire problem, there are dependencies for each row again. I have to get it work with the dependencies as well. There should be another column with the dependent rows where the rows changes their dates depending on the dependencies given.
Thank you so much for your time my friend. Have a great day!
You're welcome.
Sorry we couldn't achieve your goal.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks