# Using Offset

#### DougStroud

##### Well-known Member
I think this is the worksheet function to use, but not positive.

I have employee names in row 1 every other column starting in U1. So U1, X1, AA1 etc...

I would like to reference these employees down column A. Rather than enter the physical cell reference for each employee name down column A, I was thinking that using the offset somehow would work. But not sure how to get started.

thanks,

ds

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### NBVC

##### Well-known Member
You mean something like this?

=INDEX(\$V\$1:\$AX\$1,2*(ROWS(\$A\$1:A1)-1)+1) copied down

Where V1:AX1 is the range of employee names in row 1.

#### Peter_SSs

##### MrExcel MVP, Moderator
You may need a slight modifications to NBVC's formula as my understanding is that the names start in U1 not V1 and they seem to be placed every 3 columns not every 2 columns. So try this:
=INDEX(\$U\$1:\$AX\$1,3*(ROWS(\$A\$1:A1)-1)+1)

#### NBVC

##### Well-known Member
You may need a slight modifications to NBVC's formula as my understanding is that the names start in U1 not V1 and they seem to be placed every 3 columns not every 2 columns. So try this:
=INDEX(\$U\$1:\$AX\$1,3*(ROWS(\$A\$1:A1)-1)+1)

Thanks Peter, the U sometimes looks like a V......and the OP said "every other column" so it didn't really click that they were actually 3 colums apart so when I saw that X1 was next in line, I assumed the first was V1....

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks Peter, the U sometimes looks like a V......and the OP said "every other column" so it didn't really click that they were actually 3 colums apart so when I saw that X1 was next in line, I assumed the first was V1....
No problem, I was also swayed initially by the 'every other column' but figured the specific column references were more likely to be correct.

#### DougStroud

##### Well-known Member
Thanks Guys,
I was doing some research on the topic this morning. Thanks NBVC for the original solution and Peter for the modification note. Yes NBVC I originally said every other row... my mistake. I was in the middle of doing payroll while writing the question.
I have not tried it yet-- the file is at work and I will use it a little later today. But I would like to ask a question in the interim-- You used Index, which if works, which I am confident will work; any chance that the Offset function could work. I only inquire as the use of an alternate function as to the different possibilities. I still am seeking the fundamentals in Excel and the Offset Function came to mind first. Sorry for the longwindedness...

ps. NBVC-- nice one on the 200,000th question answer

Doug

#### Peter_SSs

##### MrExcel MVP, Moderator
Doug

OFFSET is a 'volatile' function which causes Excel to recalculate more often that 'normal'. This can slow your sheet considerably and so I would recommend avoiding OFFSET as much as possible, particularly if it is going to be used in lots of formulas on the sheet.

#### DougStroud

##### Well-known Member
Thanks Peter,
Advice noted. That is cool, at least now I know Offset could work. And I do have a lot of formulas in my payroll sheet, so the solution by NBVC and you sounds like the way to go.

Cheers,

Doug

#### NBVC

##### Well-known Member
ps. NBVC-- nice one on the 200,000th question answer

Doug

Didn't even notice that!

Do I get a prize

#### DougStroud

##### Well-known Member
Of course--

3 "We are not worthy" bows ray:

Replies
6
Views
914
Replies
8
Views
634
Replies
0
Views
438
Replies
1
Views
231
Replies
0
Views
193

1,171,323
Messages
5,874,996
Members
433,086
Latest member
swdavies

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