Using Offset

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963
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.

Any assistance please?

thanks,

ds
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 16, 2005
Messages
2,963
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
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 16, 2005
Messages
2,963
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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