excel cell formatting with concatination 2

[smiley0q0]

I have 3 sheets in an excel document. the first one i input a dollar amount in cell E1. the second sheet, i put in the same amount in cell G2, but this cell is custom formated to have 16 digits. (ie. 0000000000152.36)
then in the 3rd sheet it concatinates A2 through K2

what i want to be able to do is just type in the amount one time. preferably in the first sheet, cell E1. i tried to make G2 in the second sheet just look at E1 in the first sheet.. and it looks fine, but then the concatination is off, it only pulls in the 152.36 ... i need it to pull in all 16 digits.

does this make sense?

thanks for your help in advance,

Smiley

 

[xlbo]

in G2

=rept("0",16-len(Sheet1!E1))&Sheet1!E1

where your 1st sheet is called Sheet1

Your concatenation should work fine from then on

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[smiley0q0]

WOW

thanks Geoff

very quick and efficient.

'preciate it.

Smiley

 

[smiley0q0]

k, ran into a small snag...

when i enter a number like 520.10 in E1 in sheet1, excel automatically takes off the ending 0, it "looks" fine, but sheet2 G2 looks like

00000000000520.1 when it should look like
0000000000520.10

would i have to change E1 to be "text" so that it won't take off the ending 0?

Thanks,
Smiley

 

[carrr]

Would formatting it to "Number" with 2 decimal places work, as well?

 

[smiley0q0]

no, it's set to that already... when you look at the cell E1, it "looks" just fine "152.10", but if you look in the formula bar thing, it has taken off the ending zero. The G2 formula that Geoff gave me looks at what is in the formula bar of E1... so it returns 152.1 even though the cell "looks" like 152.10

Smiley

 

[Gavona]

Try TEXT(Sheet1!E1,"0000000000000.00&quot

 

[smiley0q0]

yep, that worked gavona

thanks.


Smiley