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"),"")
<tbody>
</tbody>
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"),"")
1 | 9 | |
2 | 3 | |
3 | ||
4 | ||
5 | 14 | |
6 | ||
7 | ||
8 | ||
9 | Pin | |
10 | ||
11 | ||
12 | ||
13 | ||
14 | ||
15 | ||
16 | ||
17 | ||
18 | ||
19 | ||
20 | ||
21 | ||
22 | ||
23 | ||
24 | ||
1 | ||
2 | ||
3 | Pin | |
4 | ||
5 | ||
6 |
<tbody>
</tbody>