Formula to read cell references as number value

Hello,

I'm having trouble with the formula below, it only works when 'Start!D27' has a value in it rather than referring to a different cell, however, I need 'Start!D27' to refer to a different cell.
So basically we have a formula that refers to a cell which refers to a different cell in a different sheet which again refers to a different cell (in that same sheet )
Here is the work book with the formula issue. I highlighted all the parts that need attention CELL REFERENCES.xlsx
Any help on this issue would be greatly appreciated.

Please Login or Register  to view this content.

I'm having trouble understanding what you want to do here.
If there's a number in H2, then what do you want to happen?
Your formula
=IF(ISNUMBER(H2), Start!L27&(H2),Start!L27)
will concatenate the number in StartL27 with H2 giving you
460205480125 (text)
Did you want them added?
What is your expected result if there's something in H2? If there isn't something in H2?

Hi and thanks for your reply.
Working in Sheet2 the formula will be found in cell E4. Cell H12 (which is part of the formula) is based off a cell in Sheet1. Let's call that cell L11. Cell L11 is also based off a different cell in sheet1 as well, we'll call that cell A1 (I know it sounds strange this whole procedure but it's complicated to explain why i do this). Now, if Cell A1 is blank that would mean Cell L11 is blank which in return would mean cell H12 in sheet2 is blank as well, the formula then should return only 6 digits. And visa versa if cell A1 is not blank.........then the formula should return 12 digits. However the formula is not working properly.
Interesting to note that when I made cell L11 in sheet1 the main original cell with the value the formula in sheet2 worked perfect. But when I based Cell L11 off Cell A1 the formula got messed up. This really beats me. It seems to me that the formula cannot read cells that are based off a cell which is based off another cell.

the value in h2 is not a real number because the formula =LEFT(L36,6) returns a text value
=IF(ISNUMBER(H2+0),Start!L27&(H2),Start!L27) would sort it

The formula that you use to pull L36 into L11 changes the value from a number to text (LEFT, MID and RIGHT functions always result in text strings). Excel sees them as two different creatures even though they look the same. If you'd used, in L11, =LEFT(L36,6) +0, you wouldn't have this issue.
Hope that helps.

but you would loose leading 0 if you did it at that point text 0045000 would come over as 45000