inserting a cell reference into a sentence?

[Bran987]

Hey, I can't believe I just found this website. This is amazing.
thought I was the only one with excel questions.

I have one that I'm having a big problem with.

Sometimes I like to pretend Microsoft Excel is actually Microsoft Wor


For instance, I'm making a footnote page, and so I'll merge as man
cells as I need to put this sentence in, for instance:

1 Gross Proceeds from Unit Sales: Calculated at an average gross sal
price per S.F. of $325, or $375 net sale price per S.F. Gross squar
footage including balconies. Average unit size is 1,103 s.f.

So all of that is in one cell, but I'm wondering if WITHIN a cell wher
you've written text, there is a way to reference a cell in another par
of the spreadsheet (so the $325, $375 or 1,103 number would chang
automatically).

Again those numbers are inside the text of that sentence so if I jus
put D3 in the sentence the program doesn't know I'm trying to referenc
an actual cell... see what I'm saying?

Thanks and great to find this place

 

[Frank Kabel]

Hi
try something like the following formula
="this is my text with a cell referenced value of: " & D3 & " and some
additional text"

 

[JE McGimpsey]

One way:

="1 Gross Proceeds from Unit Sales: Calculated at an average gross sale
price per S.F. of " & TEXT(A1, "$0") & ", or " & TEXT(B1, "$0") & " net
sale price per S.F. Gross square footage including balconies. Average
unit size is " & TEXT(C1, "#,##0") & " s.f."

 

[Bran987]

OH MY GOD THANK YOU IT WORKS
YOU ARE A GENIOUS!!!!

YOU JUST SAVED ME LIKE 5 HOURS PER MONTH, THIS IS GREAT!

Do you mind if I ask some other questions sometime

 

[Frank Kabel]

OH MY GOD THANK YOU IT WORKS
YOU ARE A GENIOUS!!!!

YOU JUST SAVED ME LIKE 5 HOURS PER MONTH, THIS IS GREAT!

Do you mind if I ask some other questions sometime?

could we stop you asking
just kidding - go ahead and ask!
Frank

 

[shadestreet]

This is basically the same question that made me discover this site las
week. This is definitely one of the best sites I have ever found.

Another tidbit, you can modify the format of the value you ar
retrieving by using the following technique:

Instead of this:

="blah blah blah" &C19& "Blah Blah"

you can use:

="Blah blah blah" &TEXT(C19,"0.00")& "Blah Blah"

See, you may want that value you are retrieving to be a date, or
percent, or only have 1 decimal place. By default it will be treate
as a real number and use trailing decimals. Lets say you wanted to ad
a date in there, without writing:

&TEXT(C19, "mmddyy")

you would get 8193847 intead of 3/13/0

 

[Bran987]

One way:

="1 Gross Proceeds from Unit Sales: Calculated at an average gros
sale
price per S.F. of " & TEXT(A1, "$0") & ", or " & TEXT(B1, "$0") &
net
sale price per S.F. Gross square footage including balconies.
Average
unit size is " & TEXT(C1, "#,##0") & " s.f."

My goodness you are good too.

Can you explain to me what the "$0" means in the sale price per s.f
formulas means and the "#,##0" means in the average unit size formul
means?

For some reason the way you just told me to do it rounded the number
exactly as I needed them to. the first way Frank told me to do it
$375 per foot appeared as $374.43664364525

 

[shadestreet]

Wow looks like I read your mind!

Yes, stick with the TEXT(C19, "XXXX") method and just modify the "XXXX
to format the data the way you need it. Here are examples o
replacements to XXXX and the outcomes:

$0.00 - Two decimals with a dollar sign

mmddyy - month, day, year

0% - no decimals, percent format.

Neat huh

 

[Bran987]

*Neat huh? *

No way man, it's not neat.

It's frickin' OUTSTANDING.

I'll never use MS Word again

Thanks a mi

 

[JE McGimpsey]

They are number format strings. See "About custom number formats" in XL
Help.