Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I insert a calculated cell range into an excel function
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I insert a calculated cell range into an excel function
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) "Gary Wallis" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I insert a calculated cell range into an excel function
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,fals e) Cell B2 =vlookup(a2,indirect(Calculated_Data_Range),2,fals e) Cell B3 =vlookup(a3,indirect(Calculated_Data_Range),2,fals e) 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 "Gary Wallis" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO INSERT A CALCULATED FIELD TO MY PIVOT TABLE EXCEL 2003 | Excel Discussion (Misc queries) | |||
pivot tables - insert calculated field | Charts and Charting in Excel | |||
Pivot tables - Insert Calculated field | Excel Discussion (Misc queries) | |||
how to change a calculated cell to = the calculated value | Excel Discussion (Misc queries) | |||
pethow do i insert a Yes/No function into a cell in Excel? | Excel Discussion (Misc queries) |