Need help with a formula pulling data from mutilple sheets

[walkerT]

What I am trying to do:

sheet 1,
C1 list the name of the business
B2 is PAYROLL
C2 is PREMIUM
D2 is FIXED COST,
A3 is THE YEAR 2003-04
A4 is THE YEAR 2004-05
A5 is THE YEAR 2005-06,

SHEET 2, WHICH IS YEAR 03-04
B2 is 03 PAYROLL
C2 IS THE Premium
D2 is the fixed cost
A3 is business A
A4 is business B
A5 is business C
A6 is business D,

SHEET 3, WHICH IS YEAR 04-05
B2 is 03 PAYROLL
C2 IS THE Premium
D2 is the fixed cost
A3 is business A
A4 is business B
A5 is business C
A6 is business D,

What I want to be able to do is on sheet 1 type in a business name, then
have the sheet pull information from years 03-04 and 04-05 with that busness
name for payroll, premium, and fixed cost for thos particular years. I have
not been able to create a formula that will even remotely work...

 

[Toppers]

See VLOOKUP function.

When you say C2 is PREMIUM is that a value or heading i.e column C contains
premiums for the list of businesses starting in A3?

 

[CHallisy]

You should be able to use a simple LOOKUP function, I think.

=Lookup(CELL WHERE YOU WOULD ENTER THE CO. NAME,WHERE YOU WANT IT TO
LOOK,DISPLAYED RESULT VECTOR)

So, for instance: If you type the name of the company into Sheet1!A1 and you
want it to look up payroll for that company from '03 you could something like
this:

=Lookup(A1,Sheet2!A3:A25,Sheet2!B3:B25)


I hope that made sense, and was what you wanted.

 

[walkerT]

Premium is the heading, I was trying to show that I would need the formula to
pick up the the premium amount for the spefic year as with the fixed cost and
payroll as well. If you can come up with a formula that would be great, if
not I will start working on the vlookup

 

[Toppers]

=VLOOKUP(C1,Sheet2!A1100,2,0) will give 03 payroll

=VLOOKUP(C1,Sheet2!A1100,3,0) will give 03 Premium

=VLOOKUP(C1,Sheet2!A1100,4,0) will give 03 Fixed cost

Change Sheet2 to Sheet3 for 04 results.

Assuming C1 is business name.

Change ranges to suit.

HTH

 

[walkerT]

tHANK YOU

You should be able to use a simple LOOKUP function, I think.

=Lookup(CELL WHERE YOU WOULD ENTER THE CO. NAME,WHERE YOU WANT IT TO
LOOK,DISPLAYED RESULT VECTOR)

So, for instance: If you type the name of the company into Sheet1!A1 and you
want it to look up payroll for that company from '03 you could something like
this:

=Lookup(A1,Sheet2!A3:A25,Sheet2!B3:B25)


I hope that made sense, and was what you wanted.

 

[walkerT]

Thank you~!

=VLOOKUP(C1,Sheet2!A1100,2,0) will give 03 payroll

=VLOOKUP(C1,Sheet2!A1100,3,0) will give 03 Premium

=VLOOKUP(C1,Sheet2!A1100,4,0) will give 03 Fixed cost

Change Sheet2 to Sheet3 for 04 results.

Assuming C1 is business name.

Change ranges to suit.

HTH