Need Named Range Formula to Auto Adjust for Every 5 Rows

[Worker8ee]

Hi everyone - I am trying to avoid creating hundreds of named range formulas. I have set up an image box to display one of three different images in A2 of Sheet1 if B2 of Sheet1 contains one of three specific values, no image shows if B2 contains the value "0". The formula I use for this named range is =INDEX(Attributes!$B$1:$B$4,MATCH(Sheet1!$B$2,Attributes!$A$1:$A$4,0)) This formula works perfectly except I need to set the same thing up to repeat every 5 rows down so for example I need the image box that's set up in A7 to appear/disappear based on the value of B7, A12 based on B12, A17 based on B17 and so on for hundreds of rows. Whenever I copy the already established image box that is linked to the named range from A2 down to A7 and every 5 rows after that, the newly created image box in A7 still looks at B2 for its direction, A12 still looks at B2 for its direction, etc. I don't want to have to create another named range for every 5 rows, does anyone know of a way around this? I am hoping I can alter the current formula for the one named range to intelligently auto adjust itself. I understand that I am using an absolute value in the MATCH(Sheet1!$B$2 section of the formula but if I don't make it absolute then it seems to become unstable and changes Sheet1!B2 to some cell that isn't even relevant.

 

[sadboy309]

Your Function:

[COLOR=#333333]=INDEX(Attributes!$B$1:$B$4,MATCH(Sheet1!$B$2,Attributes!$A$1:$A$4,0)) [/COLOR]

Change try:Where A2 based on B2; A7 based on B7. But, [A3:A6] is it will be empty.

[A2]=IF(MOD(ROW(),5)=2,INDEX([COLOR=#333333]Attributes!$[/COLOR]B$1:$B$4,MATCH([COLOR=#333333]Sheet1![/COLOR]B2,[COLOR=#333333]Attributes!$[/COLOR]A$1:$A$4,0)),"")

 

[Worker8ee]

Thanks for your suggestion sadboy, that unfortunately did not work but I do appreciate you taking the time to try to help. The named ranges seems to be a tricky section of excel.