Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Creating a current date line on a graph

I have a simple line graph showing actual values of cash balances along with forward projections. It would be most useful if the graph could show either
The current days value as a highlighted value
or
A vertical line that will move on a daily basis to show where the values are in respect of actual and projected. (this would appear like the vertical line on some TV guide menus)


Post your answer or comment

comments powered by Disqus
I am trying to create a graph with time along the bottom axis and units built on vertical axis. Each month I want to be able to put a straight vertical line to indicate the month for reporting. I can do it by using a picture or draw a line on, but someone told me you could create a series?

Hi everyone,

Does anyone know how to create a random date? I'm studying for the bar exam and want to take 3 of the past exams each day starting on Monday until I take the exam on July 26. I'm attaching the worksheet as it currently stands. I'd really appreciate any help on this.

Thanks in advance,
Rob

Excel 2010 (or earlier is fine too). I have a time series line chart, bi-annual, where the data being plotted is consistant up to an event happening, drops significantly at that point in time, and then proceeds consistantly at it's new value. I'd like to drop a vertical line at the date of the event. Is this possible? TIA

Hello Everyone - I am a new member and not too familiar with the different formulas within excel so I was hoping to get some help.

In my excel spreadsheet, I have a certification date column, and an expiry date column. A certification expires 13 months from the date of certification.

I need a formula which would automatically create the expiry date based on the certification date plus 13 months. Then, whenever I update the certification date, the expiry date will automatically be updated as well.

So if A1 (Certification date) is 06-Jan-2010 then I would like B1 (Expiry date) to automatically display 06-Feb-2011.

Hope this is clear.

Thanks,
Michelle

I can create a bar/line combo chart...but I don't know how to create a
stacked-bar/Line combo chart (particularly on two axes).

Little help?

Hi,

Hope i explain clearly here...what i hope to do is create a work rota based on a set rotation:

Basically for example

Joe Bloggs is working 2 weeks on 2 weeks off. His first day of work is 1st January 2012. Populate his rota for the next year or two.
John Smith is working 2 weeks on 2 weeks off. His first day of work is 2nd January 2012. Populate his rota for the next year or two.

I have lots of guys on 2 on 2 off rota's but starting on different days through the week. I want to be able to firstly get the data in a sheet then i can play around and create some histograms and reports to see hoe many people i have working at any time and maybe look at 14th October 2012....is joe bloggs working?

I really dont know how to go about it....any help/pointing me in the right direction would be much appreciated.

Ive used excel a fair bit including basic macro's. Just dont know how to start this one.

Thanks

Chris

I would like to create a running count based on different criteria. What formula would I require in the C1 and copied down to achieve this?
Thanks

How do I create a new shortcut icon on the Open Dialogue Box?

When I press Ctrl-O to open a new file, the Dialogue box opens with 5 icons in the left column consisting of History, My Documents, Desktop, Favorites, and Web Folders. I would like to add or change one of the icons to point to c:/files. Any input on how to do this?

Thanks, Christopher

I want to allow a user to enter a data value in a cell, then create a row
listing based on looking up the user entered value in a table array.

I have the following sample data listed on the "Data" tab of a workbook.

ASR TABLE
AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType
ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN
ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN
ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN

The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2.

Thus, on a second tab, named "User Entry", the user will enter a SecIDKey
value in a cell (A1), and then I want the formula to create a row listing of
the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4).

I've seen several posts that hint at how to do this, but haven't found one
yet that explicitly defines how to accomplish this.

Thanks in advance for assistance.

--
Thanks,
Jen

How can I create a league table based on results.

I want it to look like the picture and the statistics to be updated by functions offcourse. :-)

Hi all
I would want to create a "sensitive regions" image on a form. For example,
it would be a european map where the user could select each country.
I know I could do it on a sheet using transparent shapes (one for each
country) on top of the image, but how could I do it on a UserForm?

Thank you

