Entering information from 1 worksheet to another, if criteria is m

[Guest]

Here's my situation....I created a workbook that will be used for my
department budget. I have a worksheet that includes a summary of all 4 of my
accounts and 1 worksheet for each accounts for more detail (now at 5
worksheets). Then I created worksheets for each employee (over 60 worksheets)
so I can track the amount of money we spend on each employee per fiscal year
under the 4 accounts.

For example: Training Budget....I created a MASTER worksheet that will
include all training from everyone so that it's broken down my departments
and positions. I would like to be able to enter the information into the
MASTER worksheet and have it automatically enter the information on the
correct employee worksheet so that I won't need to enter the information
twice. The worksheets are titled by "initials" so I guess what I would need
to do is have it find the correct "initial" and have it enter the information
on the next available line. Sometimes, it'll be more than 1 employee
attending the same training so ideally, I would like to enter the information
once and have it automatically find the correct worksheets and enter the
information in each.

Layout example: A1=Initial of employee, B1=Date of training, C1=Purchase
Order #, D1=Description of training, E1=Cost, etc.

I tried doing something like this =IF(SUMMARY!$B4="JAJ",SUMMARY!D4,""), but
it didn't work because it would leave blanks on everybody elses worksheet, if
it was false. Also, my MASTER worksheet is not formatted the same.

PLEASE HELP, IF YOU CAN!!!!

 

[Guest]

Perhaps one way to automate it via non-array formulas ..

Try this recent post to a similar query:
http://tinyurl.com/fwlyy

The sample construct therein is still available at:
http://www.savefile.com/files/2544973
Auto-Extract Case Lines to Own Sheet by Doc Name.xls

 

[Guest]

Hi Max!

I checked out the spreadsheet and it looks like it may work, but I don't
understand how you created the formula. Would you kindly explain it for me so
I understand how the pieces go together. I'll need to know what it make it
work in my workbook

FORMULA: =IF(ISERROR(SMALL(OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1:$M$1,0)),
ROWS($A$1:A5))),"",INDEX(Z!A:A,MATCH(SMALL(OFFSET(Z!$J:$J,,
MATCH(WSN,Z!$K$1:$M$1,0)),ROWS($A$1:A5)),OFFSET(Z!$J:$J,,
MATCH(WSN,Z!$K$1:$M$1,0)),0)))

Thank you so much for your help!!!!

 

[Guest]

Perhaps a better way, Victoria ..

Could you upload a small, sanitized sample of your actual file,
and paste the link to it in reply here ?

Just provide 2 key sheets in the sample will do:
The "master"** sheet and a typical "individual" sheet
**what is named as sheet: Z in my sample

I'll help to set it up to suit and provide the link back to it here.
(the detailed explanations can come later, if still required)

You could use either of these 2 free n easy-to-use filehosts
to upload your sample:

http://www.flypicture.com/
http://cjoint.com/index.php

