Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Elboo
 
Posts: n/a
Default How to change (delivery) days and automatically the receive date in an other cell?


Is it an option that if cell a4 is 1 (for example an order of 1 pallet
on date 4 January), that cell b7 also becomes 1 (receive date is then 7
January)? Yes, but my most important question is then if the
deliverytime (in this example 3 days, so that c1 = 3, see figure 1)
would be changed in 4 (c1 = 4), that the receive date change in (4+4) 8
January (see figure 2) and will be placed in cel b8, or deliverytime is
6 days and the receive date will be 10 (4+6) January in cel b10 (see
figure 3)?

A= order
B= receive date
C= deliverytime

figure 1
-----A-----------B----------C
1----------------------------3
2-----------------------------
3-----------------------------
4---1------------------------
5-----------------------------
6-----------------------------
7----------------1-----------

figure 2
-----A-----------B----------C
1----------------------------4
2-----------------------------
3-----------------------------
4---1------------------------
5-----------------------------
6-----------------------------
7-----------------------------
8-----------------1-----------

figure 3
-----A-----------B----------C
1----------------------------6
2-----------------------------
3-----------------------------
4---1------------------------
5-----------------------------
6-----------------------------
7-----------------------------
8-----------------------------
9-----------------------------
10--------------1------------

I hope someone could solve this problem for me, I've tried it very long
now but it wouldn't work.

It's important that c1 could be changed in a number (of delivery days)
and that it calculated the rest automatically, I can do it handly but i
hope that someone now the formula for the automatically calculation.

If you can do it only in Dutch, no problem, I'm a Dutchman ;)


--
Elboo
------------------------------------------------------------------------
Elboo's Profile: http://www.excelforum.com/member.php...o&userid=16624
View this thread: http://www.excelforum.com/showthread...hreadid=318689

  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Elboo

To get what i think you're after to work i had to change it just a little ..
..................column A..........column B
row 1.......Order No..........ABC123
2................Order Date........5/1/05 (ie 5 Jan 05)
3................Del. Time (days)..3
4...............1/1/05..................=IF($A4=B$2,"order
date",IF($A4B$2,IF(B$2+B$3=$A4,"delivery",""),"") )

and filled down the column
Now you enter the order date in B2
enter the number of days delivery in B3
and on your calendar you will see the words "order date" and "delivery"

If you change either B2 or B3 the calendar will adjust automatically
You can then go across into column C for the next order etc ...

Hope this helps.
Cheers
JulieD

"Elboo" wrote in message
...

Is it an option that if cell a4 is 1 (for example an order of 1 pallet
on date 4 January), that cell b7 also becomes 1 (receive date is then 7
January)? Yes, but my most important question is then if the
deliverytime (in this example 3 days, so that c1 = 3, see figure 1)
would be changed in 4 (c1 = 4), that the receive date change in (4+4) 8
January (see figure 2) and will be placed in cel b8, or deliverytime is
6 days and the receive date will be 10 (4+6) January in cel b10 (see
figure 3)?

A= order
B= receive date
C= deliverytime

figure 1
-----A-----------B----------C
1----------------------------3
2-----------------------------
3-----------------------------
4---1------------------------
5-----------------------------
6-----------------------------
7----------------1-----------

figure 2
-----A-----------B----------C
1----------------------------4
2-----------------------------
3-----------------------------
4---1------------------------
5-----------------------------
6-----------------------------
7-----------------------------
8-----------------1-----------

figure 3
-----A-----------B----------C
1----------------------------6
2-----------------------------
3-----------------------------
4---1------------------------
5-----------------------------
6-----------------------------
7-----------------------------
8-----------------------------
9-----------------------------
10--------------1------------

I hope someone could solve this problem for me, I've tried it very long
now but it wouldn't work.

It's important that c1 could be changed in a number (of delivery days)
and that it calculated the rest automatically, I can do it handly but i
hope that someone now the formula for the automatically calculation.

If you can do it only in Dutch, no problem, I'm a Dutchman ;)


--
Elboo
------------------------------------------------------------------------
Elboo's Profile:
http://www.excelforum.com/member.php...o&userid=16624
View this thread: http://www.excelforum.com/showthread...hreadid=318689



  #3   Report Post  
Elboo
 
Posts: n/a
Default


Ok thanks, that's something that looks good. But the only thing is that
de order date is given (in my case when there are more then 21.600 subs
used).

But i will try to fix it, if you have a suggestion i will hear it.

Thanx 4 your time :)

