Using Offset

DougStroud

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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)
 
Upvote 0
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....
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top