i have an excel file that uses vlookup to access (hidden) sheet in workbook.
can i use this function in the same way but access a file located on server in lieu of worksheet in the same file?
current function:
=IF(B24="","",VLOOKUP(B24,Worksheet_Data_DONOTERASE!$M$4:$O$9187,2,FALSE))
Yes. Best way to set it up is probably with bOTH files open and tabbing between the two files to select the correct cell ranges needed for the formula. Once the source file is closed, the formula should update to incude the complete path from your source file.... providing there are no issues with permissions to reach ther server file, of course.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Can i swap out the worksheet name in existing formula with file name of external file?
Also, how could i get cost point for points to have someoen resolve this issue for me?
I set up a simple VLOOKUP in Book 2, relating to Book 1.
With Book 1 OPEN, I set this up:
=VLOOKUP(A2,[Book1.xlsx]Sheet1!$A:$B,2,FALSE)
in Book 2.
Once book 1 is closed, it changed automatically to:
=VLOOKUP(A2,'https://d.docs.live.net/8e867843e6449ab1c/Documents/[Book1.xlsx]Sheet1'!$A:$B,2,FALSE)
as my file was on my OneDrive folder. If I change the lookup value in A2, it updates perfectly, without opening Book 1.
There is a commercial services section, if you want to use it... but this should be fixable here in the free section, without recourse to spending any $$$
I got it to work, trying to add like formula to other cells, but ...
I have a cell with a drop down list, wont accept formula because "this valuedoesnt match the data valiation restrictions defined for this cell" ...
How do i remove list or dat restrictions?
Please forgive the elementary questions, 30+ cad designer/drafter wearing extra hats
With the cell selected, Data ribbon | Data Validation | change to None.
Administrative Note:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I think a sample file would help, 'cos I'm not sure I follow you.
The formula in columns d-f pull the description based on part number,
im trying to add the same formula to columns h,i,l & m to pull their respective data,
worksheet:
Attachment 763862
data sheet:
Attachment 763863
There are instructions at the top of the page explaining how to attach your sample workbook (not images of it).
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Can you guide me to "manageattachments"?
Unfortunately the attachment icon doesn't work at the moment. So... for a new thread (1st post), scroll to Manage Attachments and for a new post in your existing thread click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments. In both cases, the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
Like this?
Trying to pull other info
Change D24 to:
=IF($B24="","",VLOOKUP($B24,'[QUOTE-SAGE-MASTER-EXTRACT.xlsx]Sheet1'!$M:$R,2,FALSE))
(tidier). For UoM, remove the data validation in the column and use:
=IF($B24="","",VLOOKUP($B24,'[QUOTE-SAGE-MASTER-EXTRACT.xlsx]Sheet1'!$M:$R,3,FALSE))
for the others,
=IF($B24="","",VLOOKUP($B24,'[QUOTE-SAGE-MASTER-EXTRACT.xlsx]Sheet1'!$M:$R,4,FALSE))
=IF($B24="","",VLOOKUP($B24,'[QUOTE-SAGE-MASTER-EXTRACT.xlsx]Sheet1'!$M:$R,5,FALSE))
but guessing a bit, as the column headers differ.
Thank you so much, it works but ...
The validation in cell b(c)24 doesnt work
formula shows as:
=#ref!$m$4:$m$9187
i dont know what the #ref! Refers too, seemed to work ok until i deleted original ref sheet in the document
That appears BECAUSE you deleted the original sheet in the document. You can't do that without causing the formulae to fall over.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Is there a way to point it to the new, external document?
Open the source and set the formula up again. Why are you deleting the sheet in the document?
Because i want the info pulled from external file that can be updated regularly
And thank you so much for the help, please send me info and ill buy you a dinner
Last edited by jackshield; 01-17-2022 at 03:43 PM.
Gawd. Edit your post and temove your email... unless you love spam.
No need. I do this for fun on rainy days when there's not much else to do.
The box (boxes) in question have data validation (pull down list)
under data validation:
Source is listed as: =#ref!
I dont see an actual formula or specifics to link
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks