Problem in Excel

[Visuarchie]

I have a problem in Excel to which i seeking a solution.

I have imported some External data using the Query option and I am able to Refresh it as and when I want.

In another location in the file, I have given reference to a particular cell that I am importing (eg. on Cell A3 I am saying =G121 to show the contents of cell G121).

In the normal course in Excel, when a row is added or deleted the cell references get automatically updated.

However, as I am using an External Query to populate a column, as soon as I put refresh, I am assuming that Excel thinks that I have deleted and uploaded new data. So now what happens is that the old cell references are still in place, but it is now picking the wrong cell.

How can I solve it? I would like to have suggestions so that I can make the cell references get automatically updated or reference the cell based on a partial text string of what I am searching for.

Please help.

 

[Richard Schollar]

Hi

Probably best to use some form of lookup formula if you can uniquely identify the item of data you are interested in. What identifies this data item as the one you want (eg a unique reference, preferably, in a horizontally adjacent cell)?

 

[Visuarchie]

Hi

Probably best to use some form of lookup formula if you can uniquely identify the item of data you are interested in. What identifies this data item as the one you want (eg a unique reference, preferably, in a horizontally adjacent cell)?

Ok, I will tell exactly what I am doing.

From a site that provides the NAV of mutual funds, I download the entire data in text form. There are close to 4000 rows of data. The data that I am importing looks like this:

113065;Axis Triple Advantage Fund - Dividend Option;10.0352;9.9348;10.0352;15-Feb-2011

What you see above is the data from one cell.

So it is possible to identify the data I need using the ref at the beginning of the text string or the name of the fund, eg "Axis Triple Advantage Fund" which is unique.

How can I create this look up formula that you are talking about?

 

[Richard Schollar]

Assume:

the 4000 rows of data has been imported to Sheet1 column A
the lookup formula will reside in Sheet2 cell B2, Sheet2!A2 contains the ID of the fund (113065)

Then in Sheet2!B2 enter:

=VLOOKUP(Sheet2!A2 & "*",Sheet1!$A:$A,1,FALSE)

 

[Visuarchie]

Assume:

the 4000 rows of data has been imported to Sheet1 column A
the lookup formula will reside in Sheet2 cell B2, Sheet2!A2 contains the ID of the fund (113065)

Then in Sheet2!B2 enter:

=VLOOKUP(Sheet2!A2 & "*",Sheet1!$A:$A,1,FALSE)

Thanks a million. It works! I am not able to understand the logic of the syntax. Can you please care to explain?

 

[Richard Schollar]

Sure:

=VLOOKUP(Sheet2!A2 & "*",Sheet1!$A:$A,1,FALSE)

Sheet2!A2 & "*"
The lookup value is the Scheme number (found in A2) with an asterisk (*) concatenated on the end - the * is a wildcard chracter meaning "zero or more of any character" which basically will match the first value in the lookup range:

Sheet1!$A:$A
that starts with the scheme number. It returns the value in A:A that is matched because the first column is specified:

,1,

and finally the final argument:

FALSE

specifies the first match obtained should be returned.