I have a database on excel which is numbered 1-7000, I also have a separate folder which is from 1-7000 in separate folder. I want to link these and thought if I hyperlink them it would be easy. But it will take time to hyperlink over 7000 files, i was hoping if there is a easier way to do this for example autoformatting the hyperlinks?
Any help would be appreciated
Thanks
Can you give more specific examples?
An example of what you have now in your database and how you need them linked? Are they totally separate files in a single directory, etc?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
the main file has a list of customers and their addresses and each customer is numbered from 1-1000. the other folder has detailed lists of the customer, their purchases and previous order etc and i need to link these.
So how are the files named? What is the path?
from 1 - 7000, for example the customer in the database is 1 and then in a separate folder it is called 1
i have hyperlinked some of them but to do all 7000 would take a long time
Assuming your have your files in a folder called C:\Douments and Settings\MyCustomers
and the name of the workbook is the same as the ID listed in cell A1 of your current workbook, then try in B1 formula like:
=HYPERLINK("[C:\Documents and Settings\MyCustomers\"&A2&".xls]Sheet1!A1",A2)
This will give you a link to cell A1 of Sheet1 in that folder with workbook name same as what you place in A2.
You can copy this formula down... and if desired, hide column A...
THE FOLDER IS IN MY DESKTOP BUT WHERE SHOULD I INPUT THE FORMULA? shall i put it in the column with the actual 1? or somewhere else? i jus want to be able to drag down the hyperlink to make it easier
No need to capitalize your sentences... it is perceived as shouting!
The formula goes in an adjacent column to your list of customers... the links will open the respective files.
Change the A2 in the formula to the actual cell containing the customer id.
sorry about the caps, i tried dragging it down but it keep opening the first file, which one do i need to change?
If you drag down the formula, the red A2 should change to A3, A4, etc.... all automatically.
it doesnt, the first one works fine but when i drag it down or change it manually to a3 or even drag it down it opens the first file
it does change to a3 however, like the first file is 0 and the next is 1, but when i drag it down it changed the number from 1 to 0 and then opens 0
Did you change the A2's in the formula to the cell containing the first filename to open? And your path is correct?
i no it sounds dumb but what do you mean by containing the cell for the first filename, the first formula i put it in for 0 worked, but when i drag it down it keep going to 0 everytime
This part here in my formula:
"[C:\Documents and Settings\MyCustomers\"&A2&".xls
is looking for a filename in cell A2.
If you have 0 in A2, then it assumes your filename is 0.xls
if you have 1 in A3 and copy down the formula then it should be referencing 1.xls
I don't see how it "goes back to 0".
You are putting the formula in a column that is not the same column where the actual filenames are listed, right?
If you can post a sample attachment of the workbook, just so I see what it looks like, that might help.
is there anyway to drag it down
ok the formula i am using is =HYPERLINK("[C:\Customer Account No\"&F3&".xls]Sheet1!A1",A2)
f3 is the column with the 1 in there and that is the filename, i have put the formula in a row along the same row but it still says 0
Do you mean the hyperlink text says 0?
If you click the hyperlink does it go to the right place?
If you change the last A2 to F3, then the hyperlink text should match what you have in F3.
=HYPERLINK("[C:\Customer Account No\"&F3&".xls]Sheet1!A1",F3)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks