Data extraction from hyperlinked workbooks
up vote
0
down vote
favorite
I have hundreds of different workbooks to work with and extract certain data from them and summarize these data in a separate workbook. This overview workbook contains hyperlinks to the single workbooks.
I need help to extract data from these single workbooks to the overview using the hyperlinks automatically. Is this possible?
Please see photo below on what it looks like.
microsoft-excel vba microsoft-excel-2016
edited Nov 29 at 17:10
Worthwelle
2,2993924
asked Nov 29 at 11:58
fresh_french
1
add a comment |
up vote
0
down vote
favorite
I have hundreds of different workbooks to work with and extract certain data from them and summarize these data in a separate workbook. This overview workbook contains hyperlinks to the single workbooks.
I need help to extract data from these single workbooks to the overview using the hyperlinks automatically. Is this possible?
Please see photo below on what it looks like.
microsoft-excel vba microsoft-excel-2016
edited Nov 29 at 17:10
Worthwelle
2,2993924
asked Nov 29 at 11:58
fresh_french
1
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have hundreds of different workbooks to work with and extract certain data from them and summarize these data in a separate workbook. This overview workbook contains hyperlinks to the single workbooks.
I need help to extract data from these single workbooks to the overview using the hyperlinks automatically. Is this possible?
Please see photo below on what it looks like.
microsoft-excel vba microsoft-excel-2016
edited Nov 29 at 17:10
Worthwelle
2,2993924
asked Nov 29 at 11:58
fresh_french
1
I have hundreds of different workbooks to work with and extract certain data from them and summarize these data in a separate workbook. This overview workbook contains hyperlinks to the single workbooks.
I need help to extract data from these single workbooks to the overview using the hyperlinks automatically. Is this possible?
Please see photo below on what it looks like.
microsoft-excel vba microsoft-excel-2016
microsoft-excel vba microsoft-excel-2016
edited Nov 29 at 17:10
Worthwelle
2,2993924
asked Nov 29 at 11:58
fresh_french
1
edited Nov 29 at 17:10
Worthwelle
2,2993924
asked Nov 29 at 11:58
fresh_french
1
edited Nov 29 at 17:10
Worthwelle
2,2993924
edited Nov 29 at 17:10
Worthwelle
2,2993924
edited Nov 29 at 17:10
Worthwelle
2,2993924
2,2993924
asked Nov 29 at 11:58
fresh_french
1
asked Nov 29 at 11:58
fresh_french
1
asked Nov 29 at 11:58
fresh_french
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
Extracting data from other worksheets is possible by using special formulas such as the one below:
='\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'!$K$14
This formula has several parts:
'\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'is the reference to the sheet itself. This can be broken down into:
\srv-file1.redactedexample shareis the folder location. This can be on the local drive or on a network share. If the workbook being referenced is in the same folder as the overview workbook, then this can be omitted entirely.
[Time Sheet.xls]is the file name of the workbook, wrapped in.
Sheet1is the sheet within Time Sheet.xls that is being referenced.
!separates the filename/sheet from the specific cell being referenced.
$K$14is the reference to the specific cell in the workbook. You can, of course, also use relative addressing such asK14.
Unfortunately, from what I can tell, you can't store the reference to the workbook's location in a cell and reference it in other cells. It needs to be entered into each cell. The nice thing is that you can simply double click on the cell in the overview workbook, enter = and then click the cell in the referenced workbook and Excel will create the formula for you. (The full file path won't show up until you close the referenced workbook.)
answered Nov 29 at 17:23
Worthwelle
2,2993924
this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help!
– fresh_french
Dec 1 at 13:00
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1379392%2fdata-extraction-from-hyperlinked-workbooks%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Extracting data from other worksheets is possible by using special formulas such as the one below:
='\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'!$K$14
This formula has several parts:
'\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'is the reference to the sheet itself. This can be broken down into:
\srv-file1.redactedexample shareis the folder location. This can be on the local drive or on a network share. If the workbook being referenced is in the same folder as the overview workbook, then this can be omitted entirely.
[Time Sheet.xls]is the file name of the workbook, wrapped in.
Sheet1is the sheet within Time Sheet.xls that is being referenced.
!separates the filename/sheet from the specific cell being referenced.
$K$14is the reference to the specific cell in the workbook. You can, of course, also use relative addressing such asK14.
Unfortunately, from what I can tell, you can't store the reference to the workbook's location in a cell and reference it in other cells. It needs to be entered into each cell. The nice thing is that you can simply double click on the cell in the overview workbook, enter = and then click the cell in the referenced workbook and Excel will create the formula for you. (The full file path won't show up until you close the referenced workbook.)
answered Nov 29 at 17:23
Worthwelle
2,2993924
this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help!
– fresh_french
Dec 1 at 13:00
add a comment |
up vote
0
down vote
Extracting data from other worksheets is possible by using special formulas such as the one below:
='\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'!$K$14
This formula has several parts:
'\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'is the reference to the sheet itself. This can be broken down into:
\srv-file1.redactedexample shareis the folder location. This can be on the local drive or on a network share. If the workbook being referenced is in the same folder as the overview workbook, then this can be omitted entirely.
[Time Sheet.xls]is the file name of the workbook, wrapped in.
Sheet1is the sheet within Time Sheet.xls that is being referenced.
!separates the filename/sheet from the specific cell being referenced.
$K$14is the reference to the specific cell in the workbook. You can, of course, also use relative addressing such asK14.
Unfortunately, from what I can tell, you can't store the reference to the workbook's location in a cell and reference it in other cells. It needs to be entered into each cell. The nice thing is that you can simply double click on the cell in the overview workbook, enter = and then click the cell in the referenced workbook and Excel will create the formula for you. (The full file path won't show up until you close the referenced workbook.)
answered Nov 29 at 17:23
Worthwelle
2,2993924
this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help!
– fresh_french
Dec 1 at 13:00
add a comment |
up vote
0
down vote
up vote
0
down vote
Extracting data from other worksheets is possible by using special formulas such as the one below:
='\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'!$K$14
This formula has several parts:
'\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'is the reference to the sheet itself. This can be broken down into:
\srv-file1.redactedexample shareis the folder location. This can be on the local drive or on a network share. If the workbook being referenced is in the same folder as the overview workbook, then this can be omitted entirely.
[Time Sheet.xls]is the file name of the workbook, wrapped in.
Sheet1is the sheet within Time Sheet.xls that is being referenced.
!separates the filename/sheet from the specific cell being referenced.
$K$14is the reference to the specific cell in the workbook. You can, of course, also use relative addressing such asK14.
Unfortunately, from what I can tell, you can't store the reference to the workbook's location in a cell and reference it in other cells. It needs to be entered into each cell. The nice thing is that you can simply double click on the cell in the overview workbook, enter = and then click the cell in the referenced workbook and Excel will create the formula for you. (The full file path won't show up until you close the referenced workbook.)
answered Nov 29 at 17:23
Worthwelle
2,2993924
Extracting data from other worksheets is possible by using special formulas such as the one below:
='\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'!$K$14
This formula has several parts:
'\srv-file1.redactedexample share[Time Sheet.xls]Sheet1'is the reference to the sheet itself. This can be broken down into:
\srv-file1.redactedexample shareis the folder location. This can be on the local drive or on a network share. If the workbook being referenced is in the same folder as the overview workbook, then this can be omitted entirely.
[Time Sheet.xls]is the file name of the workbook, wrapped in.
Sheet1is the sheet within Time Sheet.xls that is being referenced.
!separates the filename/sheet from the specific cell being referenced.
$K$14is the reference to the specific cell in the workbook. You can, of course, also use relative addressing such asK14.
Unfortunately, from what I can tell, you can't store the reference to the workbook's location in a cell and reference it in other cells. It needs to be entered into each cell. The nice thing is that you can simply double click on the cell in the overview workbook, enter = and then click the cell in the referenced workbook and Excel will create the formula for you. (The full file path won't show up until you close the referenced workbook.)
answered Nov 29 at 17:23
Worthwelle
2,2993924
edited Nov 29 at 17:42
answered Nov 29 at 17:23
Worthwelle
2,2993924
answered Nov 29 at 17:23
Worthwelle
2,2993924
answered Nov 29 at 17:23
Worthwelle
2,2993924
2,2993924
this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help!
– fresh_french
Dec 1 at 13:00
add a comment |
this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help!
– fresh_french
Dec 1 at 13:00
this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help!
– fresh_french
Dec 1 at 13:00
this is the actually the method that i am using now and it takes a lot of time especially if you're dealing with hundreds of workbooks. the closest thing that i have worked on so far is using power query. i was thinking more of a macro that could extract data from the links provided. appreciate the help!
– fresh_french
Dec 1 at 13:00
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1379392%2fdata-extraction-from-hyperlinked-workbooks%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
