Hi,

I have Date column on sheet1 and i would like Date Columns to propagate
on other sheets

when i enter this formula on the sheet2

=A1

it displays 01/01/1900 if cell is empty on sheet1

If cell is empty i dont want to display that 01/01/1900 thing on the
other sheet

If sheet1 date column cell is empty and other sheet date column should
be empty no 01/01/1900 thingg.....

how do i rectify this

thank you in advance
regards
Jay

=if(sheet2!a1="","",sheet2!a1)



Jay wrote:
>
> Hi,
>
> I have Date column on sheet1 and i would like Date Columns to propagate
> on other sheets
>
> when i enter this formula on the sheet2
>
> =A1
>
> it displays 01/01/1900 if cell is empty on sheet1
>
> If cell is empty i dont want to display that 01/01/1900 thing on the
> other sheet
>
> If sheet1 date column cell is empty and other sheet date column should
> be empty no 01/01/1900 thingg.....
>
> how do i rectify this
>
> thank you in advance
> regards
> Jay

--

Dave Peterson

Dave,

I had given this If statment , problem is if i give this if statement i
have another worksheet with the following formula

=SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))

formula returns #Value error if i put if statement as you gave , If i
remove that IF statement , formula works perfectly ....

how to rectify this problem ?

thank you
jay
Dave Peterson wrote:
> =if(sheet2!a1="","",sheet2!a1)
>
>
>
> Jay wrote:
> >
> > Hi,
> >
> > I have Date column on sheet1 and i would like Date Columns to propagate
> > on other sheets
> >
> > when i enter this formula on the sheet2
> >
> > =A1
> >
> > it displays 01/01/1900 if cell is empty on sheet1
> >
> > If cell is empty i dont want to display that 01/01/1900 thing on the
> > other sheet
> >
> > If sheet1 date column cell is empty and other sheet date column should
> > be empty no 01/01/1900 thingg.....
> >
> > how do i rectify this
> >
> > thank you in advance
> > regards
> > Jay
>
> --
>
> Dave Peterson

Maybe you can attack it slightly differently--not using =month()

=SUMPRODUCT(--(ISNUMBER(Sheet2!A9:A500)),
--(TEXT(Sheet2!A9:A500,"mm")="01"),
(Sheet2!K9:K500))

If this doesn't work, it's best to post your actual formula.

Jay wrote:
>
> Dave,
>
> I had given this If statment , problem is if i give this if statement i
> have another worksheet with the following formula
>
> =SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))
>
> formula returns #Value error if i put if statement as you gave , If i
> remove that IF statement , formula works perfectly ....
>
> how to rectify this problem ?
>
> thank you
> jay
> Dave Peterson wrote:
> > =if(sheet2!a1="","",sheet2!a1)
> >
> >
> >
> > Jay wrote:
> > >
> > > Hi,
> > >
> > > I have Date column on sheet1 and i would like Date Columns to propagate
> > > on other sheets
> > >
> > > when i enter this formula on the sheet2
> > >
> > > =A1
> > >
> > > it displays 01/01/1900 if cell is empty on sheet1
> > >
> > > If cell is empty i dont want to display that 01/01/1900 thing on the
> > > other sheet
> > >
> > > If sheet1 date column cell is empty and other sheet date column should
> > > be empty no 01/01/1900 thingg.....
> > >
> > > how do i rectify this
> > >
> > > thank you in advance
> > > regards
> > > Jay
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Dave,

its giving formula result 0

??/
Dave Peterson wrote:
> Maybe you can attack it slightly differently--not using =month()
>
> =SUMPRODUCT(--(ISNUMBER(Sheet2!A9:A500)),
> --(TEXT(Sheet2!A9:A500,"mm")="01"),
> (Sheet2!K9:K500))
>
> If this doesn't work, it's best to post your actual formula.
>
> Jay wrote:
> >
> > Dave,
> >
> > I had given this If statment , problem is if i give this if statement i
> > have another worksheet with the following formula
> >
> > =SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))
> >
> > formula returns #Value error if i put if statement as you gave , If i
> > remove that IF statement , formula works perfectly ....
> >
> > how to rectify this problem ?
> >
> > thank you
> > jay
> > Dave Peterson wrote:
> > > =if(sheet2!a1="","",sheet2!a1)
> > >
> > >
> > >
> > > Jay wrote:
> > > >
> > > > Hi,
> > > >
> > > > I have Date column on sheet1 and i would like Date Columns to propagate
> > > > on other sheets
> > > >
> > > > when i enter this formula on the sheet2
> > > >
> > > > =A1
> > > >
> > > > it displays 01/01/1900 if cell is empty on sheet1
> > > >
> > > > If cell is empty i dont want to display that 01/01/1900 thing on the
> > > > other sheet
> > > >
> > > > If sheet1 date column cell is empty and other sheet date column should
> > > > be empty no 01/01/1900 thingg.....
> > > >
> > > > how do i rectify this
> > > >
> > > > thank you in advance
> > > > regards
> > > > Jay
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

