| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookup to another open spreadsheet
Hello,
I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
I must be missing something. If the worksheet is called 0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls? -- Vasant "Jill" wrote in message ... Hello, I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Oops...Sorry,
The formula is ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'! C1:C4,4,FALSE)" -----Original Message----- I must be missing something. If the worksheet is called 0618mrp.xls, why is the formulqa referring to IDA001-Intransit.xls? -- Vasant "Jill" wrote in message ... Hello, I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Aha. Then you need to break up the string as well as write the lookup table
reference in R1C1 format: "=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!R1C3:R4C3,4,FALSE)" -- Vasant "Jill" wrote in message ... Oops...Sorry, The formula is ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'! C1:C4,4,FALSE)" -----Original Message----- I must be missing something. If the worksheet is called 0618mrp.xls, why is the formulqa referring to IDA001-Intransit.xls? -- Vasant "Jill" wrote in message ... Hello, I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Jill,
It's looking for a file named "fDate & mrp.xls". Try this (untested): ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'! C1:C4,4,FALSE)" The FDate needs to be outside the strings or it's literally "fDate". -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jill" wrote in message ... Oops...Sorry, The formula is ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'! C1:C4,4,FALSE)" -----Original Message----- I must be missing something. If the worksheet is called 0618mrp.xls, why is the formulqa referring to IDA001-Intransit.xls? -- Vasant "Jill" wrote in message ... Hello, I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Earl,
That works great. The extra " and & and " is what the formula needed. Thank you sooooo much for your help. I truely spent hours trying to figure this out. Thank you again! Jill -----Original Message----- Jill, It's looking for a file named "fDate & mrp.xls". Try this (untested): ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'! C1:C4,4,FALSE)" The FDate needs to be outside the strings or it's literally "fDate". -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jill" wrote in message ... Oops...Sorry, The formula is ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'! C1:C4,4,FALSE)" -----Original Message----- I must be missing something. If the worksheet is called 0618mrp.xls, why is the formulqa referring to IDA001-Intransit.xls? -- Vasant "Jill" wrote in message ... Hello, I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Hey! What about me? I posted a minute before Earl!
Seriously, though, I think Earl's solution (based on your original attempt) may give you the wrong references. C1:C4 will be interpreted as A:D since you are using R1C1 notation. -- Vasant "Jill" wrote in message ... Earl, That works great. The extra " and & and " is what the formula needed. Thank you sooooo much for your help. I truely spent hours trying to figure this out. Thank you again! Jill -----Original Message----- Jill, It's looking for a file named "fDate & mrp.xls". Try this (untested): ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'! C1:C4,4,FALSE)" The FDate needs to be outside the strings or it's literally "fDate". -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jill" wrote in message ... Oops...Sorry, The formula is ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'! C1:C4,4,FALSE)" -----Original Message----- I must be missing something. If the worksheet is called 0618mrp.xls, why is the formulqa referring to IDA001-Intransit.xls? -- Vasant "Jill" wrote in message ... Hello, I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Jill,
Why is the file name IDA001-Intransit.xls in the VLOOKUP you're building when you say you're opening 0618mrp.xls. Have I missed something? The file doesn't have to be open anyway for the VLOOKUP to get hold of it. But if it doesn't exist, you'll get a dialog titled "Update values" which lists files. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jill" wrote in message ... Hello, I need some help with this formula. I'm trying to do a vLookup from my active sheet to another open spreadsheet. The other spreadsheet has been named 0618mrp.xls. The mmdd (06/18) has been defined earlier in the macro as fDate. The macro will effectively open the file using fDate & mrp.xls. The problem is when I use fDate & mrp.xls in a vLookup Excel opens a window and wants me to select a file to refer to. The file I want to refer to is already open but Excel won't recognize it in the vLookup. This is the formula: ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'! R1C1:R19C2,2,FALSE)" I would really appreciate your help, Jill |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| spreadsheet does not open | Excel Discussion (Misc queries) | |||
| Can't see the open spreadsheet | Excel Discussion (Misc queries) | |||
| Spreadsheet will not open without Excel Application being open fir | Excel Discussion (Misc queries) | |||
| Help! I can't get a spreadsheet to open | New Users to Excel | |||
| How to open another Excel spreadsheet to copy data into current spreadsheet ? | Excel Programming | |||
![Jill[_7_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
