what's the best way to do this?

[gbiwan]

Hi!
I'm going to apologize ahead of time if I'm asking wayyy too much from the
group... I'm very grateful for all the great answers that you have given me
in the past! But I'm now in a place that I need to get another
perspective... as i seem to have run into a brick wall...

I have a payroll worksheet that needs to be flexible on how many lines it
creates on the final sheet we send to payroll... depending on whether or not
data is entered in the employee's column
Everything is now depending on there being a employee number, name, job code
and payrate which works well... then I have a row for each of the data entry
columns to the left of the worksheet that uses a "if" formula to either
return an answer or leave blank (depending on whether or not there's data in
the reference cell)

I then read the info on another worksheet in the same workbook... and with
your great help I now sort the rows with data and put it onto another sheet
that I then copy to a new workbook with the original input sheet and send
that to HO... sounds confusing eh? that 's where I'm at... there needs to
be an easier way correct?

a b c d e f g
h i j k l m n o p q r s t u v
w x y z
Employee Full Name Job Pay 1 2 3 4 5 6 7
8 9 10 11 12 13 14 15 Reg OT Stat Prem Grat
Other
Number Code Rate 16 17 18 19 20 21 22 23
24 25 26 27 28 29 30 31 Hr Hr Hr Stat

0002 EEE, fred 01 $7.00 8.00
8.00 1.00 8.00 2.00 3.00 4.00


Above is an example of the input sheet... below is what I need to change
that data into... again only when there is info on that pay period for that
earning code

a b c d e f
g h i j k l
m n
Emp| Emp Name | Ent Dt |Earning\Ded| hrs | hrly/sal | Rate | Earn Amt |Ben
Amt | Ded Amt| G/L Meth| G/L Grp| G/L Account | G/L Ben Group
Code
0002 EEE,fred (blank) 1 8 H 7
A 8800-01-021-06-023 1
0002 EEE,fred 2 1 H 10.5
A 8800-01-021-06-023 1
0002 EEE,fred 21 8 H 7
A 8800-01-021-06-023 1
0002 EEE,fred 20 2 H 10.5
A 8800-01-021-06-023 1
0002 EEE,fred 201 1 A
4 A
8800-01-021-06-023 1
0002 EEE,fred 221 1 A
3 A
8800-01-021-06-023 1

column "A" is always the employee number,
Column "B" is the employee name,
column "C" is always blank,
column "D" - reg hr= 1, OT hr= 2, STAT hr= 21, Prem STAT= 20, Grat= 221,
and Other= 201

201 and 221 always have 1 in column "E", A in Column "F" and the data sheet
from column "Y" (221) or "Z" (201)(from the input sheet) on this sheet's
Column "H"
column "E" is the hours from the data sheet
Column "F" is either H or A (A for 201&221)
Column "G" is the rate of pay (and code 2 & 20 @ 1 1/2 times)
Column "H" amounts from code #201 & 221
Column "I" "J" "L" are always blank
Column "K" is always A
Column "M" is the calculated by the Job code from the data sheet info
(column "C" for those still with us)
Column "N" is always 1

so is there a way to create a lookup or another way to create these lines on
a worksheet with no blanks? currently I need to create 6 lines for every
line of employee and some of these need 125 employees...

Now I would have left this alone (as the sheet was working OK) but there are
still a few users that are finding ways to muck up the sheet and so I
thought that if there was an easier way to keep the workbook fairly small
and still do the same things then it would be a great leap forward! And
save me a lot of "fixing" time...

Anybody have any ideas?

Thanks in advance for your help!

Greg

 

[Paul Robinson]

Hi
I didn't read your post because it looked terribly long - I'd guess a
lot of others thought the same...
Several short posts about specific problems are much more likely to be
answered. Also, maybe you could write down a much simplified version
of your problem, say involving only 2 or 3 columns of data. It should
be easy enough for you to work out how to extend the
answers/suggestions to your larger problem.

regards
Paul

 

[Greg Liber]

Thanks Paul!

I'll try to reduce the clutter... the problem is that this challenge seems
to me to be one issue... how do I take information from one row and create
up to 6 rows of information? The way I'm doing it now consists of 6 rows of
"IF" formulas for each input line and this gets messy when we have 125 rows
of data...(or 750 rows of "IF" formulas)

Anybody have an idea where I should start?

Thanks in advance for your time!

Greg