software

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 quot;initialsquot; so I guess what I would need
to do is have it find the correct quot;initialquot; 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=quot;JAJquot;,SUMMARY!D4,quot;quot;), 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!!!!Perhaps one way to automate it via non-array formulas ..

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

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

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Victoriaquot; wrote:
gt; Here's my situation....I created a workbook that will be used for my
gt; department budget. I have a worksheet that includes a summary of all 4 of my
gt; accounts and 1 worksheet for each accounts for more detail (now at 5
gt; worksheets). Then I created worksheets for each employee (over 60 worksheets)
gt; so I can track the amount of money we spend on each employee per fiscal year
gt; under the 4 accounts.
gt;
gt; For example: Training Budget....I created a MASTER worksheet that will
gt; include all training from everyone so that it's broken down my departments
gt; and positions. I would like to be able to enter the information into the
gt; MASTER worksheet and have it automatically enter the information on the
gt; correct employee worksheet so that I won't need to enter the information
gt; twice. The worksheets are titled by quot;initialsquot; so I guess what I would need
gt; to do is have it find the correct quot;initialquot; and have it enter the information
gt; on the next available line. Sometimes, it'll be more than 1 employee
gt; attending the same training so ideally, I would like to enter the information
gt; once and have it automatically find the correct worksheets and enter the
gt; information in each.
gt;
gt; Layout example: A1=Initial of employee, B1=Date of training, C1=Purchase
gt; Order #, D1=Description of training, E1=Cost, etc.
gt;
gt; I tried doing something like this =IF(SUMMARY!$B4=quot;JAJquot;,SUMMARY!D4,quot;quot;), but
gt; it didn't work because it would leave blanks on everybody elses worksheet, if
gt; it was false. Also, my MASTER worksheet is not formatted the same.
gt;
gt; PLEASE HELP, IF YOU CAN!!!!
gt;

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))),quot;quot;,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!!!!

quot;Maxquot; wrote:

gt; Perhaps one way to automate it via non-array formulas ..
gt;
gt; Try this recent post to a similar query:
gt; tinyurl.com/fwlyy
gt;
gt; The sample construct therein is still available at:
gt; www.savefile.com/files/2544973
gt; Auto-Extract Case Lines to Own Sheet by Doc Name.xls
gt;
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik

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 quot;masterquot;** sheet and a typical quot;individualquot; 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:

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

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

Kindly note that no attachments should be
posted *directly* to the newsgroup
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Victoriaquot; wrote:
gt; Hi Max!
gt;
gt; I checked out the spreadsheet and it looks like it may work, but I don't
gt; understand how you created the formula. Would you kindly explain it for me so
gt; I understand how the pieces go together. I'll need to know what it make it
gt; work in my workbook
gt;
gt; FORMULA: =IF(ISERROR(SMALL(OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1 :$M$1,0)),
gt; ROWS($A$1:A5))),quot;quot;,INDEX(Z!A:A,MATCH(SMALL(OFFSET( Z!$J:$J,,
gt; MATCH(WSN,Z!$K$1:$M$1,0)),ROWS($A$1:A5)),OFFSET(Z! $J:$J,,
gt; MATCH(WSN,Z!$K$1:$M$1,0)),0)))
gt;
gt; Thank you so much for your help!!!!

Hi Max,

Here is the link: www.flypicture.com?display=updoneamp;id=r9jxm6zZ

The quot;1101-6156quot; is the master sheet and the quot;PCAquot; and quot;KABquot; 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!!!

quot;Maxquot; wrote:

gt; Perhaps a better way, Victoria ..
gt;
gt; Could you upload a small, sanitized sample of your actual file,
gt; and paste the link to it in reply here ?
gt;
gt; Just provide 2 key sheets in the sample will do:
gt; The quot;masterquot;** sheet and a typical quot;individualquot; sheet
gt; **what is named as sheet: Z in my sample
gt;
gt; I'll help to set it up to suit and provide the link back to it here.
gt; (the detailed explanations can come later, if still required)
gt;
gt; You could use either of these 2 free n easy-to-use filehosts
gt; to upload your sample:
gt;
gt; www.flypicture.com/
gt; cjoint.com/index.php
gt;
gt; For cjoint.com (it's in French), just click quot;Browsequot; button,
gt; navigate to your folder gt; select the sample file gt; Open,
gt; then click the button centred in the page below
gt; (labelled quot;Creer le lien Cjointquot;) and it'll generate the link.
gt; Then just copy amp; paste the generated link as part
gt; and parcel of your response here.
gt;
gt; Kindly note that no attachments should be
gt; posted *directly* to the newsgroup
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik

Here's a sample construct customized to suit
what you described in your original post:
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 quot;masterquot; 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=quot;quot;,K$1=quot;quot;),quot;quot;,IF($B2=K$1,ROW(),quot;quot;))
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 amp; picked up by the formulas in each
individual's sheet that we're going to set up later

Click Insert gt; Name gt; Define
Put under quot;Names in workbook:quot;: WSN
Put in the quot;Refers to:quot; box:
=MID(CELL(quot;Filenamequot;,INDIRECT(quot;A1quot;)),FIND(quot;]quot;,CELL(quot;Filenamequot;,INDIRECT(quot;A1quot;)
)) 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))),quot;quot;,INDEX('110 1-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 gt; 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))),quot;quot;,INDEX('110 1-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.lt;row numbergt;) 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(...),quot;quot;, ..
ensures that neat blanks: quot;quot; 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]
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

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

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

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

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

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

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---