|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com