Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data from 1 sheet to the next
I need help on this project; conditional formatting does not work on different sheets and I do not know how
to change the formulas or what formula to use, that will work please help. B1 this CELL equals 09/04/2006 Sheet1 D5 E5 F5 G5 H5 I5 J5 K5 L5 M5 8 09/04/2006 17/04/2006 X X X X X X X 4 10/04/2006 14/04/2006 X X X X X I would like this result on Sheet2. It should be the extra day from Sheet1 being placed in D5 (8-7 the days of the week in G5 to M5) and the date (Sheet1 F5 -1) into Sheet2 E5, which would put correct date in F5 (=E5+D5) which in turn would place a (X) in G5 and H5. Also the Site Number (A5) & Names (B5 & C5) would need to be copied over from Sheet1 to Sheet2 to match the row. Sheet2 D5 E5 F5 G5 H5 I5 J5 K5 L5 M5 D5 E5 1 16/04/2006 17/04/2006 X X This is what each Cell contains. A5 - SITE number (001, A6 is 002 ect..) B5 - Last Name C5 - First Name D5 - This is entered number E5 - This is date entered (09/04/2006) F5 - formula is =E5+D5 (resulting in 17/04/2006) The next seven are day of the week starting with Sunday -ending with Saturday. Based on the formulas in the next CELLS a (X) is placed in the CELL for each day that corresponds with the week in each sheet. The formula below was provided by Peo Sjoblom Thanks again. G5 - =IF(AND($B$1=E5,$B$1<=F5),"X","") H5 - =IF(AND($B$1+1=E5,$B$1+1<=F5),"X","") I5 - =IF(AND($B$1+2=E5,$B$1+2<=F5),"X","") J5 - =IF(AND($B$1+3=E5,$B$1+3<=F5),"X","") K5 - =IF(AND($B$1+4=E5,$B$1+4<=F5),"X","") L5 - =IF(AND($B$1+5=E5,$B$1+5<=F5),"X","") M5 - =IF(AND($B$1+6=E5,$B$1+6<=F5),"X","") thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data from 1 sheet to the next
Could you please post sample data (for example on http://cjoint.com)
It's hard to understand what you have and what you want. Regards, -- AP "dpatte601" a écrit dans le message de ... I need help on this project; conditional formatting does not work on different sheets and I do not know how to change the formulas or what formula to use, that will work please help. B1 this CELL equals 09/04/2006 Sheet1 D5 E5 F5 G5 H5 I5 J5 K5 L5 M5 8 09/04/2006 17/04/2006 X X X X X X X 4 10/04/2006 14/04/2006 X X X X X I would like this result on Sheet2. It should be the extra day from Sheet1 being placed in D5 (8-7 the days of the week in G5 to M5) and the date (Sheet1 F5 -1) into Sheet2 E5, which would put correct date in F5 (=E5+D5) which in turn would place a (X) in G5 and H5. Also the Site Number (A5) & Names (B5 & C5) would need to be copied over from Sheet1 to Sheet2 to match the row. Sheet2 D5 E5 F5 G5 H5 I5 J5 K5 L5 M5 D5 E5 1 16/04/2006 17/04/2006 X X This is what each Cell contains. A5 - SITE number (001, A6 is 002 ect..) B5 - Last Name C5 - First Name D5 - This is entered number E5 - This is date entered (09/04/2006) F5 - formula is =E5+D5 (resulting in 17/04/2006) The next seven are day of the week starting with Sunday -ending with Saturday. Based on the formulas in the next CELLS a (X) is placed in the CELL for each day that corresponds with the week in each sheet. The formula below was provided by Peo Sjoblom Thanks again. G5 - =IF(AND($B$1=E5,$B$1<=F5),"X","") H5 - =IF(AND($B$1+1=E5,$B$1+1<=F5),"X","") I5 - =IF(AND($B$1+2=E5,$B$1+2<=F5),"X","") J5 - =IF(AND($B$1+3=E5,$B$1+3<=F5),"X","") K5 - =IF(AND($B$1+4=E5,$B$1+4<=F5),"X","") L5 - =IF(AND($B$1+5=E5,$B$1+5<=F5),"X","") M5 - =IF(AND($B$1+6=E5,$B$1+6<=F5),"X","") thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data from 1 sheet to the next
In Sheet2 Cell B1
=IF(Sheet1!B1<"",Sheet1!B1,"") You can actually just autofill the entire Sheet2 to Sheet2, If started from A1 throughout. Then you can delete the columns and/or rows you do not need. "dpatte601" wrote: I need help on this project; conditional formatting does not work on different sheets and I do not know how to change the formulas or what formula to use, that will work please help. B1 this CELL equals 09/04/2006 Sheet1 D5 E5 F5 G5 H5 I5 J5 K5 L5 M5 8 09/04/2006 17/04/2006 X X X X X X X 4 10/04/2006 14/04/2006 X X X X X I would like this result on Sheet2. It should be the extra day from Sheet1 being placed in D5 (8-7 the days of the week in G5 to M5) and the date (Sheet1 F5 -1) into Sheet2 E5, which would put correct date in F5 (=E5+D5) which in turn would place a (X) in G5 and H5. Also the Site Number (A5) & Names (B5 & C5) would need to be copied over from Sheet1 to Sheet2 to match the row. Sheet2 D5 E5 F5 G5 H5 I5 J5 K5 L5 M5 D5 E5 1 16/04/2006 17/04/2006 X X This is what each Cell contains. A5 - SITE number (001, A6 is 002 ect..) B5 - Last Name C5 - First Name D5 - This is entered number E5 - This is date entered (09/04/2006) F5 - formula is =E5+D5 (resulting in 17/04/2006) The next seven are day of the week starting with Sunday -ending with Saturday. Based on the formulas in the next CELLS a (X) is placed in the CELL for each day that corresponds with the week in each sheet. The formula below was provided by Peo Sjoblom Thanks again. G5 - =IF(AND($B$1=E5,$B$1<=F5),"X","") H5 - =IF(AND($B$1+1=E5,$B$1+1<=F5),"X","") I5 - =IF(AND($B$1+2=E5,$B$1+2<=F5),"X","") J5 - =IF(AND($B$1+3=E5,$B$1+3<=F5),"X","") K5 - =IF(AND($B$1+4=E5,$B$1+4<=F5),"X","") L5 - =IF(AND($B$1+5=E5,$B$1+5<=F5),"X","") M5 - =IF(AND($B$1+6=E5,$B$1+6<=F5),"X","") thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data from 1 sheet to the next
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data from 1 sheet to the next
You will need some VBA code to fill your worksheet automatically
This can't be done with worksheet functions only. Do you want me to make a try? -- Regards, -- AP "dpatte601" a écrit dans le message de ... I posted the sample here please take a look. http://cjoint.com/?esfQSjBqKX |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data from 1 sheet to the next
I was wrong: I could do it with formulaes.
See example: http://cjoint.com/?eskQPqwOqg HTH -- AP "dpatte601" a écrit dans le message de ... I posted the sample here please take a look. http://cjoint.com/?esfQSjBqKX |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data from 1 sheet to the next
"Ardus Petus" wrote in news:u3rwtQsYGHA.3848
@TK2MSFTNGP05.phx.gbl: http://cjoint.com/?eskQPqwOqg this is just what I was looking for your great and fast - how long have you been doing this for? thanks so much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Combining data from cells from several excel sheets to a new sheet | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Merger Two Data Sheet | Excel Discussion (Misc queries) |