For cjoint.com (it's in French), just click "Browse" button,
navigate to your folder > select the sample file > Open,
then click the button centred in the page below
(labelled "Creer le lien Cjoint") and it'll generate the link.
Then just copy & paste the generated link as part
and parcel of your response here.

Kindly note that no attachments should be
posted *directly* to the newsgroup

 

[Guest]

Hi Max,

Here is the link: http://www.flypicture.com?display=updone&id=r9jxm6zZ

The "1101-6156" is the master sheet and the "PCA" and "KAB" are the
individual employee sheets. This is only a sample of the actual spreadsheet,
due to confidentiality, I couldn't post the full spreadsheet. The master
sheet will probably not look like that because I don't think the formula will
work with the current view.

I would still love to know how you came up with the formula so I can explain
it to my department and to know for future use.

Thanks in advance for all your help!!!

 

[Guest]

Here's a sample construct customized to suit
what you described in your original post:
http://www.savefile.com/files/9223060
Auto-Extract_Lines_to_Own_Sheet_by_EmployeeInitials.xls

(I'm afraid I wasn't able to use the sample you posted)

Here's the play which automates it using non-array formulas ..

In sheet: 1101-6156 (the "master" sheet containing all listings)

Assume data is in cols A to F, data in row2 down, with the key col = col B,
which contains the intials of the employees (eg: PCA, KAB, RG, LTT). The
layout is as described in your original post, except with a new col A
inserted for serial nos.

Using empty cols to the right,
List the employee initials in K1 across, in any order. Ensure these are
consistent with initials listed in col B and with initials on the sheet tabs
when these are named later. Watch out for any inconsistencies, typos,
extraneous white spaces, etc.

Then put in K2: =IF(OR($B2="",K$1=""),"",IF($B2=K$1,ROW(),""))
Copy across as far as required (by 60 cols to col BR for 60 employees),
fill down by as many rows as required to cover the max expected extent of
source data.

This table assigns arb row numbers to flag out the lines for each initial,
which numbers will then be read & picked up by the formulas in each
individual's sheet that we're going to set up later

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique taken from
a post by Harlan Grove.

In a new sheet named: PCA
With the same col headers pasted into A1:F1

Put in A2:
=IF(ISERROR(SMALL(OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX('1101-6156'!A:A,MATCH(SMALL(OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$IV$1,0)),0)))

Copy A2 across to F2, fill down to say, F11
(copy down by the smallest possible range sufficient
to cover the max expected extent for any individual. Here, I've assumed that
10 rows (rows 2 to 11) is sufficient)

Cols A to F will return only the lines for the initial: PCA from 1101-6156,
with all lines neatly bunched at the top. Dress up and format the cols to
taste

Now just make a copy of the sheet: PCA, rename it as the next initial: KAB,
and we'd get the results for that initial

Repeat the copy > rename sheet process to get the rest of the initials: LTT,
RG, etc ( a one-time job). Adapt to suit ..

----
Some further explanations ..
In the set-up is for the individual sheets:
With the same col headers pasted into A1:F1, the formula placed in A2,
copied across to F2, then filled down by the smallest extent sufficient to
cover the max number of lines for any one individual (filled down say, 10
lines):
=IF(ISERROR(SMALL(OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX('1101-6156'!A:A,MATCH(SMALL(OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$IV$1,0)),0)))

OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$L$1,0)) will return
the correct column array within the source sheet for the initial on the sheet
tab. The correct column number is returned via:
MATCH(WSN,'1101-6156'!$K$1:$L$1,0).
The defined range: WSN evaluates to return the initial on the sheet tab
itself, eg: PCA, which is then matched against the range of initials in
'1101-6156'!$K$1:$L$1 [Putting in any cell: =WSN in the sheet named: PCA
will return the sheetname in the cell: PCA]

In the starting cell in A2:
SMALL(OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$IV$1,0)),ROWS($A$1:A1))
will then return the smallest row number within the column array returned by
OFFSET(...), via the incrementer term: ROWS($A$1:A1) - this term evaluates to
1 [i.e. SMALL(array,1)]

When A2 is copied down, the incrementer term will become: ROWS($A$1:A2)
which then evaluates to 2, and SMALL(array,2) will then return the 2nd
smallest row number within the column array returned by OFFSET(...).
And so on as we copy down.

The col array's row numbers returned by SMALL(...)
are then matched against the actual row numbers within the col array itself
in the source sheet to return the correct row number for the indexed col A in
the source sheet, ie: INDEX('1101-6156'!A:A.<row number>) returns the item
within col A in the source sheet corresponding to the row number

The behaviour for col A is likewise propagated as we copy A2 across and down.
INDEX('1101-6156'!A:A, will change to INDEX('1101-6156'!B:B,
INDEX('1101-6156'!C:C, and so on, and this extracts the corresponding items
from cols B, C, ... F from the source sheet. [ROWS($A$1:A1) will change to
ROWS($A$1:B1), etc when copied across and ROWS($A$1:B2) when copied down, but
the results returned remain the same as what is happening in col A]

The front error trap: =IF(ISERROR(...),"", ..
ensures that neat blanks: "" are returned instead of ugly #NUM! errors once
all the lines for the initial have been extracted [we need only to trap the
result returned by SMALL(..) for the #NUM! errors]

---

 

[Guest]

Thank You Max for taking the time to help!!!

I will try the formulas with my layout and see if it work the same.

 

[Guest]

Thank You Max for taking the time to help!!!
I will try the formulas with my layout and see if it work the same.

You're welcome !
Thanks for the feedback ..

---

 

[Guest]

Typo correction ..
The horizontal range: '1101-6156'!$K$1:$L$1 referred to within lines:

OFFSET('1101-6156'!$J:$J,,MATCH(WSN,'1101-6156'!$K$1:$L$1,0)) will return
the correct column array within the source sheet for the initial on the sheet
tab. The correct column number is returned via:
MATCH(WSN,'1101-6156'!$K$1:$L$1,0).
The defined range: WSN evaluates to return the initial on the sheet tab
itself, eg: PCA, which is then matched against the range of initials in
'1101-6156'!$K$1:$L$1

should all read as: '1101-6156'!$K$1:$IV$1
(For consistency, as horiz. range was extended till col IV in the formulas)


---