Identify a worksheet from a provided range

**Optional_karl** · 02-03-2015, 11:07 AM

Hello,

I'm going to try and explain myself here...

I have the below function:

Please Login or Register  to view this content.

What this is doing is finding the latest entered value in a horizontal list, and returning the month (header row) this data was entered for.

There's two issues with my code:

1. to get the column from the Cell that has been updated, i have had to use the MID function. i tried using C.Column, but i didn't know how then to put that with the 'HeaderRow' variable that's passed in to identify the month. is there a tidier way of doing this?

2. The function is currently used on 1 sheet within a workbook, but the function automatically updates whenever something changes in the workbook. As the Criteria is passed in, it 'knows' that the range is on the 'Progress_Tracker' sheet. however i have had to explicitly state that sheet when returning my value, otherwise it uses the active sheet at the time of update. is there a way to identify the 'sheet' from the 'Criteria' range?

The formula appears in the worksheet as =HPROGDATE($D$4:$M$4,3)

edit - changed to solved

**stnkynts** · 02-03-2015, 11:46 AM

1. to get the column from the Cell that has been updated, i have had to use the MID function. i tried using C.Column, but i didn't know how then to put that with the 'HeaderRow' variable that's passed in to identify the month. is there a tidier way of doing this?

Please Login or Register  to view this content.

2. The function is currently used on 1 sheet within a workbook, but the function automatically updates whenever something changes in the workbook. As the Criteria is passed in, it 'knows' that the range is on the 'Progress_Tracker' sheet. however i have had to explicitly state that sheet when returning my value, otherwise it uses the active sheet at the time of update. is there a way to identify the 'sheet' from the 'Criteria' range?

I am not really sure what you are asking. Maybe look at using the Parent property. A google search will give you information.

**Optional_karl** · 02-09-2015, 08:30 AM

Thanks very much,

even without explaining myself particuarly well, the 'parent' property is exactly what i was after.

Both issues solved, thanks again!