copying date from one sheet to another work sheet

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

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)


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
Click to expand...
 
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
 
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.
 
Dave,

its giving formula result 0

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

its giving formula result 0

??/
Click to expand...
 
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?


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

i have English UK settings
Click to expand...
 
yes i get same numbers for count function

and TRUE for isnumber function

If i put =isnumber(A9:A500) i m getting FALSE
 
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.
sorry,

For =count(a9:a500) i get 223

=counta(a9:a500) i get 224

sorry for the last message
Click to expand...
 
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to get largest date from a Vlookup 1
SUMPRODUCT Function Compare Date 3
Empty Cells 5
Build a reference to another worksheet from cell values in currentsheet 3
countifs Function not giving desired result with date criteria 2
Sorting data in new sheet. 3
Date Function 2
Returning a YES or NO if there is a value on a range of cells 0

Back
Top