Excel Formula Issue

Hi,

What I'm trying to do is to create a pseudo-XML styled builder. You
input information and it wraps it around tags and it concatenate cells
together (maybe a few IF statements here and there to check for blank
cells).

The issue itself is that I have something like this "=IF(B5="",R5 & R10
& R13 & R16 & R19 & R22 & R25,"")" in one cell (minus the beginning and
ending quotes) and instead of showing the calculated values it shows
that formula as is. I searched around for people with this issue but
most of them reply with the Tools -> Options -> Calculations ->
Automatic (check). I have verified that, I have hit F9, I have gone to
that cell F2 and ENTER, I have set to manual and F9, I have "Detect and
Repair", and I have Save->Exit->Open. All the mentioned steps didn't
resolve the issue.

I don't believe it's a circular reference (because EXCEL didn't mention
so and I had the same issue earlier when I tired to reference a single
cell in another sheet in the same workbook - more on that below). The
funny thing is that part of the formula on the sheet works (namely the
one's I copy & pasted but if I go back and edit that formula (to
provide corrections or updates) it would break.

Since I'm not that familiar with programming in Excel or setting up
macros, I have not tried those solutions and would prefer not to use
either solution if possible seeing as how my goal doesn't require that
approach.

Is this an issue with Excel limitations? -- I know some of the strings
I'm putting together are quite long (though I estimate under 500
characters). I have something like the formula above in quite a few
places too but like I said before, the copy & paste method works and
other methods do not. The workbook is 43.5 KB.

Is this a bug? -- Perhaps I am in need of an update or patch? I have
Excel 2003 (11.8033.8036) SP2 (part of Office Pro Edition 2003) and if
I remember correctly, this was a clean install from our own CD and not
an upgrade or pre-installed.

I have not attached the spreadsheet but if that would clarify the
situation, I can and will.

I don't think system specs are related but here at work we have XP
Media Center Edition SP2 with P4 3.2 GHz and I believe a GB of RAM.

----
In regards to referencing another cell in another sheet in same
workbook, I did something like "=Sheet1!D62" (without beginning and
ending quotes) and it has the same problem as the formula - which isn't
a big surprise since they should be the same problem. But before I
thought it was because I was referencing too many data or the data
itself was too large.

Thank you very much if you read through all that and thanks if you
skimmed it :D
I have ran out of ideas so any input would be greatly appreciated.

Two things to check out:

1) Is there an apostrophe in front of the formula? If there is delete it.

2) Go to Tools, Options and see if "show formulas" is selected. If it is
deselect it.

"[email protected]" wrote:

> Hi,
>
> What I'm trying to do is to create a pseudo-XML styled builder. You
> input information and it wraps it around tags and it concatenate cells
> together (maybe a few IF statements here and there to check for blank
> cells).
>
> The issue itself is that I have something like this "=IF(B5="",R5 & R10
> & R13 & R16 & R19 & R22 & R25,"")" in one cell (minus the beginning and
> ending quotes) and instead of showing the calculated values it shows
> that formula as is. I searched around for people with this issue but
> most of them reply with the Tools -> Options -> Calculations ->
> Automatic (check). I have verified that, I have hit F9, I have gone to
> that cell F2 and ENTER, I have set to manual and F9, I have "Detect and
> Repair", and I have Save->Exit->Open. All the mentioned steps didn't
> resolve the issue.
>
> I don't believe it's a circular reference (because EXCEL didn't mention
> so and I had the same issue earlier when I tired to reference a single
> cell in another sheet in the same workbook - more on that below). The
> funny thing is that part of the formula on the sheet works (namely the
> one's I copy & pasted but if I go back and edit that formula (to
> provide corrections or updates) it would break.
>
> Since I'm not that familiar with programming in Excel or setting up
> macros, I have not tried those solutions and would prefer not to use
> either solution if possible seeing as how my goal doesn't require that
> approach.
>
> Is this an issue with Excel limitations? -- I know some of the strings
> I'm putting together are quite long (though I estimate under 500
> characters). I have something like the formula above in quite a few
> places too but like I said before, the copy & paste method works and
> other methods do not. The workbook is 43.5 KB.
>
> Is this a bug? -- Perhaps I am in need of an update or patch? I have
> Excel 2003 (11.8033.8036) SP2 (part of Office Pro Edition 2003) and if
> I remember correctly, this was a clean install from our own CD and not
> an upgrade or pre-installed.
>
> I have not attached the spreadsheet but if that would clarify the
> situation, I can and will.
>
> I don't think system specs are related but here at work we have XP
> Media Center Edition SP2 with P4 3.2 GHz and I believe a GB of RAM.
>
> ----
> In regards to referencing another cell in another sheet in same
> workbook, I did something like "=Sheet1!D62" (without beginning and
> ending quotes) and it has the same problem as the formula - which isn't
> a big surprise since they should be the same problem. But before I
> thought it was because I was referencing too many data or the data
> itself was too large.
>
> Thank you very much if you read through all that and thanks if you
> skimmed it :D
> I have ran out of ideas so any input would be greatly appreciated.
>
>

