Fill multiple cells by entering data in one cell

[AlisonB]

Help!

I am setting up a timetable and would like to fill several cells
automatically by entering data in one cell.

Basically, I have a series of meetings to schedule, each of which has a
number of deadlines dates. For example, for a meeting on 21 Feb 2006,
there are deadline dates of 24 Jan 2006, 31 Jan 2006 and 9 Feb 2006.
All of the dates are on a worksheet with one meeting per row. On
another worksheet in the same spreadsheet, when I type in the meeting
date in a cell, I want the previous cells in the row to fill up
automatically using the deadline dates.

I've messed around with IF and Lookup functions without success. Can
anyone point me in the right direction?

AlisonB

 

[soxcpa]

A basic formula could solve this problem. For example, if the meeting
date is in cell C3, use the formula to create you deadline dates.

D3 =C3-24
E3 =C3-21
F3 =C3-12

This way, the deadlines are always set to 24, 21, and 12 days before
the meeting date, respectively. It can also work accross sheets.

D3 =Sheet1!C3-24
E3 =Sheet1!C3-21
F3 =Sheet1!C3-12

 

[AlisonB]

Thanks for a prompt reply. I can see where you are coming from, but
the problem with this idea is that the gaps between the deadlines are
not always the same. I think I need the solution to use the actual
dates in the cells, so that if I have to change a date the revised date
is reflected in the second worksheet. What I am aiming for is that I
click in a cell on one worksheet and reference it to a meeting date in
a cell on the other worksheet. This action then triggers the other
cells to fill with the deadline dates.

Hope this makes sense!

All advice much appreciated

 

[soxcpa]

Is there a constant gap between the deadlines? A week, month,
something? Is the deadline stored in another cell somewhere? You could
add an on_update event that changes certain cells to a different value
based on a change to a cell, but one would need to know what the
relationship of the proposed change is. (Increase the current cell by 5
days, add 3 days to the current meeting date, something...)

 

[soxcpa]

Is this what you are trying?
SHEET1:
A B C D
1 Dead1 Dead2 Dead3 MeetingDate
2 01/24/06 01/31/06 02/09/06 02/24/06
3 02/02/06 02/09/06 02/23/06 03/01/06

Then on SHEET2, you have assorted cells that need to change to based on
the meeting date selected? For example, when you change the meeting
date on sheet2 from 2/24/06 to 3/1/06, you want the DEAD2 for the
meeting on sheet to to change from 1/31/06 to 2/9/06.

 

[AlisonB]

Thanks again for your help. No, that is not quite it.

Your SHEET 1 is right except there is no set number of days between the
deadlines - these are set by other people according to their
availablity, and are subject to change. Access is limited to this
worksheet for a number of reasons.

SHEET2 is a Project Control table and has the same column headings
(plus a lot of others), but the relevant cells are blank until someone
enters a meeting date. Our Project Managers know when all the types of
meetings are due to take place. This worksheet is used as a summary of
the progress of projects. The idea is that if a Project Manager wants
his project on an agenda for a particular meeting, he types in the date
of the meeting he thinks he wants to go to and the deadline dates
autofill for him giving him the timescales for that particular meeting.
If he types in a date and finds he cannot meet the dealines, he would
over type the meeting date with later one and the deadline dates for
the later meeting will then show.

Hope this makes sense. I am open to ideas for a different approach as
it is early days and I can change how the spreadsheet is set up at this
stage if necessary.

Kind regards,

AlisonB

 

[soxcpa]

I think I understand what you arr trying to do.

On SHEET1, you have a list of meeting dates and their related
deadlines. This is protected so only people who actually understand
what each of the deadlines entails can change it.
On SHEET2, you have a "request to get on agenda" form of some sort
where a Project Manager can enter a meeting date, and the related
deadlines (pulled from SHEET1) corresponding to the typed date fill-in.
If these dates are unacceptable, the PM can change the meeting date and
new deadline dates fill-in.

This can be accomplished easily IF THE MEETING DATES on Sheet1 are in
SEQUENTIAL order.
You can us a VLOOKUP function in Sheet2 to find the related deadlines.
The in english general syntax for VLOOKUP is:
=VLOOKUP(What,Where,Column)
=VLOOKUP(MeetingDate, TableOfDatesAndDeadlines, ReturnColumnX)

Using SHEET1 as follows (note I moved Meeting date). All of the dates
on SHEET1 could be hard-coded dates or formulas, it doesnt matter. I am
assuming Meeting date is typed in.
A B C D
1 MeetingDate Dead1 Dead2 Dead3
2 02/24/06 01/24/06 01/31/06 02/09/06
3 03/01/06 02/02/06 02/09/06 02/23/06

On SHEET2, designate one cell for the PM to type in the proposed
meeting date. I will assume B5
Then, in the cell where you want Dead1 to appear, use:
=VLOOKUP(B5,Sheet1!$A$1:$D$3,2)

In the cell where you want Dead2, and Dead3 to appear, use:
=VLOOKUP(B5,Sheet1!$A$1:$D$3,3) -and- =VLOOKUP(B5,Sheet1!$A$1:$D$3,4)
,respectively.

On another note, you could even use a lookup feild for the meeting date
on SHEET2. Select the MeetingDate cell (B5 in my example), select
DATA-VALIDATION from the toolbar. In the Data Valadation dialog box,
change the 'Allow' from 'Any Value' to 'List'. Then, change the
'Source' box below it to a list of valid meeting dates. The only
problem is the list of available meeting dates would have to be on
SHEET2. (you could copy the SHEET1 dates to a unprinting region of
SHEET2). Just a thought.

 

[AlisonB]

This looks good - I'll give it a try. Thanks for all your help.

Kind regards,

AlisonB