I have a line chart with various lines of data spanning past and future months, but I'd like to put a vertical black line on the chart showing the current day for reference. I'm creating the chart from a Macro which is working great, but I can't find any way to add just a line for the current date. I really thought this might be a common problem, but I've yet to find any questions or answers related to it. Also I know it's possible as I've seen it done in other Excel spreadsheets, but I don't have access to any of them to see how they did it.

Update: I've created a sample Excel spreadsheet with the logic I'm using to create the charts in the Macro, but nothing I've tried has come even close to creating a vertical line on the current date to show the current date on the chart. If someone knows how to do this and can demo it on this spreadsheet along with any other notes I'd appreciate the feedback.

Update 2: I found this showing how to add a line for current date - http://peltiertech.com/Excel/Charts/...ertSeries.html - but given I'm using Excel 2010 I'm not sure how to transpose this from the earlier version. The steps it points out don't seem to apply anymore.

Take care --

Sam

How do I create a Red vetical date line on my graphs. This line is linked somehow to the Cell that I have the current date in. This line will move when the current date changes. I have a picture of what I need to do but don't know how.

Removed e-mail address. You can put it in your profile where it's safe - Moderator

I have an XY graph which connects the series with a line. Lets say the
series runs left to right on the chart creating a straight horizontal line.
Is it possible to have multiple colours on that line? That is, I want to
colour certain portion of the line a certain colour and other portion another
colour. Is this possible

Hey all,

By activiting a button within my workbook several xy graph charts are generated. The data is taken from one sheet and the graphs/charts are generated within a different sheet with the plotted data completed.

The y scale is numerical i.e. 0-100 and the x scale is date Jan06 - Jan08 (monthly intervals). Presently I am placing a 'timeline' on manually - this line represents the current date when the graph is plotted.

My request for help. Is there a way to script this into my current VB where it can verify Date() then Plot a line accordingly?

Help and advice much appreciated.

I have several columns to work off of. The first two are dates (start date
and a finish date). I also have an hourly grosse dollar profit (GDP) column.
What I want to do is add the hourly gross dollar column if the dates fall
within a certain guideline. For Example:

A B C D (GDP) E (date started) F
(Estimated Date end)
1 - John Doe $12/hr $20/hr $8/hr 12/1/2005 2/1/2006

I want to show the grosse dollar amount for John in the month of December,
but only for the month of December. I'll then want to show the grosse dollar
amount for John in the month of January, and only for the month of January
(this, of course, would be in a different cell).

To get really tricky, I want to create a rolling commission calculator based
on the start and end dates. The calculation would assume 8 hour work days,
but the work week would need to be based solely on Monday through Friday (and
exclude certain holidays). Basically, if I were to open the spreadsheet in
February, it would forecast an estimated amount of commission to be paid out
at the end of that month of February. In another cell, it would also show
what the estimated commission should be for the following month (March). If I
were to open it in March, then the commision calculator would show what we
have for March (for the current) and April (for the next).

So far I can calculate the estimated commission, but I don't have a way to
base it on a calendar. I can only say that if every person were to start
working today, and go exactly one month, then here is what that commission
would be. As you can see, it's not all that practical.

ANY SUGGESTIONS?!?!?!

I can't figure out how to create a formula to give me a number using the
current date based on the month and stop calculating. So lets say the
current date is May 04/06 and I want the cell to show the number 4,
then 5 when its May 05/06 and so on. Well I'm using a formula that
does that, which is =Day(Today()). However I need this formula to stop
calculating at the end of the month. So if its May 31/06, then the cell
should reflect 31. But once the date is June 01/06 then my number is
back to 1. I need it to only calculate based on the month I assign to
it. I've been playing with this for ever and can't figure it out.

--
Renz09
------------------------------------------------------------------------
Renz09's Profile: http://www.excelforum.com/member.php...o&userid=34069
View this thread: http://www.excelforum.com/showthread...hreadid=539166

