looking up by 2 cells

gtho351

New Member
Joined
Aug 24, 2005
Messages
27
Hi guys and girls
I'm having a tought time trying to work out how to get excel to pick a cell above my referance cell.
I have a roster sytem that I am trying to link to another Time Sheet where my staff hours are set. As it stands now I have to enter there start and finish times manualy if I could get a formula to work so that it would pick the staff members start and finish tim and transfer that to the wages sheet my life would be so much easier. I already have the name recognition stuff sorted but as i have said getting it to refer to the cell 2 spaces above mmmmm diferent story.
Any help would be greatfully appreciated
Cheers
Bill
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Hi Bill

You can use offset for this.
Don't know your details, so I've put 1 to 5 in A1 to A5 on a sheet.

Let's say I want to find what's in 2 rows above A4:

In a cell where I want the answer, I put the formula:

=OFFSET(A4,-2,0)

Answer will be 2.

A4 is the reference cell, -2 is the number of rows to offset, 0 is the number of columns to offset. Change to your requirements.

Is this what you want?
 

gtho351

New Member
Joined
Aug 24, 2005
Messages
27
Hi Sykes

Thanks heaps that does exactly what i want all i need to do (if it can be done is the OFFSET to reference of the found cell. Here is the example

=SUM((B8:B33=J6)*OFFSET(B18,-2,0))

Where J6 is Glenn and his position in B8:B33 is B18
Is there someway to get the OFFSET formula to pick that up as the staff wander around the roster depending on needs and who is sick
That is why I've used =SUM((B8:B33=J6)* (a small section of the whole array formula) save me changing and chasing names all through the roster

Cheers
Bill
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Bill
Glad it's what you want.
Unfortunately I don't understand your next question. I'm normally quite good at visualising what a poster has, or is working with, but I'm stumped here.
Try to bear in mind that other folk have no idea about your project, and don't have it in front of them to refer to, so we rely entirely on your description of it, and what you want to achieve within it.
It could be that I'm just being thick, and someone else will come along in a minute and offer a solution, if so great! Otherwise you'll have to give us a bit more of a clue mate!
 

gtho351

New Member
Joined
Aug 24, 2005
Messages
27

ADVERTISEMENT

Sorry Sykes I've tried to post the excel sheet but for whatever reason it wont do it
 

gtho351

New Member
Joined
Aug 24, 2005
Messages
27

ADVERTISEMENT

Finaly

This hopefully will show some of what I am trying to do
Your formula and mine together =SUM((B8:B33=J6)*OFFSET(B6,-2,0))
Gives me the right result of her start time But I have a seperate Time Sheet that all the staff start and finish times get entered. I want this to happen automaticly so Is there a way that we can get the B6 referance in your formula automaticly where as I would have to enter that in manualy at the moment

Cheers
Bill
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Bill

Well done for getting it to work!
Unfortunately I'm drawing a blank here. I just can't visualise the problem.

Can anyone else come in here, and offer a solution?
I'm sure it can be done, it just needs someone to understand Bill's needs.
 

gtho351

New Member
Joined
Aug 24, 2005
Messages
27
=SUM((B8:B33=Marlene)*OFFSET(B6,-2,0))
How do I get B6 to happen without me entering it manualy
This is frustrating I know exactly what I want to happen but I can't explane it
God I feel so dopey sometimes
 

Forum statistics

Threads
1,141,757
Messages
5,708,340
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top