asked on

data type in excel file needs to be Number for Pivot table

Hi,
I have an excel file that has pivot tables in it that depend on a columns data type to be numbers in order to update automatically.

The data is dumped into the file automatically from sql server 2005 via a linked server to oracle...(the source data is on an oracle database) but it is interpreted as text (see picture)...I have formatted the cells as number but I suppose the data type that is being dumped to the file is text....anyway how can I ensure the data is of Number type so that the table can update?

thanks

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

several ways:

- copy an empty cell, click the colulmn and do a Paste Special - Add

- Select the column and do a Data > Text to Columns > Finish

Either could be automated with a a macro. If the data is always in the same column, just fire up the macro recorder while you perform one of the above and then run the macro on new data.

cheers, teylyn

blossompark

ASKER

Hi teylyn,
thanks for your comments.
tried
copy an empty cell, click the colulmn and do a Paste Special - Add
which ended with the same reults

Select the column and do a Data > Text to Columns > Finish
this wouldn't let me proceed because the fields are initially empty,

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kakhaber Siradze

select recods. click on warning "!" and convert to number
excel.png

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

@Angelgeo, this also requires the values to be actually present in the cells. As with the methods in the first post, this needs to be repeated manually and does not apply dynamically to linked content.

blossompark

ASKER

Hi teylyn,
going to try your suggestion, thanks and will update you...
thanks angelgeo...tried that!!

blossompark

ASKER

Hi teylyn, worked perfect!! thank you :-)