Array formulas won't recognize external data from Microsoft Query
 

I set up an external query to pull data from an Oracle database into a sheet
in my excel file. I have array formulas on another sheet that use the
external data as their data source for calculations. The problem is that all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the data is
recognized and my array formula sums increase accordingly. I think there is
some kind of hidden formatting on the external data, but can't find it.
Please help.

Thanks.

Array formulas won't recognize external data from Microsoft Query
 

External data, depending on it's source, quite often comes in as data, try
either setting the data in MSQuery if you database accepts something like
the Val or Value functions, or copy a 1 and select your imported data and
EditPaste special...Values and multiply should 'force then to be seen as
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I set up an external query to pull data from an Oracle database into a
sheet
in my excel file. I have array formulas on another sheet that use the
external data as their data source for calculations. The problem is that
all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the data is
recognized and my array formula sums increase accordingly. I think there
is
some kind of hidden formatting on the external data, but can't find it.
Please help.

Thanks.

Array formulas won't recognize external data from Microsoft Qu
 

Thanks for the reply, Nick. I looked up the value functions in my Oracle SQL
guide, but couldn't figure out how to get them to work here. I next tried the
copy/paste with multiplication, but the resulting value was 1. It's like
Excel is not even treating the data as numeric.

Any other suggestions?



"Nick Hodge" wrote:

External data, depending on it's source, quite often comes in as data, try
either setting the data in MSQuery if you database accepts something like
the Val or Value functions, or copy a 1 and select your imported data and
EditPaste special...Values and multiply should 'force then to be seen as
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I set up an external query to pull data from an Oracle database into a
sheet
in my excel file. I have array formulas on another sheet that use the
external data as their data source for calculations. The problem is that
all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the data is
recognized and my array formula sums increase accordingly. I think there
is
some kind of hidden formatting on the external data, but can't find it.
Please help.

Thanks.

Array formulas won't recognize external data from Microsoft Qu
 

Try putting the 1 on a ne sheet or workbook before copying, so you know what
you copy is a number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
Thanks for the reply, Nick. I looked up the value functions in my Oracle
SQL
guide, but couldn't figure out how to get them to work here. I next tried
the
copy/paste with multiplication, but the resulting value was 1. It's like
Excel is not even treating the data as numeric.

Any other suggestions?



"Nick Hodge" wrote:

External data, depending on it's source, quite often comes in as data,
try
either setting the data in MSQuery if you database accepts something like
the Val or Value functions, or copy a 1 and select your imported data and
EditPaste special...Values and multiply should 'force then to be seen as
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I set up an external query to pull data from an Oracle database into a
sheet
in my excel file. I have array formulas on another sheet that use the
external data as their data source for calculations. The problem is
that
all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the data is
recognized and my array formula sums increase accordingly. I think
there
is
some kind of hidden formatting on the external data, but can't find it.
Please help.

Thanks.

Array formulas won't recognize external data from Microsoft Qu
 

I just tried that and I'm still seeing "1" as the result of the multiply paste.

"Nick Hodge" wrote:

Try putting the 1 on a ne sheet or workbook before copying, so you know what
you copy is a number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
Thanks for the reply, Nick. I looked up the value functions in my Oracle
SQL
guide, but couldn't figure out how to get them to work here. I next tried
the
copy/paste with multiplication, but the resulting value was 1. It's like
Excel is not even treating the data as numeric.

Any other suggestions?



"Nick Hodge" wrote:

External data, depending on it's source, quite often comes in as data,
try
either setting the data in MSQuery if you database accepts something like
the Val or Value functions, or copy a 1 and select your imported data and
EditPaste special...Values and multiply should 'force then to be seen as
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I set up an external query to pull data from an Oracle database into a
sheet
in my excel file. I have array formulas on another sheet that use the
external data as their data source for calculations. The problem is
that
all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the data is
recognized and my array formula sums increase accordingly. I think
there
is
some kind of hidden formatting on the external data, but can't find it.
Please help.

Thanks.

Array formulas won't recognize external data from Microsoft Qu
 

And you're selecting Paste specialValues+Multiply??? (Never known that
fail)

Try a blank cell and Paste specialValue+Add

If this fails, try formatting as general first and repeat either route
above. Also is this fails, sue helper columns, eg

=VALUE(A1)

and copy down and across and then copypaste special... values and delete
the old columns

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I just tried that and I'm still seeing "1" as the result of the multiply
paste.

