Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
Okay - so I've got a toughie of a question and hope someone can help me out.
Here's the low down... Worksheet1 is a simple spreadsheet which lists Employee Names in Column 'A', Employee # in Column 'B' and each day of the month from column 'C' on... The spreadsheet has 6 months of this on each worksheet. Worksheet2 is a simple spreadsheet with Employee Names in Column 'A', Employee # in Column 'B', Date worked in Column 'C' and Hours worked in Column 'D'. Now Worksheet2 is a report that I get from another department. What I need to do is take the hours worked from worksheet2 and enter them into worksheet1. I currently do this manually, but am hoping there is a way (maybe macros) to have excel do it automatically. What I need is for worksheet1 to see which employee worked on what day (from worksheet2) and fill in the data on worksheet1 in the appropriate areas. Any ideas or comments would be greatly appriciated. Thanks, Matt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
One way which should deliver it ..
Assuming data in both Sheet1 and Sheet2 start in row 2 down We'll use Emp# to match since this is the unique key In Sheet1, Put in C2's formula bar, then array-enter** by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(ISNA(MATCH(1,(Sheet2!$B$2:$B$100=$B2)*(Sheet2! $C$2:$C$100=C$1),0)),"",INDEX(Sheet2!$D$2:$D$100,M ATCH(1,(Sheet2!$B$2:$B$100=$B2)*(Sheet2!$C$2:$C$10 0=C$1),0))) Copy C2 across as many cols as there are dates in row1, then just fill down as far as required to populate the grid. **Correctly done, Excel will insert curly braces: { } around the formula. Confirm this is so by viewing the formula bar. If you don't see the curly braces, then it hasn't been correctly array-entered. Adjust the ranges to suit the actual extents before you copy C2 across/down: Sheet2!$B$2:$B$100 Sheet2!$D$2:$D$100 Sheet2!$C$2:$C$100 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mpenkala" wrote: Okay - so I've got a toughie of a question and hope someone can help me out. Here's the low down... Worksheet1 is a simple spreadsheet which lists Employee Names in Column 'A', Employee # in Column 'B' and each day of the month from column 'C' on... The spreadsheet has 6 months of this on each worksheet. Worksheet2 is a simple spreadsheet with Employee Names in Column 'A', Employee # in Column 'B', Date worked in Column 'C' and Hours worked in Column 'D'. Now Worksheet2 is a report that I get from another department. What I need to do is take the hours worked from worksheet2 and enter them into worksheet1. I currently do this manually, but am hoping there is a way (maybe macros) to have excel do it automatically. What I need is for worksheet1 to see which employee worked on what day (from worksheet2) and fill in the data on worksheet1 in the appropriate areas. Any ideas or comments would be greatly appriciated. Thanks, Matt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
Hi Max,
Thanks for the formula, it worked great! Now, since I'm a bugger and want to make this even more complicated. Is it possible to have the data on Sheet2 come from another workbook? The reports that contain the data in Sheet2 comes from an external source once a month. To make your formula work, I simply copy/pasted the data into a blank worksheet. But I am hoping it's possible to grab this data from a folder (on my desktop) instead of having to copy/paste it. And if it is possible, can I set it up to retrieve the data from multiple workbooks instead of just one? (So I can get the data from each month, instead of just one month). Thanks again for your help - very impressive! Matt P. "Max" wrote: One way which should deliver it .. Assuming data in both Sheet1 and Sheet2 start in row 2 down We'll use Emp# to match since this is the unique key In Sheet1, Put in C2's formula bar, then array-enter** by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(ISNA(MATCH(1,(Sheet2!$B$2:$B$100=$B2)*(Sheet2! $C$2:$C$100=C$1),0)),"",INDEX(Sheet2!$D$2:$D$100,M ATCH(1,(Sheet2!$B$2:$B$100=$B2)*(Sheet2!$C$2:$C$10 0=C$1),0))) Copy C2 across as many cols as there are dates in row1, then just fill down as far as required to populate the grid. **Correctly done, Excel will insert curly braces: { } around the formula. Confirm this is so by viewing the formula bar. If you don't see the curly braces, then it hasn't been correctly array-entered. Adjust the ranges to suit the actual extents before you copy C2 across/down: Sheet2!$B$2:$B$100 Sheet2!$D$2:$D$100 Sheet2!$C$2:$C$100 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mpenkala" wrote: Okay - so I've got a toughie of a question and hope someone can help me out. Here's the low down... Worksheet1 is a simple spreadsheet which lists Employee Names in Column 'A', Employee # in Column 'B' and each day of the month from column 'C' on... The spreadsheet has 6 months of this on each worksheet. Worksheet2 is a simple spreadsheet with Employee Names in Column 'A', Employee # in Column 'B', Date worked in Column 'C' and Hours worked in Column 'D'. Now Worksheet2 is a report that I get from another department. What I need to do is take the hours worked from worksheet2 and enter them into worksheet1. I currently do this manually, but am hoping there is a way (maybe macros) to have excel do it automatically. What I need is for worksheet1 to see which employee worked on what day (from worksheet2) and fill in the data on worksheet1 in the appropriate areas. Any ideas or comments would be greatly appriciated. Thanks, Matt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
Matt
Thanks for the formula, it worked great! Glad to hear that ! Is it possible to have the data on Sheet2 come from another workbook? Yes, but it'd make the formula much, much longer since we'd need to now contend with having (ugh!) filepaths and filenames in the formula. I'd keep it v.simple and go with the manual copy n paste which takes only a few seconds to effect. Maybe shorten the sheetname for that matter, eg: use "X" instead of "Sheet2", which shortens the working formula even further <g. You can experiment with the above in this way, leaving it to Excel to adjust the formulas for you: Right-click on Sheet2 Move or Copy New book OK. Then save the new book as the desired filename (this filename could be very longish ..) to your destination folder (this folder can be deeply nested, I'd figure <g). Close the book. Now go back to your file's Sheet1's C2. The formula in C2 would now look something like this: =IF(ISNA(MATCH(1,('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$B$2:$B$100=$B2)*('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$C$2:$C$100=C$1),0)),"",INDEX('C:\Document s and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$D$2:$D$100,MATCH(1,('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$B$2:$B$100=$B2)*('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$C$2:$C$100=C$1),0))) Ok, I don't know about you, but I almost always feel a tsunamic migraine coming on looking at this kind of formula. Don't ask me to debug this, please, I give up .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mpenkala" wrote in message ... Hi Max, Thanks for the formula, it worked great! Now, since I'm a bugger and want to make this even more complicated. Is it possible to have the data on Sheet2 come from another workbook? The reports that contain the data in Sheet2 comes from an external source once a month. To make your formula work, I simply copy/pasted the data into a blank worksheet. But I am hoping it's possible to grab this data from a folder (on my desktop) instead of having to copy/paste it. And if it is possible, can I set it up to retrieve the data from multiple workbooks instead of just one? (So I can get the data from each month, instead of just one month). Thanks again for your help - very impressive! Matt P. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
Hey Max,
Thanks again for the help. Wow - that formula is crazy long. I think I'll stick with your advice and just stick to the copy paste. I renamed Sheet2 to 'OT' which simplified it a little. Thanks again - I've learned a ton already! Matt "Max" wrote: Matt Thanks for the formula, it worked great! Glad to hear that ! Is it possible to have the data on Sheet2 come from another workbook? Yes, but it'd make the formula much, much longer since we'd need to now contend with having (ugh!) filepaths and filenames in the formula. I'd keep it v.simple and go with the manual copy n paste which takes only a few seconds to effect. Maybe shorten the sheetname for that matter, eg: use "X" instead of "Sheet2", which shortens the working formula even further <g. You can experiment with the above in this way, leaving it to Excel to adjust the formulas for you: Right-click on Sheet2 Move or Copy New book OK. Then save the new book as the desired filename (this filename could be very longish ..) to your destination folder (this folder can be deeply nested, I'd figure <g). Close the book. Now go back to your file's Sheet1's C2. The formula in C2 would now look something like this: =IF(ISNA(MATCH(1,('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$B$2:$B$100=$B2)*('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$C$2:$C$100=C$1),0)),"",INDEX('C:\Document s and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$D$2:$D$100,MATCH(1,('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$B$2:$B$100=$B2)*('C:\Documents and Settings\mpenkala\Desktop\[Book2.xls]Sheet2'!$C$2:$C$100=C$1),0))) Ok, I don't know about you, but I almost always feel a tsunamic migraine coming on looking at this kind of formula. Don't ask me to debug this, please, I give up .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mpenkala" wrote in message ... Hi Max, Thanks for the formula, it worked great! Now, since I'm a bugger and want to make this even more complicated. Is it possible to have the data on Sheet2 come from another workbook? The reports that contain the data in Sheet2 comes from an external source once a month. To make your formula work, I simply copy/pasted the data into a blank worksheet. But I am hoping it's possible to grab this data from a folder (on my desktop) instead of having to copy/paste it. And if it is possible, can I set it up to retrieve the data from multiple workbooks instead of just one? (So I can get the data from each month, instead of just one month). Thanks again for your help - very impressive! Matt P. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
You're welcome, Matt !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mpenkala" wrote in message ... Hey Max, Thanks again for the help. Wow - that formula is crazy long. I think I'll stick with your advice and just stick to the copy paste. I renamed Sheet2 to 'OT' which simplified it a little. Thanks again - I've learned a ton already! Matt |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
Hey Max (and other readers),
just thought I'd post and say that I ended up scrapping this layout as the formula Max wrote slowed down my computer too much. Could just be me and my crappy computer (cause the formula does work great). I've tried simplifying the table and hopefully will get an answer on my NEW problem soon!! Thanks again all! Matt "Max" wrote: You're welcome, Matt ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mpenkala" wrote in message ... Hey Max, Thanks again for the help. Wow - that formula is crazy long. I think I'll stick with your advice and just stick to the copy paste. I renamed Sheet2 to 'OT' which simplified it a little. Thanks again - I've learned a ton already! Matt |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filling data according to seperate worksheet
No prob, good luck with your re-design !
But do note that there are 2 things that could be done to keep things manageable / workable 1. Set the book's calc mode to Manual Click Tools Options Calculation tab Check "Manual" OK Then we can press F9 to recalc only as and when required, eg after all the daily new data entries, data updates, etc are completed. In this way the calc/recalc won't auto-kick in after each data entry and we can complete all data entries etc easily w/o fuss. 2. Use the smallest range sizes possible in the array expression. The larger the range sizes, the slower the recalc. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mpenkala" wrote in message ... Hey Max (and other readers), just thought I'd post and say that I ended up scrapping this layout as the formula Max wrote slowed down my computer too much. Could just be me and my crappy computer (cause the formula does work great). I've tried simplifying the table and hopefully will get an answer on my NEW problem soon!! Thanks again all! Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Filling In Corresponding Data from Another Worksheet | Excel Discussion (Misc queries) | |||
Look up Data from Worksheet within same Workbook | Excel Discussion (Misc queries) | |||
Look up Data from Worksheet within same Workbook | New Users to Excel | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) |