Excel VBA (?!)-refer to a cell on Sheet to left of X, based on criteria on Sheet X
 

Hello,

I have a workbook which contains sheets for a 4-week rota period. A
one rota period ends, people either owe (or are owed) hours.

*I would like ...* to write a user defined function that will look at
name in Sheet X!Column A, find that name on the Sheet immediately t
the left of this sheet and return the content of a cell further alon
on the same row.

I have been trying to do this with the Index and Match function i.e.

=INDEX('March 22'!$E$12:$E$142,MATCH(C45,'March 22'!$C$12:$C$142,0))

but don't want users to have to retype the sheet name for each ne
4-week period.

What do you think? Clear as mud ... or can you help?

Thanks,

Temp

--
Message posted from http://www.ExcelForum.com

Excel VBA (?!)-refer to a cell on Sheet to left of X, based on criteria on Sheet X
 

Tempy,

You could try using a macro. If your ranges are always the same, you could
use something like this to reproduce your formula:

ActiveCell.FormulaR1C1 = _
"=INDEX('" & Worksheets(ActiveSheet.Index - 1).Name & _
"'!R12C5:R142C5,MATCH(R[" & ActiveCell.Row & "]C3,'" & _
Worksheets(ActiveSheet.Index - 1).Name & "'!R12C3:R142C3,0))"

However, your formula didn't reference column A of anywhere, so I'm not sure
what you meant specifically.

HTH,
Bernie
MS Excel MVP

"tempjones " wrote in message
...
Hello,

I have a workbook which contains sheets for a 4-week rota period. As
one rota period ends, people either owe (or are owed) hours.

*I would like ...* to write a user defined function that will look at a
name in Sheet X!Column A, find that name on the Sheet immediately to
the left of this sheet and return the content of a cell further along
on the same row.

I have been trying to do this with the Index and Match function i.e.

=INDEX('March 22'!$E$12:$E$142,MATCH(C45,'March 22'!$C$12:$C$142,0))

but don't want users to have to retype the sheet name for each new
4-week period.

What do you think? Clear as mud ... or can you help?

Thanks,

Tempy


---
Message posted from http://www.ExcelForum.com/

Excel VBA (?!)-refer to a cell on Sheet to left of X, based on criteria on Sheet X
 

Hi Bernie,

That does make sense - the column A reference was a red herring.

I have yet to make it work but am going to play around before I pos
again.

Thanks,

Temp

--
Message posted from http://www.ExcelForum.com