Two quick questions

timdee · #1 07-06-2005, 05:53 AM

Firstly, I am using Excel 97 SR-2. My questions are:

If I enter a date in a cell then drag the fill handle down the column it increased by one day each time. Is it possible to create 2 rows with the same date, then the next two increase by one etc. If so, how do I do it?

Secondly, if there is a formula in a cell but no data for it to use it displays ####. Is it possible to stop this until data is entered?
Regards,
Tim.

#2 07-06-2005, 07:05 AM

Tim,

Put this formula in A2, and copy down

=IF(MOD(ROW(),2)=0,A1,A1+1)

--
HTH

Bob Phillips

"timdee" <timdee.1rqqai_1120640719.7833@excelforum-nospam.com> wrote in
message news:timdee.1rqqai_1120640719.7833@excelforum-nospam.com...
>
> Firstly, I am using Excel 97 SR-2. My questions are:
>
> If I enter a date in a cell then drag the fill handle down the column
> it increased by one day each time. Is it possible to create 2 rows
> with the same date, then the next two increase by one etc. If so, how
> do I do it?
>
> Secondly, if there is a formula in a cell but no data for it to use it
> displays ####. Is it possible to stop this until data is entered?
> Regards,
> Tim.
>
>
> --
> timdee
> ------------------------------------------------------------------------
> timdee's Profile:
http://www.excelforum.com/member.php...o&userid=24943
> View this thread: http://www.excelforum.com/showthread...hreadid=384795
>

timdee · #3 07-06-2005, 07:27 AM

Brilliant, thanks Bob.

#4 07-06-2005, 08:05 AM

> > If I enter a date in a cell then drag the fill handle down the column
> > it increased by one day each time. Is it possible to create 2 rows
> > with the same date, then the next two increase by one etc. If so, how
> > do I do it?

Another slant to the 1st Q (above) to play with ..

With an initial date in A1, say: 01-Jul-2005

Put in say, B1:
=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/2),)

Format B1 as date, and copy down

This will yield in col B:

01-Jul-2005
01-Jul-2005
02-Jul-2005
02-Jul-2005
03-Jul-2005
03-Jul-2005
04-Jul-2005
04-Jul-2005
05-Jul-2005
05-Jul-2005
etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

#5 07-06-2005, 08:05 AM

Some corrections, sorry:

Lines

> With an initial date in A1, say: 01-Jul-2005
>
> Put in say, B1:
> =OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/2),)

should read as:

With running dates filled in A1 down:

01-Jul-2005
02-Jul-2005
03-Jul-2005
04-Jul-2005
05-Jul-2005
06-Jul-2005
etc

Put in say, B1:
=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/2),)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

#6 07-06-2005, 08:05 AM

"timdee" wrote:
> .. Secondly, if there is a formula in a cell but no data for it to use it
> displays ####. Is it possible to stop this until data is entered?

Try something along these lines:

If C1 contains the formula, say: =A1/B1
try instead in C1: =IF(OR(A1="",B1=""),"",A1/B1)

The simple error trap: =IF(OR(A1="",B1=""),"", ... )
will help ensure that C1 returns a blank: "" instead of computing A1/B1
"prematurely", until values are entered in both A1 and B1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

timdee · #7 07-06-2005, 12:01 PM

Thanks Guys. Bob, is there a simple way of inserting an occasional 3rd row with the same date?

#8 07-06-2005, 02:05 PM

If you mean 3 rows of the same date, then use

=IF(MOD(ROW(),3)<>1,A1,A1+1)

and copy down as before

--
HTH

Bob Phillips

"timdee" <timdee.1rr73j_1120662618.3565@excelforum-nospam.com> wrote in
message news:timdee.1rr73j_1120662618.3565@excelforum-nospam.com...
>
> Thanks Guys. Bob, is there a simple way of inserting an occasional 3rd
> row with the same date?
>
>
> --
> timdee
> ------------------------------------------------------------------------
> timdee's Profile:
http://www.excelforum.com/member.php...o&userid=24943
> View this thread: http://www.excelforum.com/showthread...hreadid=384795
>

timdee · #9 07-07-2005, 05:14 AM

Bob, I am most grateful for your help. Just to clarify what I am trying to achieve. Each day I usually have 2 rows of data to enter, but just occasionally there will be a third row required for a day, then I will revert back to two rows again. I am not sure if this is possible and I don't want to take up any of your valuable time. but would appreciate your advice.

#10 07-07-2005, 07:05 AM

I would use another cell to toggle it, like this

=IF(MOD(ROW(),IF(H1<>"",3,2))<>1,A1,A1+1)

and when you want it for a third row, put any value in H1, when you want
every second, clear H1

--
HTH

Bob Phillips

"timdee" <timdee.1rskyd_1120727110.4079@excelforum-nospam.com> wrote in
message news:timdee.1rskyd_1120727110.4079@excelforum-nospam.com...
>
> Bob, I am most grateful for your help. Just to clarify what I am trying
> to achieve. Each day I usually have 2 rows of data to enter, but just
> occasionally there will be a third row required for a day, then I will
> revert back to two rows again. I am not sure if this is possible and I
> don't want to take up any of your valuable time. but would appreciate
> your advice.
>
>
> --
> timdee
> ------------------------------------------------------------------------
> timdee's Profile:
http://www.excelforum.com/member.php...o&userid=24943
> View this thread: http://www.excelforum.com/showthread...hreadid=384795
>

Welcome to the Excel Forum

Forum Statistics