Hi,
In sheet titled '2021 Bet' I have the following formula:
{=INDEX('2021 Draw FA'!$C$51:$J$66,MATCH('2021 Bet'!$B5,'2021 Draw FA'!$B$51:$B$66,0),'2021 Bet'!$A5,'2021 Draw FA'!$C$50:$J$50)}
This formula worked perfectly. However the ranges indexed in '2021 Draw FA' contain text, and on a weekly basis I am changing the contents of J51:J66. To do this, I deleted each cell individually (by pressing 'delete' on keyboard to remove contents), then typed in the new text. After doing this, my above formula now returns #REF!
I suppose it is telling me it can no longer reference those cells, as they have been deleted. But the deletion was only temporary, they now have text again. So given the range is valid, and there is data in every cell referenced, how can I get it stop saying #REF! and return the values that are there?
Many thanks.
In sheet titled '2021 Bet' I have the following formula:
{=INDEX('2021 Draw FA'!$C$51:$J$66,MATCH('2021 Bet'!$B5,'2021 Draw FA'!$B$51:$B$66,0),'2021 Bet'!$A5,'2021 Draw FA'!$C$50:$J$50)}
This formula worked perfectly. However the ranges indexed in '2021 Draw FA' contain text, and on a weekly basis I am changing the contents of J51:J66. To do this, I deleted each cell individually (by pressing 'delete' on keyboard to remove contents), then typed in the new text. After doing this, my above formula now returns #REF!
I suppose it is telling me it can no longer reference those cells, as they have been deleted. But the deletion was only temporary, they now have text again. So given the range is valid, and there is data in every cell referenced, how can I get it stop saying #REF! and return the values that are there?
Many thanks.