Filling up cells that depend on another input cell

C

crapit

E.g input cell m1 = jan

a b c .... ae3
3


Cell display value only as DAY, where DAY is depend on date from dd-mm-yy,
dd always set to 01, yy set to 07, and mm extract from m1

Cell 3a Display value as Monday which is actually 01-01-07,
Cell 3b Display value as Tuesday which is actually 02-01-07,
Cell 3c Display value as Wednesday which is actually 03-01-07,
Cell 3ae Display value as Monday which is actually 31-01-07,
 
S

Sandy Mann

For A3 use:

=DATE(2007,(MATCH(M1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)),1)

For B3 use:

=A3+1 and copy across to AE3


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


E.g input cell m1 = jan

a b c .... ae3
3


Cell display value only as DAY, where DAY is depend on date from dd-mm-yy,
dd always set to 01, yy set to 07, and mm extract from m1

Cell 3a Display value as Monday which is actually 01-01-07,
Cell 3b Display value as Tuesday which is actually 02-01-07,
Cell 3c Display value as Wednesday which is actually 03-01-07,
Cell 3ae Display value as Monday which is actually 31-01-07,
 
C

crapit

Hi, If using copy across to AE3 it will simply display to the next month, then wat about making the cell to show up to the last day of the month?

Jan 31
Feb 28
Mar 31
Apr 30
....
For A3 use:

=DATE(2007,(MATCH(M1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)),1)

For B3 use:

=A3+1 and copy across to AE3


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


E.g input cell m1 = jan

a b c .... ae3
3


Cell display value only as DAY, where DAY is depend on date from dd-mm-yy,
dd always set to 01, yy set to 07, and mm extract from m1

Cell 3a Display value as Monday which is actually 01-01-07,
Cell 3b Display value as Tuesday which is actually 02-01-07,
Cell 3c Display value as Wednesday which is actually 03-01-07,
Cell 3ae Display value as Monday which is actually 31-01-07,
 
S

Sandy Mann

In B3 use:

=IF(MONTH(A3+1)=MONTH($A$3),A3+1,"")

Then in A4 use:
=A3+33-DAY(A3+33)+1

Then the same formula as B3 indexed to suit.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Hi, If using copy across to AE3 it will simply display to the next month, then wat about making the cell to show up to the last day of the month?

Jan 31
Feb 28
Mar 31
Apr 30
...
For A3 use:

=DATE(2007,(MATCH(M1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)),1)

For B3 use:

=A3+1 and copy across to AE3


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


E.g input cell m1 = jan

a b c .... ae3
3


Cell display value only as DAY, where DAY is depend on date from dd-mm-yy,
dd always set to 01, yy set to 07, and mm extract from m1

Cell 3a Display value as Monday which is actually 01-01-07,
Cell 3b Display value as Tuesday which is actually 02-01-07,
Cell 3c Display value as Wednesday which is actually 03-01-07,
Cell 3ae Display value as Monday which is actually 31-01-07,
 
S

Sandy Mann

Sandy Mann said:
In B3 use:
Click to expand...
=IF(MONTH(A3+1)=MONTH($A$3),A3+1,"")
Click to expand...

Except of course you only need that formula from AB3 to AE3! ie

=IF(MONTH(AB3+1)=MONTH($A$3),AB3+1,"")

The original one will work perfectly well up to that point and will use less resources.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


In B3 use:

=IF(MONTH(A3+1)=MONTH($A$3),A3+1,"")

Then in A4 use:
=A3+33-DAY(A3+33)+1

Then the same formula as B3 indexed to suit.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Hi, If using copy across to AE3 it will simply display to the next month, then wat about making the cell to show up to the last day of the month?

Jan 31
Feb 28
Mar 31
Apr 30
...
For A3 use:

=DATE(2007,(MATCH(M1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)),1)

For B3 use:

=A3+1 and copy across to AE3


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


E.g input cell m1 = jan

a b c .... ae3
3


Cell display value only as DAY, where DAY is depend on date from dd-mm-yy,
dd always set to 01, yy set to 07, and mm extract from m1

Cell 3a Display value as Monday which is actually 01-01-07,
Cell 3b Display value as Tuesday which is actually 02-01-07,
Cell 3c Display value as Wednesday which is actually 03-01-07,
Cell 3ae Display value as Monday which is actually 31-01-07,
 
C

crapit

Thks 4 the help
Sandy Mann said:
In B3 use:
Click to expand...
=IF(MONTH(A3+1)=MONTH($A$3),A3+1,"")
Click to expand...

Except of course you only need that formula from AB3 to AE3! ie

=IF(MONTH(AB3+1)=MONTH($A$3),AB3+1,"")

The original one will work perfectly well up to that point and will use less resources.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


In B3 use:

=IF(MONTH(A3+1)=MONTH($A$3),A3+1,"")

Then in A4 use:
=A3+33-DAY(A3+33)+1

Then the same formula as B3 indexed to suit.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Hi, If using copy across to AE3 it will simply display to the next month, then wat about making the cell to show up to the last day of the month?

Jan 31
Feb 28
Mar 31
Apr 30
...
For A3 use:

=DATE(2007,(MATCH(M1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)),1)

For B3 use:

=A3+1 and copy across to AE3


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


E.g input cell m1 = jan

a b c .... ae3
3


Cell display value only as DAY, where DAY is depend on date from dd-mm-yy,
dd always set to 01, yy set to 07, and mm extract from m1

Cell 3a Display value as Monday which is actually 01-01-07,
Cell 3b Display value as Tuesday which is actually 02-01-07,
Cell 3c Display value as Wednesday which is actually 03-01-07,
Cell 3ae Display value as Monday which is actually 31-01-07,
 
S

Sandy Mann

You're very welcome, hanks for the thanks

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Thks 4 the help
Sandy Mann said:
In B3 use:
Click to expand...
=IF(MONTH(A3+1)=MONTH($A$3),A3+1,"")
Click to expand...

Except of course you only need that formula from AB3 to AE3! ie

=IF(MONTH(AB3+1)=MONTH($A$3),AB3+1,"")

The original one will work perfectly well up to that point and will use less resources.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


In B3 use:

=IF(MONTH(A3+1)=MONTH($A$3),A3+1,"")

Then in A4 use:
=A3+33-DAY(A3+33)+1

Then the same formula as B3 indexed to suit.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Hi, If using copy across to AE3 it will simply display to the next month, then wat about making the cell to show up to the last day of the month?

Jan 31
Feb 28
Mar 31
Apr 30
...
For A3 use:

=DATE(2007,(MATCH(M1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)),1)

For B3 use:

=A3+1 and copy across to AE3


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


E.g input cell m1 = jan

a b c .... ae3
3


Cell display value only as DAY, where DAY is depend on date from dd-mm-yy,
dd always set to 01, yy set to 07, and mm extract from m1

Cell 3a Display value as Monday which is actually 01-01-07,
Cell 3b Display value as Tuesday which is actually 02-01-07,
Cell 3c Display value as Wednesday which is actually 03-01-07,
Cell 3ae Display value as Monday which is actually 31-01-07,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

cell formatting & Input box 1
Return mm/dd/yy based on 1st Day of Month 5
SUMIF or SUMPRODUCT to calculate monthly sales of various products 5
How to set formula for poping up the date of next Wednesday.... 2
Calculate DATE from Weekday and Week # 8
Select value on a cell by matching value of two cells 1
Calendar Problem 4
Entering dates into cells as mm/yy or yy 4

Top