Posts: 8,130
Threads: 201
Joined: Feb 2019
Reputation:
55
Any on here? Trying to get a spreadsheet to call in a load of data from another one. Easy enough, but not when you have to change the referenced workbook for a few hundred others. Oh and to me, and Macros are something where you don't have to pay VAT.
Tried turning back off and on again already. That made it go away for a bit, but that's all.
Posts: 7,975
Threads: 255
Joined: Jan 2019
Reputation:
6
The Johnson will know. The 3 amigos - Word Outlook and Excel
Posts: 4,733
Threads: 105
Joined: May 2019
Reputation:
3
(11-22-2020, 10:53 PM)MancBaggie Wrote: The Johnson will know. The 3 amigos - Word Outlook and Excel
Stick it on the later base
Posts: 12,682
Threads: 1,158
Joined: Jan 2019
Reputation:
15
Why don't you use Excel's Query functionality and name the sheet/table within that query?
https://analystcave.com/create-microsoft...and%20more.
Posts: 66
Threads: 6
Joined: Feb 2019
Reputation:
0
11-23-2020, 12:01 AM
(This post was last modified: 11-23-2020, 12:03 AM by baggiemike.)
(11-22-2020, 10:50 PM)HawkingsHalfpint Wrote: Any on here? Trying to get a spreadsheet to call in a load of data from another one. Easy enough, but not when you have to change the referenced workbook for a few hundred others. Oh and to me, and Macros are something where you don't have to pay VAT.
Tried turning back off and on again already. That made it go away for a bit, but that's all.
Try "VLOOKUP" it will take data from sheets and applies it
into other sheets depending upon the parameters you set.
Lots of info on Google and You Tube
Hope this helps
Posts: 8,130
Threads: 201
Joined: Feb 2019
Reputation:
55
11-23-2020, 12:03 AM
(This post was last modified: 11-23-2020, 12:04 AM by HawkingsHalfpint.)
FWIW, the internet is no use. Otherwise wouldn’t be posting here.
Used to use an add-in called MoreFunc which isn’t supported in 64-bit, sadly. The argument was called INDIRECT.EXT. Maybe if I’d inserted the terms Brexit, SJ or Slav’s Future, it would have been a better argument. Sigh.
(11-23-2020, 12:01 AM)baggiemike Wrote: (11-22-2020, 10:50 PM)HawkingsHalfpint Wrote: Any on here? Trying to get a spreadsheet to call in a load of data from another one. Easy enough, but not when you have to change the referenced workbook for a few hundred others. Oh and to me, and Macros are something where you don't have to pay VAT.
Tried turning back off and on again already. That made it go away for a bit, but that's all.
Try "VLOOKUP" it will take data from sheets and applies it
into other sheets depending upon the parameters you set.
Lots of info on Google and You Tube
Hope this helps
Thanks Mike. VLOOKUP is handy, but not what I need here.
Posts: 66
Threads: 6
Joined: Feb 2019
Reputation:
0
(11-23-2020, 12:03 AM)HawkingsHalfpint Wrote: FWIW, the internet is no use. Otherwise wouldn’t be posting here.
Used to use an add-in called MoreFunc which isn’t supported in 64-bit, sadly. The argument was called INDIRECT.EXT. Maybe if I’d inserted the terms Brexit, SJ or Slav’s Future, it would have been a better argument. Sigh.
(11-23-2020, 12:01 AM)baggiemike Wrote: (11-22-2020, 10:50 PM)HawkingsHalfpint Wrote: Any on here? Trying to get a spreadsheet to call in a load of data from another one. Easy enough, but not when you have to change the referenced workbook for a few hundred others. Oh and to me, and Macros are something where you don't have to pay VAT.
Tried turning back off and on again already. That made it go away for a bit, but that's all.
Try "VLOOKUP" it will take data from sheets and applies it
into other sheets depending upon the parameters you set.
Lots of info on Google and You Tube
Hope this helps
Thanks Mike. VLOOKUP is handy, but not what I need here. Does this help.
https://trumpexcel.com/combine-multiple-worksheets/
Posts: 8,130
Threads: 201
Joined: Feb 2019
Reputation:
55
Thanks Mike. I’ll give it a try.
Getting the data (from a few different cells in each file, in the same cell location) from a closed workbook seems easy enough. What I’m struggling with is to copy the formula without having to repaste the source file name into each output cell. Have a list of filenames in the first column, can point to the files and retrieve data no problem. But even a copy-paste by row is so much ballache (really can’t be arse for hundreds of filenames). What the .ext add-in used to let you do was return the value in that first column as a text string that you could place into the first set of cells in the first row, and then simply extend it downwards and the references would copy.
I’ll check this out in the morning, all I can see are grids every time I blink!
Posts: 10,700
Threads: 674
Joined: Feb 2020
Why the need for so many workbooks?
Can you not set up worksheets instead of workbooks and save the ONE workbook as opposed to many?
You see, you’ve asked for a solution based on how you think it needs to work. Tell us your requirements (the big picture) instead of asking for the formula for your solution on how you believe it needs to work.
Posts: 8,130
Threads: 201
Joined: Feb 2019
Reputation:
55
11-23-2020, 06:46 AM
(This post was last modified: 11-23-2020, 06:54 AM by HawkingsHalfpint.)
Multi user access basically FF, plus these files get created, modified and utilised every day.
The files are item masters. Their data is used in other sheets also. But I need a ‘master’ master file in order to summarise, count and verify them easily.
I know already that the correct answer is a database, and that excel isn’t a database. MS Access would piss this, but I know fuck all about how to build databases (and is this is only a temporary thing until I invest in an ERP system). Excel is cheap and easy to use in the interim. Well, mostly!
|