Would like some help on what formulas to use to have my payroll worksheet feed into two seperate worksheets.

Basically I have a worksheet where the header is set as one of two companies. Under this will be an incremental run number, then columns with relevant payroll data.

Example below.

Company AXB ATA ATA ATA AXB
Run 1 2 3 4 5
PayData 5555 6666 777 888 999
PayData 5555 6666 777 888 999
PayData 5555 6666 777 888 999

Basically I want one worksheet for company AXB, and one worksheet for company ATA, with data copied into each based on the company heading, so only the main worksheet with both companies is fed data. Am using excel 2003.

Hi Jason,

Am Thu, 2 Oct 2014 05:35:26 +0100 schrieb JasonL:

Company AXB ATA ATA ATA AXB
Run 1 2 3 4 5
PayData 5555 6666 777 888 999
PayData 5555 6666 777 888 999
PayData 5555 6666 777 888 999

for AXB
in A1: =INDEX(Master!A:A,ROW())
in B1: =INDEX(Master!B:B,ROW())
in C1: =INDEX(Master!F:F,ROW())
and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I want to avoid this if possible. The above was an example, I have way more than 5 columns. Company order is somewhat random, so I don't want to have to find each column that relates to each company and manually insert that in the formula at the top of each column. I want this to be an automated process if possible, the above way I'd have to be manually adjusting the formulas as I fill out the master sheet every time I run pays depending on which company it is for.

Hi Jason,

Am Thu, 2 Oct 2014 08:49:47 +0100 schrieb JasonL:

I want to avoid this if possible. The above was an example, I have way
more than 5 columns. Company order is somewhat random, so I don't want
to have to find each column that relates to each company and manually
insert that in the formula at the top of each column. I want this to be
an automated process if possible, the above way I'd have to be manually
adjusting the formulas as I fill out the master sheet every time I run
pays depending on which company it is for.

for both companies in A1:
=INDEX(Master!A:A,ROW())
For "ABX in B1:
=IFERROR(INDEX(Master!$A$1:$F$2000,ROW(),SMALL(IF( Master!$1:$1="AXB",COLUMN($1:$1)),COLUMN(A1))),"")
enter the array formula with CTRL+Shift+Enter and copy to the right and
down.
Same formula with "ATA" for the other company


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, October 1, 2014 11:35:26 PM UTC-5, JasonL wrote:
Would like some help on what formulas to use to have my payroll

worksheet feed into two seperate worksheets.



Basically I have a worksheet where the header is set as one of two

companies. Under this will be an incremental run number, then columns

with relevant payroll data.



Example below.



Company AXB ATA ATA ATA AXB

Run 1 2 3 4 5

PayData 5555 6666 777 888 999

PayData 5555 6666 777 888 999

PayData 5555 6666 777 888 999



Basically I want one worksheet for company AXB, and one worksheet for

company ATA, with data copied into each based on the company heading, so

only the main worksheet with both companies is fed data. Am using excel

2003.









--

JasonL

Easiest way is to NOT make a separate sheet but just use datafilterautofilter to filter by the company desired and print.

This formula comes up with a name error?
How do you use a autofilter to filter out columns? In excel 2003? Can only filter out rows as far as I know.

It wouldn't work for my worksheet anyway. The payroll data below the company and run headers includes formulas to total up multiple columns and sections of pay data. Those cells would still show totals for both companies even with one company filtered out.

Hi Jason,

Am Fri, 3 Oct 2014 08:20:04 +0100 schrieb JasonL:

This formula comes up with a name error?

look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Small"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

It's obviously an issue with using such an old version of excel. As saving your file and converting to excel 2003 results in #name? errors for all the iferror formulas. Unfortunately we aren't getting upgraded to the newest version of excel for another 12-14 months.

Hi Jason,

Am Mon, 6 Oct 2014 03:08:11 +0100 schrieb JasonL:

It's obviously an issue with using such an old version of excel. As
saving your file and converting to excel 2003 results in #name? errors
for all the iferror formulas. Unfortunately we aren't getting upgraded
to the newest version of excel for another 12-14 months.

I did not know that you use xl2003. Have another look for your workbook
on OneDrive. I changed IFERROR to IF(ISERROR..


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional