# 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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.

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)

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

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.

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

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.

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

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

Doug

Didn't even notice that!

Do I get a prize

Of course--

3 "We are not worthy" bows ray:

Replies
1
Views
84
Replies
2
Views
103
Replies
0
Views
193
Replies
2
Views
176
Replies
1
Views
366

1,203,065
Messages
6,053,328
Members
444,653
Latest member
Curdood

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