Then you either don't have any dates that are in January ins sheet2!a9:a500

Or you have numbers in K9:K500 that addup to 0 when limited to just January
dates.

Are you sure your "dates" are really dates--not just text that looks like dates?

Try typing in a date in one of those cells (01/01/2006 would work for me in my
USA settings) to see if the formula changes.

Jay wrote:
>
> Dave,
>
> its giving formula result 0
>
> ??/
> Dave Peterson wrote:
> > Maybe you can attack it slightly differently--not using =month()
> >
> > =SUMPRODUCT(--(ISNUMBER(Sheet2!A9:A500)),
> > --(TEXT(Sheet2!A9:A500,"mm")="01"),
> > (Sheet2!K9:K500))
> >
> > If this doesn't work, it's best to post your actual formula.
> >
> > Jay wrote:
> > >
> > > Dave,
> > >
> > > I had given this If statment , problem is if i give this if statement i
> > > have another worksheet with the following formula
> > >
> > > =SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))
> > >
> > > formula returns #Value error if i put if statement as you gave , If i
> > > remove that IF statement , formula works perfectly ....
> > >
> > > how to rectify this problem ?
> > >
> > > thank you
> > > jay
> > > Dave Peterson wrote:
> > > > =if(sheet2!a1="","",sheet2!a1)
> > > >
> > > >
> > > >
> > > > Jay wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I have Date column on sheet1 and i would like Date Columns to propagate
> > > > > on other sheets
> > > > >
> > > > > when i enter this formula on the sheet2
> > > > >
> > > > > =A1
> > > > >
> > > > > it displays 01/01/1900 if cell is empty on sheet1
> > > > >
> > > > > If cell is empty i dont want to display that 01/01/1900 thing on the
> > > > > other sheet
> > > > >
> > > > > If sheet1 date column cell is empty and other sheet date column should
> > > > > be empty no 01/01/1900 thingg.....
> > > > >
> > > > > how do i rectify this
> > > > >
> > > > > thank you in advance
> > > > > regards
> > > > > Jay
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

i have dates and numbers on A9:A500 and K9:K500
but its giving 0

i have English UK settings

Dave Peterson wrote:
> Then you either don't have any dates that are in January ins sheet2!a9:a500
>
> Or you have numbers in K9:K500 that addup to 0 when limited to just January
> dates.
>
> Are you sure your "dates" are really dates--not just text that looks like dates?
>
> Try typing in a date in one of those cells (01/01/2006 would work for me in my
> USA settings) to see if the formula changes.
>
> Jay wrote:
> >
> > Dave,
> >
> > its giving formula result 0
> >
> > ??/
> > Dave Peterson wrote:
> > > Maybe you can attack it slightly differently--not using =month()
> > >
> > > =SUMPRODUCT(--(ISNUMBER(Sheet2!A9:A500)),
> > > --(TEXT(Sheet2!A9:A500,"mm")="01"),
> > > (Sheet2!K9:K500))
> > >
> > > If this doesn't work, it's best to post your actual formula.
> > >
> > > Jay wrote:
> > > >
> > > > Dave,
> > > >
> > > > I had given this If statment , problem is if i give this if statement i
> > > > have another worksheet with the following formula
> > > >
> > > > =SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))
> > > >
> > > > formula returns #Value error if i put if statement as you gave , If i
> > > > remove that IF statement , formula works perfectly ....
> > > >
> > > > how to rectify this problem ?
> > > >
> > > > thank you
> > > > jay
> > > > Dave Peterson wrote:
> > > > > =if(sheet2!a1="","",sheet2!a1)
> > > > >
> > > > >
> > > > >
> > > > > Jay wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have Date column on sheet1 and i would like Date Columns to propagate
> > > > > > on other sheets
> > > > > >
> > > > > > when i enter this formula on the sheet2
> > > > > >
> > > > > > =A1
> > > > > >
> > > > > > it displays 01/01/1900 if cell is empty on sheet1
> > > > > >
> > > > > > If cell is empty i dont want to display that 01/01/1900 thing on the
> > > > > > other sheet
> > > > > >
> > > > > > If sheet1 date column cell is empty and other sheet date column should
> > > > > > be empty no 01/01/1900 thingg.....
> > > > > >
> > > > > > how do i rectify this
> > > > > >
> > > > > > thank you in advance
> > > > > > regards
> > > > > > Jay
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

