I need the number 1,802,224 to be pulled from anothe rworksheet and be shown
as 1,802. as the result.

To just change the display:

Format/Cells/Number/Custom #,##0,

In article ,
Jamie wrote:

I need the number 1,802,224 to be pulled from anothe rworksheet and be shown
as 1,802. as the result.

I need the number 1,802,224 to be pulled from anothe rworksheet
and be shown as 1,802. as the result.

Assuming that number is in cell A1 on the other sheet, this maybe....

=INT(OtherSheetName!A1/1000)

???

Rick

"Rick Rothstein \(MVP - VB\)" wrote...
I need the number 1,802,224 to be pulled from anothe rworksheet
and be shown as 1,802. as the result.

Assuming that number is in cell A1 on the other sheet, this maybe....

=INT(OtherSheetName!A1/1000)

This doesn't round. It truncates to the nearest integer less than or
equal to the original value. If you're going to use a function, use
the correct one.

=ROUND(OtherSheetName!A1,-3)

OTOH, to actually round the value:

=ROUND(Sheet2!A1/1000,0)

In article ,
JE McGimpsey wrote:

To just change the display:

Format/Cells/Number/Custom #,##0,

In article ,
Jamie wrote:

I need the number 1,802,224 to be pulled from anothe rworksheet and be
shown
as 1,802. as the result.

But the OP wanted the result to be 1,802. , not 1,802,000
so it would have to be
=ROUND(OtherSheetName!A1,-3)/1000
or
=ROUND(OtherSheetName!A1/1000,0)
--
David Biddulph

"Harlan Grove" wrote in message
ups.com...

This doesn't round. It truncates to the nearest integer less than or
equal to the original value. If you're going to use a function, use
the correct one.

=ROUND(OtherSheetName!A1,-3)

"Rick Rothstein \(MVP - VB\)" wrote...
I need the number 1,802,224 to be pulled from anothe rworksheet
and be shown as 1,802. as the result.

Assuming that number is in cell A1 on the other sheet, this maybe....

=INT(OtherSheetName!A1/1000)

I need the number 1,802,224 to be pulled from anothe rworksheet
and be shown as 1,802. as the result.

Assuming that number is in cell A1 on the other sheet, this maybe....

=INT(OtherSheetName!A1/1000)

This doesn't round. It truncates to the nearest integer less than or
equal to the original value. If you're going to use a function, use
the correct one.

=ROUND(OtherSheetName!A1,-3)

Did you mean to write

=ROUND(OtherSheetName!A1,-3)/1000

It is unclear from the OP's original example whether he wants rounding or
truncation.

Rick

The subject line would tend to indicate "round"...<g

In article ,
"Rick Rothstein \(MVP - VB\)"
wrote:

It is unclear from the OP's original example whether he wants rounding or
truncation.

Actually, the OP was ambiguous as to whether the stored value should be
1,802 or whether it should just "be shown as 1,802".

If the latter, there's no need for functions at all:

=OtherSheetName!A1

Format as #,##0,

In article ,
"David Biddulph" <groups [at] biddulph.org.uk wrote:

But the OP wanted the result to be 1,802. , not 1,802,000
so it would have to be
=ROUND(OtherSheetName!A1,-3)/1000
or
=ROUND(OtherSheetName!A1/1000,0)
--

It is unclear from the OP's original example whether he wants rounding or
truncation.

The subject line would tend to indicate "round"...<g

Oh sure, now we have to read the Subject lines too! <g

Thanks, I missed that... I stand corrected.

Rick

Format as #,##0,

As I have posted before, my main "world" is the compiled version of VB and
in my 25+ years of programming BASIC and VB, I never saw that 'end with a
comma" trick... I just tried it in the compiled version of VB and it works
in the Format statement there (one comma truncates/rounds off the thousands,
two trailing commas truncates/rounds off the millions, and so on). Very
interesting. After all these years... still learning new things about an old
friend. Thanks for posting/emphasizing it.

By the way, that means there is another alternative for the OP's question...

=--TEXT(OtherSheetName!A1,"#,###,")

as a number (leave off the double minus signs for text).

Rick