index match return value

Originally Posted by TMS

If you're already using VBA to clear cells, then just add another line of code to put the formula back.

Change "X30" to the cell where you want the formula to go (where it was originally)

TMS,
thanks so very muchly for your awesome help i used your line in the vba i have adding it to the end of the reset function and then changed the "X30" as advised and it worked perfectly!! so thanks.

oh and i learned something odd, in the sheet i have, the formula goes into a 50 by 50 cell grid but not the top 2 rows as thats where i have some text and the cascading drop downs, so i was confounded briefly on how i would change the cell reference to put the formula back into all 50 rows and columns without it going into the top 2 rows.
first i tried the reference A3:A50 and that worked fine for the first row. then i just wildly guessed that if i put a comma after the A50 i could reference the second row like this A3:A50,B3:B50 and that worked too. but then i realised if i was going to do it this way, i would have to do it for all 50 columns and that would be just ridiculous so i guessed again wildly and thought what would happen if i just referenced the lot like this A3:AX50 thinking it would show an error for trying to put the formula in rows 1 and 2 but to my surprise, it only put the formula in to rows 3 onwards. so it worked perfectly.

your help has been soo awesome i thank you so very much