How do I insert a calculated cell range into an excel function

[Gary Wallis]

I use the Vlookup function in Excel to extract information from different
parts of a csv file imported from another program. The problem that I have is
that I need to change the cell range in the Vlookup function each time I
import an updated file as the data will have moved depending on the size of
the file. I am able to automatically calculate what the cell references
should be, but cannot find a way to get these calculated references into the
function without keying them in directly, which is not practical.

I would be grateful for any help with this.

 

[Barb Reinhardt]

When you say "calculate" the references, I'm guessing you mean that you have
a named range for those references.

Try using something like this:

=VLOOKUP(C1,'[Yourworkbook.xls]'!YourNamedRange,2)

 

[pshepard]

If you want to avoid updating the range each time the data changes, you can
use the indirect function and using a calculated range.

An example of what this would look like:

Worksheet Name: "New Data"
Range: A1:B6

Country Units
USA 298
Germany 123
Brazil 456
Egypt 987
Japan 321

Worksheet Name: "Weekly Template"

Cell A1 Brazil
Cell A2 Egypt
Cell A3 Japan
Cell B1 =vlookup(a1,indirect(Calculated_Data_Range),2,false)
Cell B2 =vlookup(a2,indirect(Calculated_Data_Range),2,false)
Cell B3 =vlookup(a3,indirect(Calculated_Data_Range),2,false)


Worksheet Name: "Indirect References"
Cell A1 Calculated_Data_Range
Cell B1 'New Data'!$A$1:$B$6

In the Indirect References worksheet, highlight A1:B1, then from the Insert
menu, select Create, select Left Column to name B1 "Calculated_Data_Range".

In B1 of the Indirect References worksheet is where a formula to calculate
the range name would be for this example.

Let me know if this helps.

Peggy