If you type:
=count(sheet2!a9:a500)
and
=counta(sheet2!a9:a500)

do you get the same number returned?

If you type:
=isnumber(a9)
(or any/all the other cells)
do you get TRUE returned?



Jay wrote:
>
> i have dates and numbers on A9:A500 and K9:K500
> but its giving 0
>
> i have English UK settings
>
> Dave Peterson wrote:
> > Then you either don't have any dates that are in January ins sheet2!a9:a500
> >
> > Or you have numbers in K9:K500 that addup to 0 when limited to just January
> > dates.
> >
> > Are you sure your "dates" are really dates--not just text that looks like dates?
> >
> > Try typing in a date in one of those cells (01/01/2006 would work for me in my
> > USA settings) to see if the formula changes.
> >
> > Jay wrote:
> > >
> > > Dave,
> > >
> > > its giving formula result 0
> > >
> > > ??/
> > > Dave Peterson wrote:
> > > > Maybe you can attack it slightly differently--not using =month()
> > > >
> > > > =SUMPRODUCT(--(ISNUMBER(Sheet2!A9:A500)),
> > > > --(TEXT(Sheet2!A9:A500,"mm")="01"),
> > > > (Sheet2!K9:K500))
> > > >
> > > > If this doesn't work, it's best to post your actual formula.
> > > >
> > > > Jay wrote:
> > > > >
> > > > > Dave,
> > > > >
> > > > > I had given this If statment , problem is if i give this if statement i
> > > > > have another worksheet with the following formula
> > > > >
> > > > > =SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))
> > > > >
> > > > > formula returns #Value error if i put if statement as you gave , If i
> > > > > remove that IF statement , formula works perfectly ....
> > > > >
> > > > > how to rectify this problem ?
> > > > >
> > > > > thank you
> > > > > jay
> > > > > Dave Peterson wrote:
> > > > > > =if(sheet2!a1="","",sheet2!a1)
> > > > > >
> > > > > >
> > > > > >
> > > > > > Jay wrote:
> > > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have Date column on sheet1 and i would like Date Columns to propagate
> > > > > > > on other sheets
> > > > > > >
> > > > > > > when i enter this formula on the sheet2
> > > > > > >
> > > > > > > =A1
> > > > > > >
> > > > > > > it displays 01/01/1900 if cell is empty on sheet1
> > > > > > >
> > > > > > > If cell is empty i dont want to display that 01/01/1900 thing on the
> > > > > > > other sheet
> > > > > > >
> > > > > > > If sheet1 date column cell is empty and other sheet date column should
> > > > > > > be empty no 01/01/1900 thingg.....
> > > > > > >
> > > > > > > how do i rectify this
> > > > > > >
> > > > > > > thank you in advance
> > > > > > > regards
> > > > > > > Jay
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

yes i get same numbers for count function

and TRUE for isnumber function

If i put =isnumber(A9:A500) i m getting FALSE




Dave Peterson wrote:
> If you type:
> =count(sheet2!a9:a500)
> and
> =counta(sheet2!a9:a500)
>
> do you get the same number returned?
>
> If you type:
> =isnumber(a9)
> (or any/all the other cells)
> do you get TRUE returned?
>
>
>
> Jay wrote:
> >
> > i have dates and numbers on A9:A500 and K9:K500
> > but its giving 0
> >
> > i have English UK settings
> >
> > Dave Peterson wrote:
> > > Then you either don't have any dates that are in January ins sheet2!a9:a500
> > >
> > > Or you have numbers in K9:K500 that addup to 0 when limited to just January
> > > dates.
> > >
> > > Are you sure your "dates" are really dates--not just text that looks like dates?
> > >
> > > Try typing in a date in one of those cells (01/01/2006 would work for me in my
> > > USA settings) to see if the formula changes.
> > >
> > > Jay wrote:
> > > >
> > > > Dave,
> > > >
> > > > its giving formula result 0
> > > >
> > > > ??/
> > > > Dave Peterson wrote:
> > > > > Maybe you can attack it slightly differently--not using =month()
> > > > >
> > > > > =SUMPRODUCT(--(ISNUMBER(Sheet2!A9:A500)),
> > > > > --(TEXT(Sheet2!A9:A500,"mm")="01"),
> > > > > (Sheet2!K9:K500))
> > > > >
> > > > > If this doesn't work, it's best to post your actual formula.
> > > > >
> > > > > Jay wrote:
> > > > > >
> > > > > > Dave,
> > > > > >
> > > > > > I had given this If statment , problem is if i give this if statement i
> > > > > > have another worksheet with the following formula
> > > > > >
> > > > > > =SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))
> > > > > >
> > > > > > formula returns #Value error if i put if statement as you gave , If i
> > > > > > remove that IF statement , formula works perfectly ....
> > > > > >
> > > > > > how to rectify this problem ?
> > > > > >
> > > > > > thank you
> > > > > > jay
> > > > > > Dave Peterson wrote:
> > > > > > > =if(sheet2!a1="","",sheet2!a1)
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Jay wrote:
> > > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I have Date column on sheet1 and i would like Date Columns to propagate
> > > > > > > > on other sheets
> > > > > > > >
> > > > > > > > when i enter this formula on the sheet2
> > > > > > > >
> > > > > > > > =A1
> > > > > > > >
> > > > > > > > it displays 01/01/1900 if cell is empty on sheet1
> > > > > > > >
> > > > > > > > If cell is empty i dont want to display that 01/01/1900 thing on the
> > > > > > > > other sheet
> > > > > > > >
> > > > > > > > If sheet1 date column cell is empty and other sheet date column should
> > > > > > > > be empty no 01/01/1900 thingg.....
> > > > > > > >
> > > > > > > > how do i rectify this
> > > > > > > >
> > > > > > > > thank you in advance
> > > > > > > > regards
> > > > > > > > Jay
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

