Filling up cells that depend on another input cell

  • Thread starter Thread starter crapit
  • Start date Start date
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,
 
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,
 
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,
 
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,
 
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,
 
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,
 
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,
 
Back
Top