| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
| EULA | Excel Discussion (Misc queries) | |||
| Fill down formulas in cells to the LEFT of external data range? | Excel Worksheet Functions | |||
| Get External Data - not editable using Query Wizard | Excel Discussion (Misc queries) | |||
| Query of External Data | Excel Discussion (Misc queries) | |||
Linear Mode
