Auto filling data according to seperate worksheet

[mpenkala]

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

 

[Max]

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,MATCH(1,(Sheet2!$B$2:$B$100=$B2)*(Sheet2!$C$2:$C$100=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

 

[mpenkala]

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]

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:\Documents
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 ..

 

[mpenkala]

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

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:\Documents
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 ..

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]

You're welcome, Matt !

 

[mpenkala]

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]

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.