# looking up by 2 cells

#### gtho351

##### New Member
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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)

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?

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

Bill
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!

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

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

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.

=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

Replies
1
Views
109
Replies
1
Views
156
Replies
0
Views
187
Replies
6
Views
499
Replies
1
Views
316

1,219,905
Messages
6,150,902
Members
450,991
Latest member
ExcelDoer

### 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.

### Which adblocker are you using?

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

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