I'm a bit ashamed to admit that the error was due to and caused by my
"over-formatting". I had thought that since all fields should be text
(since some input but not all, might contain leading zero) and went
ahead and formatted each cell to be Text and didn't realize that it
would treat formulas as text rather than parse them. Hence the error
in user input.

Thanks so much for the input though but someone else at work caught my
mistake :X

Dave F wrote:
> Two things to check out:
>
> 1) Is there an apostrophe in front of the formula? If there is delete it.
>
> 2) Go to Tools, Options and see if "show formulas" is selected. If it is
> deselect it.
>
> "[email protected]" wrote:
>
> > Hi,
> >
> > What I'm trying to do is to create a pseudo-XML styled builder. You
> > input information and it wraps it around tags and it concatenate cells
> > together (maybe a few IF statements here and there to check for blank
> > cells).
> >
> > The issue itself is that I have something like this "=IF(B5="",R5 & R10
> > & R13 & R16 & R19 & R22 & R25,"")" in one cell (minus the beginning and
> > ending quotes) and instead of showing the calculated values it shows
> > that formula as is. I searched around for people with this issue but
> > most of them reply with the Tools -> Options -> Calculations ->
> > Automatic (check). I have verified that, I have hit F9, I have gone to
> > that cell F2 and ENTER, I have set to manual and F9, I have "Detect and
> > Repair", and I have Save->Exit->Open. All the mentioned steps didn't
> > resolve the issue.
> >
> > I don't believe it's a circular reference (because EXCEL didn't mention
> > so and I had the same issue earlier when I tired to reference a single
> > cell in another sheet in the same workbook - more on that below). The
> > funny thing is that part of the formula on the sheet works (namely the
> > one's I copy & pasted but if I go back and edit that formula (to
> > provide corrections or updates) it would break.
> >
> > Since I'm not that familiar with programming in Excel or setting up
> > macros, I have not tried those solutions and would prefer not to use
> > either solution if possible seeing as how my goal doesn't require that
> > approach.
> >
> > Is this an issue with Excel limitations? -- I know some of the strings
> > I'm putting together are quite long (though I estimate under 500
> > characters). I have something like the formula above in quite a few
> > places too but like I said before, the copy & paste method works and
> > other methods do not. The workbook is 43.5 KB.
> >
> > Is this a bug? -- Perhaps I am in need of an update or patch? I have
> > Excel 2003 (11.8033.8036) SP2 (part of Office Pro Edition 2003) and if
> > I remember correctly, this was a clean install from our own CD and not
> > an upgrade or pre-installed.
> >
> > I have not attached the spreadsheet but if that would clarify the
> > situation, I can and will.
> >
> > I don't think system specs are related but here at work we have XP
> > Media Center Edition SP2 with P4 3.2 GHz and I believe a GB of RAM.
> >
> > ----
> > In regards to referencing another cell in another sheet in same
> > workbook, I did something like "=Sheet1!D62" (without beginning and
> > ending quotes) and it has the same problem as the formula - which isn't
> > a big surprise since they should be the same problem. But before I
> > thought it was because I was referencing too many data or the data
> > itself was too large.
> >
> > Thank you very much if you read through all that and thanks if you
> > skimmed it :D
> > I have ran out of ideas so any input would be greatly appreciated.
> >
> >