I have a master workbook, that I need to refer to a cell in a worksheet, that's in another workbook, but at this time, that sheet, and cell, do not exist.
This could be accomplished via a formula or a macro, doesn't matter, as long as it works...*sigh*...
A1 = New Workbook name
A2 = New Sheet Name
A3 = New Sheet Cell.
Formula:
Please Login or Register to view this content.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
If the filename and or page names have spaces (multiple words) this will work for names with or without spaces. It is very similar to mehmeticik's formula but has " ' " around the names.
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thank you, but its not quite working yet...the idea is that my boss has a workbook, but I need her workbook to refer to a cell in my workbook, a specific cell that will be in a worksheet that right now; does not exist...I really like that your formula puts in text if there is no reference yet though.
If, workbook/worksheet names would help, I can easily provide those...
The workbook this formula has to refer to is EVP-CL.xlsm and the sheet is 2-16-15, cell is G1
The names would help. I'll have to do a check here to see what is required to access a file that is remote over a network.
It doesn't have to connect over a network, it could work to have it on a shared drive and we all have shortcuts in place.
I can only get this to work if both files are located in a mutually accessible location. In my case OneDrive and that eliminates having to have a network address to figure into the equation. Is it possible for you to have a shared folder where these files can be located? If you can, then the formulae given you should work as the network path wouldn't be required.
I saw you post too late.
Open both workbooks from one of the locations and access the cell that you want by using in the = then navigating to the cell in the other workbook. The filename, worksheet name and cell address should be shown when you have actually found and clicked on the desired cell. Hit enter and the link should be formed resulting in having the cell contents being retrieved. Copy that path and enter the elements in A1:A3 like the examples given then convert the formulae to retrieve the required information from cells A1:A3 to form the link in formula format.
It may take several tries to get it exactly right. Remember, if there are spaces in any of the names, be sure to use the " ' " around the names as shown above.
Last edited by newdoverman; 02-11-2015 at 12:48 PM.
Yeah, her workbook and our workbooks can all be in the same place, and then we all have shortcuts to that location...Is there any chance I could bother you to put my workbook/sheet and cell names into the formula and then I'll adjust as needed from there?
I set up a file with the filename, worksheet name and cell reference and this formula worked
Formula:Please Login or Register to view this content.
I created the workbook and worksheet with some content in G1 and entered the workbook name including extension in A1, Worksheet name in A2 and cell reference in A3. These cells are referenced in the formula to form the link.
It's not working for me...I put it in exactly as you said, with the adjustments to look up the info, and it comes back with No File Yet.
Did you use the formula version that I uploaded that has the " ' " in the formula?
Do you have both files open at the same time?....should.
Yes, I used your formula, and I have both files open. I put the workbook's name in spot A1, then I put the worksheet name in spot A2, and the cell it needs to look at in spot A3...then I press enter, and the box has No File Yet. I'm specifically testing with a sheet that does currently exist so it should return the info from that cell, right?
Right.
The only thing that I can think of is that there might be a space somewhere in the names that shouldn't be there.
so, the formula should look like this: =IFERROR(INDIRECT("'["&EVP-CL.xlsm&"]"&2-16-15&"'!"&G1),"No File Yet"), right?
Just use the cell references as they are in the formula that I gave you. Using the actual names in the formula changes things a lot.
Hard coding the names in the formula will give you something like this (one of my files)
Formula:Please Login or Register to view this content.
But, if I use the cell references in that formula it will just refer to that page in that worksheet, whereas I need this cell, where the formula is going, to look in another workbook> to a specific worksheet> to a cell in that worksheet and then bring in the number from that cell to the cell where this formula is. In the grand scheme, this cell doesn't exist yet, but I have a test page to ensure the workings of this formula.
Once you get it working, all you would have to do is change the cell contents to get different results which is easier than changing hard-coded file an worksheet names.
With both of these workbooks open (on the same computer) this works. There are drop down lists from which to choose on Sheet1 of Test Worksbook.xlsx
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks