Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default copying date from one sheet to another work sheet

=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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default copying date from one sheet to another work sheet

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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default copying date from one sheet to another work sheet

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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default copying date from one sheet to another work sheet

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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

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


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

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




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

dave,

i found out why it was giving 224 cos i have entered IF statement that
you have given eariler in the cell it is also counting that one
cell,when i deleted that its giving both 223 now.

Jay wrote:
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


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default copying date from one sheet to another work sheet

You sure your data is in A9:a500 and K9:K500?

I don't have another guess.

Jay wrote:

dave,

i found out why it was giving 224 cos i have entered IF statement that
you have given eariler in the cell it is also counting that one
cell,when i deleted that its giving both 223 now.

Jay wrote:
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


--

Dave Peterson
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default copying date from one sheet to another work sheet

Actually, I have one more guess.

Do that same test for K9:k500

=count(k9:k500)
=counta(k9:k500)

Maybe those numbers aren't numbers!

Dave Peterson wrote:

You sure your data is in A9:a500 and K9:K500?

I don't have another guess.

Jay wrote:

dave,

i found out why it was giving 224 cos i have entered IF statement that
you have given eariler in the cell it is also counting that one
cell,when i deleted that its giving both 223 now.

Jay wrote:
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


--

Dave Peterson


--

Dave Peterson
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 40
Default copying date from one sheet to another work sheet

yes its in same range

regards
Jay
Dave Peterson wrote:
You sure your data is in A9:a500 and K9:K500?

I don't have another guess.

Jay wrote:

dave,

i found out why it was giving 224 cos i have entered IF statement that
you have given eariler in the cell it is also counting that one
cell,when i deleted that its giving both 223 now.

Jay wrote:
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


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro unouwanme Excel Discussion (Misc queries) 9 August 31st 06 09:38 PM
Copying Sheet bg19299 Excel Discussion (Misc queries) 3 July 31st 06 02:20 PM
Date formula that adds 7 days to a cell when sheet is copied dkent Excel Worksheet Functions 1 April 3rd 06 03:59 PM
copying sheet references that refer to a cell in the preceding she GBT Excel Worksheet Functions 1 March 24th 06 07:51 PM
search for latest date Sue Excel Worksheet Functions 2 August 2nd 05 11:09 PM


All times are GMT +1. The time now is 06:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"