Lookup problem
Posted by Carlo on January 30, 2001 5:38 AM
I have a workbook with three sheets. In Sheet 3, there is a table with Exchange rates that is updated through a web query. On sheet 1, I would need 4 of those exchange rates displayed. The problem is that the format of the table with the exchange rates is not a typical table. It does not start in row 1, there is some text above the table. Additionally the exchange rate values are not displayed like 18,698 but as 18.698 (with a fullstop inbetween instead of a comma, my excel does not like this...). Is there any way to get this data to sheet 1?
Posted by Mark W. on January 30, 2001 6:41 AM
1. "...the format of the table with the exchange
rates is not a typical table... there is some
text above the table."
Carlo, check to see if the "Import HTML table(s)
only" check box is checked. This is one of the
External Data Range Properties that can be viewed
and modified by right clicking on the data returned
by your web query and choosing "Data Range Properties".
2. "...exchange rate values are not displayed
like 18,698...my excel does not like this..."
I presume you mean that Excel is treating this
value as a text value. If so, consider passing
the results of your lookup to the SUBSTITUTE()
function then add zero to its results to coerce
the resulting text value into a numeric one.
Try this:
=SUBSTITUTE([lookup results],".",",")+0
