Formula is correct but value is wrong! Makes no sense!

[Astello]

I'm trying to take 2 columns: 1 with a city name in it, and 1 with a
state abbreviation in it, and put them together using this worksheet
function:

=AB5&", "&AC5


This takes the two and puts a comma between them, for example:


AB AC Turns into: "Jonesville, MO" in column AD.
Jonesville MO


I've been using the method where I double click in the bottom-right
corner of the cell to paste the formula all the way down the
spreadsheet, and it has worked fine on 20 other spreadsheets. But in
this particular spreadsheet, the values from the first cell get copied
all the way down. The formulas are right, as in they point to cells
with different values, but the same city name and state abbrev. shows
up for each one. I've tried everything to correct this. Please help!


Here is a screenshot, notice the formula, and then the value - they
don't match up


http://i25.photobucket.com/albums/c91/astello929/excelmess.jpg

 

[Pete_UK]

You probably have the worksheet set to Manual calculation - press F9 to
see if the formulae recalculate.

To set the sheet back to Automatic calculation mode, go to Tools |
Options | Calculation (tab) and check Automatic.

Hope this helps.

Pete

 

[Bob Phillips]

Do you have manual calculation set (Tools>Options>Calculation)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Astello]

Brilliant! I just about had a nervous breakdown. Thanks so much!

 

[Pete_UK]

Thanks for the feedback.

If you open a worksheet which has the calculation option set to Manual,
then all other sheets opened in that session of Excel will also revert
to this Manual setting, and of course if you save any of them they will
keep the Manual setting - something to look out for in the future.

Pete

 

[Earl Kiosterud]

Astello,

If the first workbook opened is set to manual calca, then subsequent
concurrently opened workbooks will be in manual too, regardless of their
manual/auto setting. Hard to believe? Read on. If the first opened is
auto, then the subsequent workbooks will be auto -- again regardless of
their setting. If all are closed (and Excel not necessarily restarted), the
first one opened will again set the auto/manual setting for the subsequent
concurrently opened workbooks. Bottom line: The first workbook sets the
switch. If it's manually changed, it changes it for all the workbooks.
Still haven't heard enough? If any is saved, the current Excel-wide
auto-manual setting is saved with the workbook, not it's original setting.
There are other settings that behave in this Excel-wide way. It means this:
Open multiple workbooks concurrently at your own risk.

I don't know if anyone knows why this is, but it seems to be a real
potential source of trouble. Papers have been written about spreadsheet
unreliability. Most is at the hands of the designer, but this seems to be
at the hands of the application.

"I'll take 'Things that don't make sense, for $1000, Alex.'"