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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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

[email protected]
[email protected] 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,
 
Top