To do this, you must have the Developer menu in your ribbon. Select The cell link is the location where Excel will place the index number of the item you select.
I am currently trying to make a plot of flow rate in a heating system vs. The only solution I have been able to find is to delete the formulas from the cells which should create the gaps.
Using an IF formula with "" as its value for the gaps makes Excel interpret the X-values as string labels (shudder) for the chart instead of numbers (and makes me swear about the people who wrote that requirement).
Alternatively, select the rows and under the Data ribbon select Group. the time of day from to , which causes lines to be drawn between and of the next day which extend across the chart and disturb the otherwise regular daily variation.
This inserts the ' '/'-' so you can hide/show the selection. There are many cases in which gaps are desired in a chart. Using the NA() formula (in German NV()) causes Excel to ignore the cells, but instead the previous and following points are simply connected, which has the same problem with lines across the chart.
If possible, I need this to be active for the cells C2: C30 ( D2: D30 for the date time) Using Excel 2010.
You could fill the dependend cell (D2) by a User Defined Function (VBA Macro Function) that takes the value of the C2-Cell as input parameter, returning the current date as ouput.Apparently, through sheer volume of content on the page and some dumb luck with the post title, I consistently do well for searches for “Excel dynamic named ranges” (long live the long tail of SEO! The kicker is that I wrote that post before I’d discovered the awesomeness of Excel tables, and before Excel 2010 had really gone mainstream.I’ve been meaning to redo the original post with an example that uses tables, because it simplifies things a bit.I have tried a lot of VBA codes and tricks and none of them works if a formula is entered in C2.BUT if I type a value manually in C2 the date and time is updated as needed.This is of course because a real value is entered/changes - where the formula stays the same, so to speak.