Cell Matching Without Using Volatile Functions

[ChrisRRR]

I have a very big WS that I am trying to speed up by getting rid of volatile functions like Indirect. Occasionally the spread sheet changes which makes things even more difficult because of Indirect.

Column A numbers 1-24 and repeats like hours on a clock.
Column C represents a 24 hour period per cell. C1 represents a time from the first 24 hour block in Column A.
Column B will say "Pin" according to the time in Column A.

The first 24 hour period, 9 was chosen and matched to Column A, hence the Pin in Column B.
The second 24 hour period, 3 was chosen and so on.

My question is how do I make a formula that is not volatile (No Indirect or Offset) that I can copy down this long sheet, where I can make C1 applicable to the first 24 hours and then make C2 for the next, and so on?

I have been struggling all day. I know I can use the row number for each selected time in Col C to represent a 24 hour block, I just cannot figure it out without using something volatile.

This formula is used to make the list in Col C.
=IFERROR(INDEX($A$1:$A$24,MATCH(Sheet1!A4,Sheet2!$A$1:$A$24,0)),"")

This is what I am currently playing with in Col B
=IFERROR(IF(MATCH(C1,A1,0),"Pin"),"")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Pin[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Pin[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[ChrisRRR]

I'm confused - i don't understand what you mean by "all one week apart".
I thought the first value in column C corresponded with the first set of 24 hours (first day); the second value with the second set of 24 hours (second day); the third with the third set (day) and so on.
Now you are talking about weeks (???) - I'm afraid i don't understand what you are trying to do.
Another issue: why are there blank cells in column C?

M.

I don't mean to confuse. It is a little difficult to convey my thoughts on this project. I'm trying to figure it out myself, but it sounds like you understand what I'm trying to do from your explanation

C1 corresponds to A1:A24, which directly affects B1:B24
C2 to A25-A48 affects B25:B48
C3 to A49-A72 affects B49:B72

There may not necessarily be an entry in the Col C rows. There might be for one day, but not others. There is no pattern or way to know how Col C will be filled out. It depends on the user

I just made some entries into the sheet to test the functionality. The "week apart" entries were arbitrary. It was in an effort to see how Col B reacted when there were gaps in Col C entries

Lets say there is an entry in C1 and C3, but not C2. The function in Col B works, but I expect B25:B48 to remain empty as C2 is empty. What happens is that C3 is matched to A25:A48 and the respective row in B25:B48 will say "Pin"

 

[ChrisRRR]

Ohhh, I had it backwards... I thought that Pin was being placed in Column B and you wanted Column C to report the hour associated with it. Now I see you want it the other way around. Just to make sure I have this clear... you will enter a number in Column C where C1 represents the first 24 rows, C2 represents the second 24 rows, etc. and you want the word Pin to appear in Column B within the appropriate 24 row range next to the number in Column A that matches the value in Column C. If that is correct, then all you need is this formula in cell B1 copied down to the last filled row in Column A...

=IF(INDEX(C:C,INT(1+(ROWS(C$1:C1)-1)/24))=A1,"Pin","")

This formula assumes your numbers in Column A start in cell A1. Also, this formula does not require a helper column.

Fantastic! That worked out perfectly. I did have to make the Col C rows absolute to account for the range I'm using in the index function. It accounts for gaps in Col C entries and is spot on.

Thank you so much.