With many love ;) Mark

Hi Elboo

To get what i think you're after to work i had to change it just a
little ..
..................column A..........column B
row 1.......Order No..........ABC123
2................Order Date........5/1/05 (ie 5 Jan 05)
3................Del. Time (days)..3
4...............1/1/05..................=IF($A4=B$2,"order
date",IF($A4B$2,IF(B$2+B$3=$A4,"delivery",""),"") )

and filled down the column
Now you enter the order date in B2
enter the number of days delivery in B3
and on your calendar you will see the words "order date" and
"delivery"

If you change either B2 or B3 the calendar will adjust automatically
You can then go across into column C for the next order etc ...

Hope this helps.
Cheers
JulieD



--
Elboo
------------------------------------------------------------------------
Elboo's Profile: http://www.excelforum.com/member.php...o&userid=16624
View this thread: http://www.excelforum.com/showthread...hreadid=318689

  #4   Report Post  
JulieD
 
Posts: n/a
Default

Hi Mark

i'm sorry i don't understand what you mean in this post .. could you please
clarify what you would like to see.

Cheers
JulieD

"Elboo" wrote in message
...

Ok thanks, that's something that looks good. But the only thing is that
de order date is given (in my case when there are more then 21.600 subs
used).

But i will try to fix it, if you have a suggestion i will hear it.

Thanx 4 your time :)

With many love ;) Mark

Hi Elboo

To get what i think you're after to work i had to change it just a
little ..
..................column A..........column B
row 1.......Order No..........ABC123
2................Order Date........5/1/05 (ie 5 Jan 05)
3................Del. Time (days)..3
4...............1/1/05..................=IF($A4=B$2,"order
date",IF($A4B$2,IF(B$2+B$3=$A4,"delivery",""),"") )

and filled down the column
Now you enter the order date in B2
enter the number of days delivery in B3
and on your calendar you will see the words "order date" and
"delivery"

If you change either B2 or B3 the calendar will adjust automatically
You can then go across into column C for the next order etc ...

Hope this helps.
Cheers
JulieD



--
Elboo
------------------------------------------------------------------------
Elboo's Profile:
http://www.excelforum.com/member.php...o&userid=16624
View this thread: http://www.excelforum.com/showthread...hreadid=318689



  #5   Report Post  
Elboo
 
Posts: n/a
Default


Ok, i'll try it again:

But the only thing is that de order date is AUTOMATICALLY
given/CALCULATED (in my case when there are more then 21.600 subs
used).

So when a company produced on a day 30.000 products, they need to order
a new pallet of subs (for 1 product you use 1 sub) because more then
21.600 subs are used and then there goes an order for 1 pallet.

So there is far more then 1 order date, but e.g. 7,10,12,13,17 and 18
Jan.

This makes it more difficult.... :(

Cheers
Mark

JulieD Wrote:
Hi Mark

i'm sorry i don't understand what you mean in this post .. could you
please
clarify what you would like to see.

Cheers
JulieD

"Elboo" wrote in message
...

Ok thanks, that's something that looks good. But the only thing is

that
de order date is given (in my case when there are more then 21.600

subs
used).

But i will try to fix it, if you have a suggestion i will hear it.

Thanx 4 your time :)

With many love ;) Mark

Hi Elboo

