Indirect Ref# error

Tbonekiller
Posts
16
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
July 20, 2022
- Jul 20, 2022 at 02:42 PM

I use a formula to set the name of my sheet (using VBA to move data around) to a number based on a cell value. The value is always a number and if the sheet doesn't exist then it is created (because I track data based on the number in the cell specifically).

Instead of writing a formula like this-

{=MAX(IF('8.10'!C6:C3006=A10,'8.10'!A6:A3006,))}

I'm trying to use the indirect approach so I can just copy the formula down quickly (using VBA). I'm using this formula but I run into issues when the sheet name ends in a 0 (zero)  (ex. 8.10 or 8.00)

{=MAX(IF(INDIRECT("'"&A113&"'!"&"C6:C3006")=A113,INDIRECT("'"&A113&"'!"&"A6:A3006"),))}

When excel reads cell A? it doesn't grab the numbers past the decimal if it's a 0 (zero) and it therefore gives me a ref# error.

Does anyone have a way or thought to get around this error? It's not that big of an issue to manually type in these few that happen, but I would like an automated approach if possible.

Thanks