|
in need of a formula
hi, i'm working on two sheets within the same book.
sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
Yes there is :-)
Use this in B1 and copy down =IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,FALSE)),"no work",VLOOKUP(A1,Sheet1!A:B,2,FALSE)) "Bowmanator" wrote: hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
Enter this formula on Sheet2 C1:
=IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
neither one of those formulas seam to do what i want, so i added the acual
sheets i'm working on. http://www.savefile.com/files/2073433 sheet 1 i was refering to is the production tab and sheet 2 is the shelf life tab so if date is entered in column D on production sheet it'll find it's matching date in column F on shelf life sheet, if true it'll add amount produced in column H in shelf life sheet on the same row as the matching date, if no date from column F is used in column D on production sheet it should say no production. "T. Valko" wrote: Enter this formula on Sheet2 C1: =IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
Here's the formula I entered in Shelf Life K4:
=IF(COUNTIF(PRODUCTION!D:D,F4),SUMIF(PRODUCTION!D: D,F4,PRODUCTION!F:F),"no prod") Copied down. It returned the correct results. Note that your sample file only has a few matching dates *and* you're missing some dates on the Shelf Life sheet. On the Prod sheet there are dates for 4/8, 4/9, 4/28, 4/29, 4/30. On the Shelf Life sheet the dates 4/28 and 4/29 are missing from column F. -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... neither one of those formulas seam to do what i want, so i added the acual sheets i'm working on. http://www.savefile.com/files/2073433 sheet 1 i was refering to is the production tab and sheet 2 is the shelf life tab so if date is entered in column D on production sheet it'll find it's matching date in column F on shelf life sheet, if true it'll add amount produced in column H in shelf life sheet on the same row as the matching date, if no date from column F is used in column D on production sheet it should say no production. "T. Valko" wrote: Enter this formula on Sheet2 C1: =IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
Here's the formula I entered in Shelf Life K4
I used column K so I could compare it with your results in column H. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Here's the formula I entered in Shelf Life K4: =IF(COUNTIF(PRODUCTION!D:D,F4),SUMIF(PRODUCTION!D: D,F4,PRODUCTION!F:F),"no prod") Copied down. It returned the correct results. Note that your sample file only has a few matching dates *and* you're missing some dates on the Shelf Life sheet. On the Prod sheet there are dates for 4/8, 4/9, 4/28, 4/29, 4/30. On the Shelf Life sheet the dates 4/28 and 4/29 are missing from column F. -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... neither one of those formulas seam to do what i want, so i added the acual sheets i'm working on. http://www.savefile.com/files/2073433 sheet 1 i was refering to is the production tab and sheet 2 is the shelf life tab so if date is entered in column D on production sheet it'll find it's matching date in column F on shelf life sheet, if true it'll add amount produced in column H in shelf life sheet on the same row as the matching date, if no date from column F is used in column D on production sheet it should say no production. "T. Valko" wrote: Enter this formula on Sheet2 C1: =IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
that seems to work great except when column H shows no prod i need (if
possible) column G and I to be blank. "T. Valko" wrote: Here's the formula I entered in Shelf Life K4 I used column K so I could compare it with your results in column H. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Here's the formula I entered in Shelf Life K4: =IF(COUNTIF(PRODUCTION!D:D,F4),SUMIF(PRODUCTION!D: D,F4,PRODUCTION!F:F),"no prod") Copied down. It returned the correct results. Note that your sample file only has a few matching dates *and* you're missing some dates on the Shelf Life sheet. On the Prod sheet there are dates for 4/8, 4/9, 4/28, 4/29, 4/30. On the Shelf Life sheet the dates 4/28 and 4/29 are missing from column F. -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... neither one of those formulas seam to do what i want, so i added the acual sheets i'm working on. http://www.savefile.com/files/2073433 sheet 1 i was refering to is the production tab and sheet 2 is the shelf life tab so if date is entered in column D on production sheet it'll find it's matching date in column F on shelf life sheet, if true it'll add amount produced in column H in shelf life sheet on the same row as the matching date, if no date from column F is used in column D on production sheet it should say no production. "T. Valko" wrote: Enter this formula on Sheet2 C1: =IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
Your formula in column G already does that. Here's a shorter formula:
=IF(COUNT(F4,H4)=2,DATE(YEAR(F4)+1,MONTH(F4),DAY(F 4)),"") Format as Date -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... that seems to work great except when column H shows no prod i need (if possible) column G and I to be blank. "T. Valko" wrote: Here's the formula I entered in Shelf Life K4 I used column K so I could compare it with your results in column H. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Here's the formula I entered in Shelf Life K4: =IF(COUNTIF(PRODUCTION!D:D,F4),SUMIF(PRODUCTION!D: D,F4,PRODUCTION!F:F),"no prod") Copied down. It returned the correct results. Note that your sample file only has a few matching dates *and* you're missing some dates on the Shelf Life sheet. On the Prod sheet there are dates for 4/8, 4/9, 4/28, 4/29, 4/30. On the Shelf Life sheet the dates 4/28 and 4/29 are missing from column F. -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... neither one of those formulas seam to do what i want, so i added the acual sheets i'm working on. http://www.savefile.com/files/2073433 sheet 1 i was refering to is the production tab and sheet 2 is the shelf life tab so if date is entered in column D on production sheet it'll find it's matching date in column F on shelf life sheet, if true it'll add amount produced in column H in shelf life sheet on the same row as the matching date, if no date from column F is used in column D on production sheet it should say no production. "T. Valko" wrote: Enter this formula on Sheet2 C1: =IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
i figured out the problem, all my formulas are based on the words no
production, once i changed your formula from no prod to no production everything worked and works great. again thanks alot "Bowmanator" wrote: that seems to work great except when column H shows no prod i need (if possible) column G and I to be blank. "T. Valko" wrote: Here's the formula I entered in Shelf Life K4 I used column K so I could compare it with your results in column H. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Here's the formula I entered in Shelf Life K4: =IF(COUNTIF(PRODUCTION!D:D,F4),SUMIF(PRODUCTION!D: D,F4,PRODUCTION!F:F),"no prod") Copied down. It returned the correct results. Note that your sample file only has a few matching dates *and* you're missing some dates on the Shelf Life sheet. On the Prod sheet there are dates for 4/8, 4/9, 4/28, 4/29, 4/30. On the Shelf Life sheet the dates 4/28 and 4/29 are missing from column F. -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... neither one of those formulas seam to do what i want, so i added the acual sheets i'm working on. http://www.savefile.com/files/2073433 sheet 1 i was refering to is the production tab and sheet 2 is the shelf life tab so if date is entered in column D on production sheet it'll find it's matching date in column F on shelf life sheet, if true it'll add amount produced in column H in shelf life sheet on the same row as the matching date, if no date from column F is used in column D on production sheet it should say no production. "T. Valko" wrote: Enter this formula on Sheet2 C1: =IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
in need of a formula
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... i figured out the problem, all my formulas are based on the words no production, once i changed your formula from no prod to no production everything worked and works great. again thanks alot "Bowmanator" wrote: that seems to work great except when column H shows no prod i need (if possible) column G and I to be blank. "T. Valko" wrote: Here's the formula I entered in Shelf Life K4 I used column K so I could compare it with your results in column H. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Here's the formula I entered in Shelf Life K4: =IF(COUNTIF(PRODUCTION!D:D,F4),SUMIF(PRODUCTION!D: D,F4,PRODUCTION!F:F),"no prod") Copied down. It returned the correct results. Note that your sample file only has a few matching dates *and* you're missing some dates on the Shelf Life sheet. On the Prod sheet there are dates for 4/8, 4/9, 4/28, 4/29, 4/30. On the Shelf Life sheet the dates 4/28 and 4/29 are missing from column F. -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... neither one of those formulas seam to do what i want, so i added the acual sheets i'm working on. http://www.savefile.com/files/2073433 sheet 1 i was refering to is the production tab and sheet 2 is the shelf life tab so if date is entered in column D on production sheet it'll find it's matching date in column F on shelf life sheet, if true it'll add amount produced in column H in shelf life sheet on the same row as the matching date, if no date from column F is used in column D on production sheet it should say no production. "T. Valko" wrote: Enter this formula on Sheet2 C1: =IF(COUNTIF(Sheet1!A:A,A1),SUMIF(Sheet1!A:A,A1,She et1!B:B),"no work") Copy down to C365 -- Biff Microsoft Excel MVP "Bowmanator" wrote in message ... hi, i'm working on two sheets within the same book. sheet 1 A B 1/3/09 1000 1/4/09 500 1/6/09 100 as you can see on sheet 1 the dates can be days apart. on sheet 2. column A has 365 rows indicating every day of the year, starting with A1 being 1/1/09 and ending with A365 being 12/31/09 column C1 thru C365 has the words no work i want sheet 2 to look like this A C 1/1/09 no work 1/2/09 no work 1/3/09 1000 1/4/09 500 1/5/09 no work 1/6/09 100 so is there any way to get the date entered in A1 on sheet 1 to find the same date on sheet 2 A1 and enter the amount from B1 sheet1 into C1 sheet 2? and if there is no match on sheet 2, i need column C to show no work. thanks |
| All times are GMT +1. The time now is 08:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com