"Nick Hodge" wrote:

Try putting the 1 on a ne sheet or workbook before copying, so you know
what
you copy is a number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
Thanks for the reply, Nick. I looked up the value functions in my
Oracle
SQL
guide, but couldn't figure out how to get them to work here. I next
tried
the
copy/paste with multiplication, but the resulting value was 1. It's
like
Excel is not even treating the data as numeric.

Any other suggestions?



"Nick Hodge" wrote:

External data, depending on it's source, quite often comes in as data,
try
either setting the data in MSQuery if you database accepts something
like
the Val or Value functions, or copy a 1 and select your imported data
and
EditPaste special...Values and multiply should 'force then to be seen
as
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I set up an external query to pull data from an Oracle database into
a
sheet
in my excel file. I have array formulas on another sheet that use
the
external data as their data source for calculations. The problem is
that
all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the data
is
recognized and my array formula sums increase accordingly. I think
there
is
some kind of hidden formatting on the external data, but can't find
it.
Please help.

Thanks.

Array formulas won't recognize external data from Microsoft Qu
 

I agree, weird. I updated my array formulas to include a VALUE() wrapper
around the fields that are in the source data and everything is working now.
Thanks, Nick!


"Nick Hodge" wrote:

And you're selecting Paste specialValues+Multiply??? (Never known that
fail)

Try a blank cell and Paste specialValue+Add

If this fails, try formatting as general first and repeat either route
above. Also is this fails, sue helper columns, eg

=VALUE(A1)

and copy down and across and then copypaste special... values and delete
the old columns

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I just tried that and I'm still seeing "1" as the result of the multiply
paste.

"Nick Hodge" wrote:

Try putting the 1 on a ne sheet or workbook before copying, so you know
what
you copy is a number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
Thanks for the reply, Nick. I looked up the value functions in my
Oracle
SQL
guide, but couldn't figure out how to get them to work here. I next
tried
the
copy/paste with multiplication, but the resulting value was 1. It's
like
Excel is not even treating the data as numeric.

Any other suggestions?



"Nick Hodge" wrote:

External data, depending on it's source, quite often comes in as data,
try
either setting the data in MSQuery if you database accepts something
like
the Val or Value functions, or copy a 1 and select your imported data
and
EditPaste special...Values and multiply should 'force then to be seen
as
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I set up an external query to pull data from an Oracle database into
a
sheet
in my excel file. I have array formulas on another sheet that use
the
external data as their data source for calculations. The problem is
that
all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the data
is
recognized and my array formula sums increase accordingly. I think
there
is
some kind of hidden formatting on the external data, but can't find
it.
Please help.

Thanks.

Array formulas won't recognize external data from Microsoft Qu
 

Great

Well done!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I agree, weird. I updated my array formulas to include a VALUE() wrapper
around the fields that are in the source data and everything is working
now.
Thanks, Nick!


"Nick Hodge" wrote:

And you're selecting Paste specialValues+Multiply??? (Never known that
fail)

Try a blank cell and Paste specialValue+Add

If this fails, try formatting as general first and repeat either route
above. Also is this fails, sue helper columns, eg

=VALUE(A1)

and copy down and across and then copypaste special... values and delete
the old columns

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I just tried that and I'm still seeing "1" as the result of the multiply
paste.

"Nick Hodge" wrote:

Try putting the 1 on a ne sheet or workbook before copying, so you
know
what
you copy is a number

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
Thanks for the reply, Nick. I looked up the value functions in my
Oracle
SQL
guide, but couldn't figure out how to get them to work here. I next
tried
the
copy/paste with multiplication, but the resulting value was 1. It's
like
Excel is not even treating the data as numeric.

Any other suggestions?



"Nick Hodge" wrote:

External data, depending on it's source, quite often comes in as
data,
try
either setting the data in MSQuery if you database accepts
something
like
the Val or Value functions, or copy a 1 and select your imported
data
and
EditPaste special...Values and multiply should 'force then to be
seen
as
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"mmrtech" wrote in message
...
I set up an external query to pull data from an Oracle database
into
a
sheet
in my excel file. I have array formulas on another sheet that use
the
external data as their data source for calculations. The problem
is
that
all
of the array formula results are zero.

I found that if I re-type the data in external data cells, the
data
is
recognized and my array formula sums increase accordingly. I
think
there
is
some kind of hidden formatting on the external data, but can't
find
it.
Please help.

Thanks.