My issue is that I have a chart that I need to change based on the values in a certain range. I originally built the chart with the date values being keyed in. Now when I change those keyed cells to equal another dynamic cell it messes the whole chart up.
Here are pictures showing what happens when I change the keyed in date value to equal another cell.
Good.JPG
Bad.JPG
Without an actual spreadsheet, showing exactly what your problem is... it is unlikely that you will find anyone here who can help.
So... Please post a spreasheet (without confidential info) showing your problem.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Test.xlsx
Ok, I have attached a file. So the first chart which is on rows 4 and 5 is what I am messing with. It is pulling the data from the table down on rows 34-37. If you try and change cell H35 to equal H39 it messes up the chart.
The problem is simple, but... H39 is not stored as a date. However, all my usual tricks for forcing excel to recognise it as a date are not working...
FINALLY. It's all because of the US date format. i converted the cell to MM-DD-YYY format and added a zeo in front of the day andmonth in H39. Now it's fine. see sheet.
Forgot the sheet...
Thanks for the help. However, I have kind of changed my strategy and it seems your method does not work. What if I wanted to link it to a cell that contained a vlookup? When I do that it does the same thing as before.
Once again, post an example... The date that was in H39 wasn't a date... How are you populating the lookup table?
TestNew.xlsm I have put some call-outs in there to try to explain.
Yes, it's the same issue. The dates look like dates, but they aren't. I can't see your source data - it's on your PC and I don't know where the dates came from. However, the "dates" in your sheet do seem to be very reluctant to behave like dates. So i've hit them very hard with a horrible equation that breaks them down into bits & reassembles them as dates. It's ugly, but it works. i suggest you use this to force our dates to act as dates on the dash calcs sheet...
i tried following this (as a much easier alternative) and didn't get anywhere!!
http://fiveminutelessons.com/learn-m...lue-date-excel
Thank you so much! And you are not kidding, that is one massive formula.
Try it out in a range of circumstances. If all is OK, please mark the thread as solved and (preferably) click the Add Reputation button at the foot of this post.
One more thing. How do you import the data into your source sheet? there are paste options that can help Excel realise that it's dealing with a date.
The other alternative is to persuade your government to adopt the same date notation (dd/mm/yy) as the rest of us
Just noticed you're in SLC. We're heading out that way in September - starting from SLC, going S and then NE back to Denver, for a good long holiday.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks