How to add PivotTable drop down selector to another tab?

[John1889]

To better manage our employees on their life insurance plans we need to create a column in excel to calculate todays date and their date of birth and show the age. Any suggestions would be really appreciated.

[BurgoEng]

You can use the DATEDIF to calculate a person's age. For example, the formula below will calculate the age of a person as of the current date, where BirthDate is the person's date of birth.

=DATEDIF(BirthDate,TODAY(),"y")&" years "&DATEDIF(BirthDate,TODAY(),"ym")&" months "&DATEDIF(BirthDate,TODAY(),"md")&" days"

You can find much more in the following link: cpearson.com

[John1889]

Thanks a lot for posting the link, it was very helpful.

[AlpineMan]

I am trying to create a dynamic range that will return the last/bottom 12 rows from a range. I need to produce charts which show performance on a rolling 12 month period, I currently have a table which is set out something like this:

Date Actual % Target
Jan 07 67% 90%
Feb 07 77% 90%
Mar 07 52% 90%

Every month new figures are added to the bottom of this table, I would like to be able to pick up the last/bottom 12 entries automatically so the chart gets updated and we still have all the previous months data.

[AdamU]

How can you prevent a user from clearing a cell value with the spacebar? Is there a way to trap the event and throw an alert or simply disable the spacebar for that cell?

[markbenedict]

Can anyone tell me the code to run an Access Macro from within Excel (via an Excel Macro) let's say that: The Access Database is C:\Accessdb.mdb and the macro is runmac

[lisamapiatan]

Whenever I try to make a graph by selecting a row or column of pivot table data, Excel creates a whole new tab with a pivot table chart on it. Can this default be turned off? I'd like to be able to reference the cells directly from a graph on the same page as the pivot table.

[BurgoEng]

Take a look at the OFFSET function along with COUNTA.

You should end up with something like...

=offset(Sheet1!$A$1,counta(yourrange)-11,0,12,number of columns in range)

[BurgoEng]

If you just want to prevent users from being able to alter a cell (emptying data, replacing data with a space character or otherwise making changes), check out Tools > Protection > Protect Sheet. If there are any cells that you want not protected, then select those cells and go to Format > Cells > Protection and uncheck Locked before turning on sheet protection.

[BurgoEng]

Try this code:

Dim objAcc As Access.Application
Set objAcc = New Access.Application
With objAcc
.OpenCurrentDatabase "C:\Accessdb.mdb"
.DoCmd.RunMacro ("runmac")
.CloseCurrentDatabase
.Quit
End With
Set objAcc = Nothing

If you get any error message then go to VB Editor, go to Tools > References and check the box beside Microsoft Access X.X Object Library (the X.X represents whatever version number you have).