sorry,

For =count(a9:a500) i get 223

=counta(a9:a500) i get 224

sorry for the last message
Dave Peterson wrote:
> If you type:
> =count(sheet2!a9:a500)
> and
> =counta(sheet2!a9:a500)
>
> do you get the same number returned?
>
> If you type:
> =isnumber(a9)
> (or any/all the other cells)
> do you get TRUE returned?
>
>
>
> Jay wrote:
> >
> > i have dates and numbers on A9:A500 and K9:K500
> > but its giving 0
> >
> > i have English UK settings
> >
> > Dave Peterson wrote:
> > > Then you either don't have any dates that are in January ins sheet2!a9:a500
> > >
> > > Or you have numbers in K9:K500 that addup to 0 when limited to just January
> > > dates.
> > >
> > > Are you sure your "dates" are really dates--not just text that looks like dates?
> > >
> > > Try typing in a date in one of those cells (01/01/2006 would work for me in my
> > > USA settings) to see if the formula changes.
> > >
> > > Jay wrote:
> > > >
> > > > Dave,
> > > >
> > > > its giving formula result 0
> > > >
> > > > ??/
> > > > Dave Peterson wrote:
> > > > > Maybe you can attack it slightly differently--not using =month()
> > > > >
> > > > > =SUMPRODUCT(--(ISNUMBER(Sheet2!A9:A500)),
> > > > > --(TEXT(Sheet2!A9:A500,"mm")="01"),
> > > > > (Sheet2!K9:K500))
> > > > >
> > > > > If this doesn't work, it's best to post your actual formula.
> > > > >
> > > > > Jay wrote:
> > > > > >
> > > > > > Dave,
> > > > > >
> > > > > > I had given this If statment , problem is if i give this if statement i
> > > > > > have another worksheet with the following formula
> > > > > >
> > > > > > =SUMPRODUCT((MONTH('sheet2'!A9:A500)=1)*('sheet2'!K9:K500))
> > > > > >
> > > > > > formula returns #Value error if i put if statement as you gave , If i
> > > > > > remove that IF statement , formula works perfectly ....
> > > > > >
> > > > > > how to rectify this problem ?
> > > > > >
> > > > > > thank you
> > > > > > jay
> > > > > > Dave Peterson wrote:
> > > > > > > =if(sheet2!a1="","",sheet2!a1)
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Jay wrote:
> > > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I have Date column on sheet1 and i would like Date Columns to propagate
> > > > > > > > on other sheets
> > > > > > > >
> > > > > > > > when i enter this formula on the sheet2
> > > > > > > >
> > > > > > > > =A1
> > > > > > > >
> > > > > > > > it displays 01/01/1900 if cell is empty on sheet1
> > > > > > > >
> > > > > > > > If cell is empty i dont want to display that 01/01/1900 thing on the
> > > > > > > > other sheet
> > > > > > > >
> > > > > > > > If sheet1 date column cell is empty and other sheet date column should
> > > > > > > > be empty no 01/01/1900 thingg.....
> > > > > > > >
> > > > > > > > how do i rectify this
> > > > > > > >
> > > > > > > > thank you in advance
> > > > > > > > regards
> > > > > > > > Jay
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson