format of sheetname in cell is stopping my formula.

[GingerStepChild]

Hi,

In a cell G19 I have the following to get the sheetname:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)

The format type is General but all the sheetnames will be numbers.

This sheetname value is manually entered into column A of a table on another sheet called 'Summary'. Type is also "General'

There are 4 columns in the table and I require data from 2 other columns which I get from using an Index/Match

=INDEX(Summary!$B$6:$B$24,MATCH($G$19,Summary!A6:A24,0))
=INDEX(Summary!$C$6:$C$24,MATCH($G$19,Summary!A6:A24,0))

So far so good.

My problem is when I reference the cells that has the sheetname formula, G19 it returns #N/A, which makes me sad.

When I change my reference cell to G18 it returns the values I need.

I do note that when I am checking the formula the G19 value is "2017010" (with quotes) while G18 is 2017010 without quotes.

So, what do I need to do to the sheetname formula cell G19 so my index match works correctly. I have been sniffing about INDIRECT but am not sure I understand it correctly.

Thanks in advance for your help.

 

[FDibbins]

1. Any time you use LEFT/MID/RIGHT, the answer will always be text, even if it looks like a number
2. Formatting a text cell to any numeric format (general, fixed etc) wll not change the actual contents of that cell

Try this instead...
=INDEX(Summary!$B$6:$B$24,MATCH(--$G$19,Summary!A6:A24,0))

 

[GingerStepChild]

Thanks Ford. That is so handy to now know.

Can you explain what the -- does to the reference cell $G$19?

 

[FDibbins]

Your MID() is returning a text number. -- forces a text number to be a real number

 

[GingerStepChild]

Thanks.

 

[FDibbins]

Happy to help

 

[Rick Rothstein]

Your MID() is returning a text number. -- forces a text number to be a real number

Just to add to Ford's explanation...

When you involve a "text number" in a mathematical operation, Excel will convert that "text number" to a "real number" in order to be able to complete the mathematical operation. When you write, as an example, -5 what you really have done is multiply 5 times -1. When you write --5 what you have is -1 times -1 times 5... -1 times -1 mathematically calculates to +1 and +1 times any value (5 in my example's case) is just that value. So, the double minus in front of a "text number" simply multiply it by +1 which simply returns the number as a real number. The same effect could be achieved by using 1* (multiply by 1) or 0+ (add zero) as they do the same thing as -- does... change the number from a "text number" to a "real number" without changing the value itself in any way. Once this conversion is done, the MATCH function is able to search for a real number in a range of real numbers.

 

[GingerStepChild]

Thanks Rick. Forcing text to become a number is really handy for my work.