dynamically update pivot table data source
I am trying to determine if there is a way to dynamically
update the data source underlying a pivot table. For
example, I have a data sheet that grows by one year each
month and this sheet populates a pivot table. Each time I
add a new row, I have to redefine the source data range
for the pivot table. Can this action be done
automatically? I have seen something like this to update
graphs using the series function... just not for pivot
Thanks in advance.
You can use a dynamic formula to define the source range for your Pivot
Table. There are some instructions here:
...split combined Time Date cells
from file dump have combined date time cells eg "14/04/03 14:20" (value
37725.59722). Want to perform time analysis so need to split to 2 separate
cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a
fnc to do this? (Currently convert cell to value, then fncs trunc & cell
less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively)
With your date/time in A1,
B1= Int(A1) Format as date
C1=A1-Int(A1) Format as time
Drag the formulae down as far as required.
Norma...no "update account manually" when creating redit card
In Money Plus Premium I try to add a credit card account (Diners Club) and
"update account manually' is never an option. It just tells me that I have
to sign up for the MSN oline service. Even if I cancel, it will leave a
"complete setup for online services" and when I download a statement from
their site the account is not an option to get that statement imported into
(which worked on Money 2005).
Is there an option to set it up to get manual updates? I can't imagine the
only option being chosing "the bank is not listed here".
O...Rank items, select one start date, have remaining dates follow based on rank
I have a list of projects. I have one start date. Each project has a
varying amount of time to complete. I want the user to rank the
projects based on importance (example when it will ship) and then,
based on the individual times required to complete the rest of the
projects, excel will automatically generate start and complete dates
for the remaining projects. Need to know how to do this.
rob normerica's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26316
View this t...Automatic range update of Graph
Can some one tell me if it is possible to automatically update the
(date) ranges in a graph?
Now I have to extend the ranges manually each time there is a new range
Thanx in advance!!!
Have a look at Tushar Mehta's Dynamic Ranges site.
Or Jon Peltier's site on Dynamic Charts
Gord Dibben Excel MVP
On 30 Jun 2005 06:59:31 -0700, email@example.com wrote:
>Can some one tell me if it is possible to ...Calculating age from date of birth and ignoring if blank
I've used the following formula to calculate a person's current age in years
based on a previously entered date of birth:
=ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)
However, if I haven't entered any data in the date of birth column, the
result cell shows the age "110", which throws out other calculations. Is
there any way to tell Excel not to enter any data if the date of birth is
Try this : =IF(P2="","",ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0))
"news.eternal-september.org" <firstname.lastname@example.org> w...Save prompt when no changes made
I have a spreadsheet which always asks whether I want to save the changes
I've made. But I haven't made any. I can open the file, immediately close
it, and still get the save prompt.
How do I track down what's causing the save prompt? The workbook has about
10 sheets and a few macros, if that makes any difference. I'm using Excel XP
on Windows XP.
Please reply to newsgroup, not e-mail
Some functions in excel are "volatile", they will re-calculate every time
excel is opened, this will make the worksheet change even if you don't mak...Dates #2
When I enter "august25" Excel automatically changes the entry to "Aug-25".
How do I change it so this will not happen?
precede your entry with an apostrophe
> When I enter "august25" Excel automatically changes the entry to
> "Aug-25". How do I change it so this will not happen?
...Saving All Documents when closing XL 2007
I am upgrading to XL 2007 from 97 and having a problem which sounds
simple but is annoying as hell. I have as many as 150 XL files open
at one time (all linked to one another) and with office 97 I would
exit XL and then be prompted to either save the files one at a time
or "yes to all". The "yes to all" option does not appear when I follow
the saem steps in 2007, is there an option somewhere where I can
enable this or has this feature been dropped?
When I have several workbooks open with unsaved changes and then close
Excel, the save prompt for the first workb...Date Increment [If A Sunday]
Cell B3 is formatted as date [ddd dd mmm yy] and I select
a date from a dropdown in B3 [set up thru Data Validation]
I want cell B5 to equal B3 unless B3 is showing a Sunday,
then I want B5 to increment to the Monday after the
Sunday chosen [if B3 = Sunday then B3 +1 ,if you know
what I mean]
I've tried MATCH function [against a list of all the
Sundays in the year] and IF statemnts but I'm going round
Any help greatly appreciated,
In Cell B5 (formatted as date) enter:
"Bob Beard" <email@example.com...Disable Date/time field OnLoad
Quick question, which I would be most grateful if anyone could help with.
I am trying to disable a date/time field as part of a form OnLoad. However
although the field is greyed out I am still able to populate data into the
field and save the date on a save.
The code I am using is
Is it just that this method does not work on a date/time field?.... or have
I missed something.
Thanks in advance
Could you try it with crmForm.all.new_mydate.Disabled=true;
Could be you need a 'capital' D!
Merijn van Mourik...Computer found updates
When I first activated windows I had just bought a computer and decided to
just take the hard drive out and use it in my old one (which is pretty brand
new) it was just dropped and the hard drive fried. When I activated windows
I used the product key of the new one I just bought which is a Dell instead
of the one I put it in which is an Emachines. So all the drivers were not
loaded. So I went and had network adapters added and got in. Well it showed
a list of automatic updates which I did install. Now my system hangs and
reboots itself. I can get in by going to safe mode wi...Money 2004 download update error (SMRTINET.INI)
Every time I connect to download money updates I get this error saying:
"Money encountered an error processing the downloaded file ('SMRTINET.INI').
It is not a serious error and usually if you do another update immediately
it goes away.
Microsoft MVP - Money
For UK tips & fixes see
For wishes or suggestions see
or for UK wishes email to firstname.lastname@example.org.
"Armando Torres" <email@example.com> wrote in message
news:u5...Saving in Excel #2
When I click File, Save in Excel, the default choice is set as "Save as
Copy". I would like to change the default to "Overwrite Changes". How do I
I think you already have two instances of Excel open or the same workbook open.
Make sure you only have one instance open then you can overwrite the file,
also not "Save As" but "Save"
> When I click File, Save in Excel, the default choice is set as "Save as
> Copy". I would like to change the default to "Overwrite Changes". How do I ...Latest Update 12.2.3 crashes WORD
Operating System: Mac OS X 10.5 (Leopard)
I had no problems with WORD (Mac 2008) until my recent update (12.2.3).... twice in as many days it has crashed on me - once without retrieving my work! <br><br>What's up with that?!? <br><br>Any solutions? Can I go back to the old update version I had? <br><br>thanks.
Well, 12.2.3 may be *your* most recent update, but it isn't the most recent
for Office 2008 :-) I'd suggest that you also apply the 12.2.4 update as
well as whatever is most current for Leopard -- t...the date format is so confusion....
Hi all, has anyone got a problem about the date format in excel document???
We are using business portal 4 and GP 10. Some queries (from BP4) generate
report with date column on it. but the format of the date is so confusion.
and some of the date is in US type, some of them in Aus type!!! It's soo hard
to read... does anyone got any idea about how to solve the problem???
I'm not a "date expert" and I'm not a Business Portal expert J.Joe, but I
have heard that date formats sometimes come from the workstation setup under
control panel and regional settings...Add future date with a button
I have a "last calibration" field on a form that is updated through
the calendar control. Below that, I have a "Next Calibration Due"
field that I would like to populate 1 year forward from the last
calibration field. I used the expression builder "=DateSerial(Year
([StartDate]),Month([StartDate])+12,Day([StartDate]))" which works,
however that field remains blank in the report.
I thought perhaps just adding a button beside the "next calibration"
field to calculate would be easiest.
Thanks in advance,
Take a look at the Dat...Email notification at Windows XP Login will not update
All the time when I close Outlook 2003 without reading
all unread messages in Outlook the notification of unread
messages at Win XP login reminds for unread messages
without updating whenn all messages are read. After I
mark or read all messages the notification will not
updated nor be removed.
Nice idea but buggy? I cannot see anyway to have this
alert be cleaned. Well done!
I have now a total amount of 23 unread messages at my Win
XP login and no unread messages in my Outlook. Thanks for
any help in advance!
...Find/Replace Date Question
Using Excel 2003.
Problem replacing a series of dates having a date format of dd/mm/yy. I want
to replace all dates ending with /05 with /06. I enter in the Find box /05
and in the Replace box /06
Error is "Microsoft Office cannot find a match"
I never had this problem with earlier versions of Excel.
If those are real dates, try replacing 2005 with 2006.
> Using Excel 2003.
> Problem replacing a series of dates having a date format of dd/mm/yy. I want
> to replace all dates ending with /05 with /06. I enter in the Find box /05
> ...Word unexpectedly quits when saving
I recently downloaded 10.1.9 update and my word is still quitting when
I try to save. I've tried to no avail to locate the 10.2 download or
another solution. Thanks in advance to anyone who can help!
Consistent and reproducible problems in Word usually respond to one of
the standard troubleshooting measures, which you will find listed here:
The Diagnosis page lists some things to try first for "quit on save"
> I recently downloaded 10.1.9 update and my word is still...how can you set up excel to auto-save a document?
That would depend upon your version of Excel.
There is no Autosave in XL2002 and 2003 as there was in earlier versions.
Autorecovery from Tools>Options>Save is it. This is not the same as Autosave
which made true incremental saves at intervals and alerted you before saving.
Autorecovery just saves a temporary file which it deletes if Excel closes
normally without incident.
BTW.....Dave Peterson reports that he tried an earlier version of Autosave.xla
in XL2002 and it seemed to work fine.
I have also tried the Autosave.XLA from XL97 and does the job.
To download the 97 ...Julian Date
How change a julian date to "normal" date ?
See http://www.cpearson.com/excel/jdates.htm for details.
"Luis Felipe Alcantara" <firstname.lastname@example.org> wrote in
> How change a julian date to "normal" date ?
> Tks advance,
> Luis Felipe
For a Julian date in A1.
Example: A1: 06127
In that example, B1 returns 38844 (May 7th, 2006)
Note: Excel will engage it's def...GP 10 upgrade
I am trying to upgrade from GP9 SP4 to GP10. When I run the upgrade routine
it starts upgrading the DYNAMICS database, however when it gets to the Update
button and I press this, it then runs and does some updates but finishes
again at the same Update button. I cannot seem to get further than this.
If I choose to do the upgrade without converting user security I get past
this problem - however I do need to upgrade user security.
I have tried various things - and believe my problem must be due to user
One thing I also noticed is that when I run Security Reconcile ...)How Can I Suspend the Security Check for Programs Trying to Access E-Mail Messages? (Updated Message)
My system, is running WinXP Pro SP2. My version of Outlook is 2003. I'm
not using any third-party security on this computer for the moment. I am
using standard WinXP Pro SP2 security with Firewall on. Updates & virus
I'm running an Outlook Duplicates Remover. As a result, a message keeps
popping up alerting me to the fact that the duplicates remover is trying to
access e-mail addresses. That's fine ONCE! But when I click on the Yes
button to let it continue, the security protection insists that I limit the
time to a few minutes; 10 at most. That's...out of date items
I have a tools database with LastTestDate and NextTestDate fields, which
is conditionally formatted for approaching NextTestDate.
What I want to do now, is to run a query or report which tells me which
items are out of date.
I have a query which give me the NextTestDate:
So if the NextTestDate is, say today, then I would like to be able to
run a query which will sort out the items which are either out of date
or will be very soon.
Bob H wrote:
>I have a tools database with LastTestDate and NextTestDat...