Excel boffins
#1
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.
Reply
#2
The Johnson will know. The 3 amigos - Word Outlook and Excel
Reply
#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
Reply
#4
Why don't you use Excel's Query functionality and name the sheet/table within that query?

https://analystcave.com/create-microsoft...and%20more.
Reply
#5
(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
Reply
#6
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.
Reply
#7
(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/
Reply
#8
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!
Reply
#9
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.
Reply
#10
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!
Reply


Forum Jump:


Users browsing this thread: