| 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
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 |
|
#5
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 . |
|
#6
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 . |
|
#7
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 . . |
|
#8
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 . . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Vasant,
Her first formula actually was all in RC notation. I didn't notice it'd changed along the way, and was only getting variable FDate out of the string. As you did a minute earlier. :) Do you suppose the newsgroup servers actually conspire to hold off on posts so we'll simultaneously work on similar answers? :) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... 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 . . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Good catch, Earl ... I didn't notice the change along the way in the OP's
"original" formula either <g. Regards, Vasant. "Earl Kiosterud" wrote in message ... Vasant, Her first formula actually was all in RC notation. I didn't notice it'd changed along the way, and was only getting variable FDate out of the string. As you did a minute earlier. :) Do you suppose the newsgroup servers actually conspire to hold off on posts so we'll simultaneously work on similar answers? :) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... 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 . . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Hi Vasant,
maybe she does want A:D. note that in your post you do "=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!R1C3:R4C3,4,FALSE)" rows 1 to 4 of column 3, a single column, then ask for the results from column 4 of the range - a definite error situation <g -- Regards, Tom Ogilvy "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... 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 . . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
Thanks, Tom ... as usual, you are correct. I'll quit while I'm ahead in this
thread. I really need to get more sleep ... :) Regards, Vasant "Tom Ogilvy" wrote in message ... Hi Vasant, maybe she does want A:D. note that in your post you do "=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!R1C3:R4C3,4,FALSE)" rows 1 to 4 of column 3, a single column, then ask for the results from column 4 of the range - a definite error situation <g -- Regards, Tom Ogilvy "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... 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 . . |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vLookup to another open spreadsheet
I have found that poking functions into cells as suggested causes a lo
of problems. If nothing else, you end up with a workbook full o functions, so that everytime you make a change it can take severa minutes to update (unless you turn off automatic recalculation). It is easier to program, and easier to understand later, if you use th Application.WorkbookFunction command. In this case you would use: Set LookupTable = Workbooks(...).Range(...) ResultColumn = j LookupValue = ..... FoundValue = Application.WorkbookFunction. _ vLookUp(SearchRange, LookUpValue, j, False -- Message posted from http://www.ExcelForum.com |
| 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)
Linear Mode
