Two quick questions

  • Thread starter Thread starter timdee
  • Start date Start date
T

timdee

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.
 
If I enter a date in a cell then drag the fill handle down the column
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
 
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),)
Click to expand...

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),)
 
timdee said:
.. 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?
Click to expand...

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
 
Thanks Guys. Bob, is there a simple way of inserting an occasional 3rd
row with the same date?
 
If you mean 3 rows of the same date, then use

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

and copy down as before
 
Bob, I am most grateful for your help. Just to clarify what I am tryin
to achieve. Each day I usually have 2 rows of data to enter, but jus
occasionally there will be a third row required for a day, then I wil
revert back to two rows again. I am not sure if this is possible and
don't want to take up any of your valuable time. but would appreciat
your advice
 
Bob, I am most grateful for your help. Just to clarify what I am tryin
to achieve. Each day I usually have 2 rows of data to enter, but jus
occasionally there will be a third row required for a day, then I wil
revert back to two rows again. I am not sure if this is possible and
don't want to take up any of your valuable time. but would appreciat
your advice
 
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
 
Back
Top