[[email protected]]

I'm trying to automatically fill some cells in a spreadsheet that's
linked to a MS Project file. There are 5 cells - the first one is the
cell linked to Project so it will show the current figure. In the
other cells I want to show the figure depending on the date, 1st Qtr,
2nd Qtr etc. I thought I had the 1st qtr sussed with:-

=IF(TODAY()<DATE(2009,4,1),J5,"")

but of course as soon as I get to 1st April, the cell will go blank,
which I don't want as the data is tracked on various charts. I'm also
unsure how to do the other qtr's.

Sorry if this is blindingly obvious, I'm quite new to this so any
advice will be greatly appreciated!

[Mike H]

Hi,

I'm not entirely clear what your trying but maybe this helps

=CHOOSE(ROUNDUP(MONTH(A1)/3,0),B1,B2,B3,B4)

This wil choose which quarter the date in A1 is in (1,2,3 or 4) and from
that return the contents of cell b1,b2,b3 or b4

It can easily be modified to look at the cirrent month

=CHOOSE(ROUNDUP(MONTH(TODAY())/3,0),B1,B2,B3,B4)

Mike

Mike

" wrote:

I'm trying to automatically fill some cells in a spreadsheet that's
linked to a MS Project file. There are 5 cells - the first one is the
cell linked to Project so it will show the current figure. In the
other cells I want to show the figure depending on the date, 1st Qtr,
2nd Qtr etc. I thought I had the 1st qtr sussed with:-

=IF(TODAY()<DATE(2009,4,1),J5,"")

but of course as soon as I get to 1st April, the cell will go blank,
which I don't want as the data is tracked on various charts. I'm also
unsure how to do the other qtr's.

Sorry if this is blindingly obvious, I'm quite new to this so any
advice will be greatly appreciated!

[OssieMac]

A few questions.
What do you want it to return if not a blank?
What is the value in J5?
What do you want returned if it is the 3rd quarter or 4th quarter etc.

Not sure if it will help but the following formulas return the date of the
1st day of quarters.

=EDATE("1/1/2009",0) Returns Jan 1 2009
=EDATE("1/1/2009",3) Returns Apr 1 2009
=EDATE("1/1/2009",6) Returns Jul 1 2009
=EDATE("1/1/2009",9) Returns Oct 1 2009
=EDATE("1/1/2009",12) Returns Jan 1 2010

You can use a cell containing an actual date in lieu of the date in double
quotes and I personally think it is a better way to do it.


--
Regards,

OssieMac


" wrote:

I'm trying to automatically fill some cells in a spreadsheet that's
linked to a MS Project file. There are 5 cells - the first one is the
cell linked to Project so it will show the current figure. In the
other cells I want to show the figure depending on the date, 1st Qtr,
2nd Qtr etc. I thought I had the 1st qtr sussed with:-

=IF(TODAY()<DATE(2009,4,1),J5,"")

but of course as soon as I get to 1st April, the cell will go blank,
which I don't want as the data is tracked on various charts. I'm also
unsure how to do the other qtr's.

Sorry if this is blindingly obvious, I'm quite new to this so any
advice will be greatly appreciated!

[[email protected]]

Apologies to both Mike and Ossie, as I said this is all new to me and
I clearly haven't explained what I'm trying to do properly :( Let's
see if I can show exactly what I need...
This is the current set up:-

Cell J5
Current - This is the cell that's linked to MS Project, and in this
case shows the % Complete of the task

Cell J6
Qtr 1 - This is where I want to show the % Complete up to and
including 31/3

Cell J7
Qtr 2 - % Complete from 01/04 to 30/06

Cells J8 and J9
Qtrs 3 and 4 - % Complete from the relevant dates

What I want the formula to do is update depending on the current
date. So Cell J6 will update when the user updates the links from 1st
Jan to 31st March, J7 will begin to fill from 1st Apri lto 30th June,
and so on. I need the cells to stay at a fixed figure, within the
quarter, as the figures are being used in charts that graph the
progress by Quarter.

Cell J5 will constantly change, but that's fine as there's another
chart graphing overall progress of the tasks.

The CHOOSE(ROUNDUP) option only works if I'm looking at more than one
column for my results - I think?!?!

Am I going about this the right way? Is it possible to do something
with the link to Project? At the moment, the link in J5 reads:-

{=MSProject.Project.9|'C:\Data\MMMP\Leicester 2009v2.mpp'!'!LINK_11'}

Can I add something to the link using actual dates rather than TODAY
()?

Thanks for your suggestions so far - not your fault I'm hopeless at
explaining things!

Dave