To get what i think you're after to work i had to change it just a
little ..
..................column A..........column B
row 1.......Order No..........ABC123
2................Order Date........5/1/05 (ie 5 Jan 05)
3................Del. Time (days)..3
4...............1/1/05..................=IF($A4=B$2,"order
date",IF($A4B$2,IF(B$2+B$3=$A4,"delivery",""),"") )

and filled down the column
Now you enter the order date in B2
enter the number of days delivery in B3
and on your calendar you will see the words "order date" and
"delivery"

If you change either B2 or B3 the calendar will adjust

automatically
You can then go across into column C for the next order etc ...

Hope this helps.
Cheers
JulieD



--
Elboo

------------------------------------------------------------------------
Elboo's Profile:
http://www.excelforum.com/member.php...o&userid=16624
View this thread:

http://www.excelforum.com/showthread...hreadid=318689



--
Elboo
------------------------------------------------------------------------
Elboo's Profile: http://www.excelforum.com/member.php...o&userid=16624
View this thread: http://www.excelforum.com/showthread...hreadid=318689



  #6   Report Post  
JulieD
 
Posts: n/a
Default

Hi Elboo

okay, let me see if i've understood - you company produces items, 21,600
items fit on a pallet, when you get more than that you need to order a new
pallet. So you could be ordering a new pallet everyday, every second day or
whenever ...and you need this worksheet to work every time you order a
pallet.

If this is the case, there is no problem, just copy what is in column B
(minus the order date and delivery time) to columns C to IV (this will give
you 255 orders all up). If that is not going to be enough, i can change it
so it goes down the rows and then you have 65,535 order lines.

i hope i've understood correctly .. if not, please try again :)

Cheers
JulieD

"Elboo" wrote in message
...

Ok, i'll try it again:

But the only thing is that de order date is AUTOMATICALLY
given/CALCULATED (in my case when there are more then 21.600 subs
used).

So when a company produced on a day 30.000 products, they need to order
a new pallet of subs (for 1 product you use 1 sub) because more then
21.600 subs are used and then there goes an order for 1 pallet.

So there is far more then 1 order date, but e.g. 7,10,12,13,17 and 18
Jan.

This makes it more difficult.... :(

Cheers
Mark

JulieD Wrote:
Hi Mark

i'm sorry i don't understand what you mean in this post .. could you
please
clarify what you would like to see.

Cheers
JulieD

"Elboo" wrote in message
...

Ok thanks, that's something that looks good. But the only thing is

that
de order date is given (in my case when there are more then 21.600

subs
used).

But i will try to fix it, if you have a suggestion i will hear it.

Thanx 4 your time :)

With many love ;) Mark

Hi Elboo

To get what i think you're after to work i had to change it just a
little ..
..................column A..........column B
row 1.......Order No..........ABC123
2................Order Date........5/1/05 (ie 5 Jan 05)
3................Del. Time (days)..3
4...............1/1/05..................=IF($A4=B$2,"order
date",IF($A4B$2,IF(B$2+B$3=$A4,"delivery",""),"") )

and filled down the column
Now you enter the order date in B2
enter the number of days delivery in B3
and on your calendar you will see the words "order date" and
"delivery"

If you change either B2 or B3 the calendar will adjust

automatically
You can then go across into column C for the next order etc ...

Hope this helps.
Cheers
JulieD



--
Elboo

------------------------------------------------------------------------
Elboo's Profile:
http://www.excelforum.com/member.php...o&userid=16624
View this thread:

http://www.excelforum.com/showthread...hreadid=318689



--
Elboo
------------------------------------------------------------------------
Elboo's Profile:
http://www.excelforum.com/member.php...o&userid=16624
View this thread: http://www.excelforum.com/showthread...hreadid=318689



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 08:47 AM
Date issue between Windows and Macintosh version dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 04:51 PM
Conditional Formatting for dates spacerocket Excel Worksheet Functions 2 November 4th 04 11:13 AM


All times are GMT +1. The time now is 02:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"