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. |
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. |
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. |
All times are GMT +1. The time now is 08:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com