Hi, I'm creating a database of ages on people. What I need is to create
a formula that will tell me how old they are on the current day; i.e. if
a Person A's birthday is 2/27/80, then the Age Column will read "25"
today (2/26/06). But when I open the file tomorrow (2/27/06) it will
change to "26".

I.e. I don't want to manually have to update the Age Column. Is there
a formula to do this?

So far I've come up with Column A=Birthday, Column B=Today's date (with
the formula "TODAY()") and when I subtract A from B, it gives me a year,
where the age is the last 2 digits. How can I take that number and
convert it into the person's age?

Ex. 2/26/2006 - 8/18/1967 = 7/11/38. While the 7/11 is superfulous, 38
is the age. But how do I convert that number to state that?

--
Fidelio1st
------------------------------------------------------------------------
Fidelio1st's Profile: http://www.excelforum.com/member.php...o&userid=31956
View this thread: http://www.excelforum.com/showthread...hreadid=516761

Hello all,

I have a time sheet template. I have to mail these time sheets in to my
supervisor on the 15th and the last day of every month. I have code in the
template (and consequently on all timesheets based on this template) that
will pop up reminder messages based on the current date in relation to the
date the time sheets need to be emailed.

What I would like to know is if there is a way to set a constant, like
PayPeriodDate, when I create a new file from the template. That way, when I
email it to my boss, he won't get the reminder messages popping up on him
when he opens it after the Pay Period Ending Date. I also want to use this
for some other automated/data entry text boxes that I don't want him to have
to deal with.

Thanks for any help anyone can provide,

Conan Kelly

I would like to create a mean line for a bar graph with a data series that
includes 5 categories. The trick is that I would like to create the mean line
using only 4 of the categories (the fifth is don't know). Any ideas of how I
can select only 4 of the 5 to create a mean reference line? Thanks.

Hi Guys,

Firstly, I want to thank you guys for this great site. I am an Excel newbie and this place has really helped me out. You guys also have an excellently moderated forum which makes things easy to search for.

Here's my problem

I am trying to autmoate a support call stats sheet to allow me to automate the creation of reporting tables and graphs. Each row represents a separate call

I have created a dynamic range called MYR2 which selects rows containing the text "9/25/2005" in column A:


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Column C lists a categories for each call.

I have created a SUMPRODUCT forumla in order for me to count a specific category per the specific date:


	VB:
	
=SUMPRODUCT((MYR2="9/25/2005")*(C2:C7="Config/Setup Error")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This all works. What I want to do now is create another dynamic range for column C. However, I want the range of rows to be computed based on the range of rows in MYR2. I have to manually I have tried using (probably incorrectly) MATCH, and the dynamic range within dynamic range, but they didn't work (but I may not be doing them properly).

I hope this is clear. I can upload a spreadsheet if you would like.

Thanks for your help

Andre

I can't figure out how to create a formula to give me a number using the current date based on the month and stop calculating. So lets say the current date is May 04/06 and I want the cell to show the number 4, then 5 when its May 05/06 and so on. Well I'm using a formula that does that, which is =Day(Today()). However I need this formula to stop calculating at the end of the month. So if its May 31/06, then the cell should reflect 31. But once the date is June 01/06 then my number is back to 1. I need it to only calculate based on the month I assign to it. I've been playing with this for ever and can't figure it out.

Is it possible to rename in the legend the trend line on a graph.

Currently, the legend for the graph says "3 per Mov. Avg. (2010 Unit Sales)"
I would like it to say "3 Month Moving Average"

I do not see an option to rename a trend line in a graph.

I can of course do the 3 month moving average calculations and add a "Series name", but was hoping to be able to swtich between 3, 4, 5 moving average or even polynomial trends at the click of a button. (Yes, i can build that function into a new series, but seems like a lot of work...).

I need help in creating a formula to have pie chart created based on a date range entered in one tab from data in another tab.

If you look at the sample attached, I have created a detailed example of what I am wanting to do.

Is this possible?

thanks,
Nick


No luck finding an answer? You could always try Google.