Reference Data from multiple Spreadsheets (Names keep chanding)

Hi there. i have been trying to find this through your forum but so far either i suck or i havent looked in the right place,

I am looking to get a spreadsheet to reference a specific cell in another spreadsheet. the problem being that the name of the other spreadsheet wont be the same each time. the other issue being that there is multiple spreadsheets i need to reference, but they will all be in the same folder.

so is there any way to get a formula that can pull out the data from a group of spreadsheets based on where they are stored more than the names that they are given?
I am not looking for a solution as such as i would like to learn how to do it (more interesting than copying and pasting something) but i will be honest ive not got a clue and the time scale isnt drastically important so it will take as long as it does to learn what im doing :-)

I have tried playing with it but i will be honest i havent played with VB since i was in school (12 Years ago) so a little rusty (seized)

by multiple spreadsheets you mean workbooks or sheets within a workbook?

if you are going to do VBA
one way to write a code regardless of the sheet name is to declare worksheet as variable

Please Login or Register  to view this content.

that way each time you address ws it will be the sheet you had active at the time

if this isnt going to be plausible i suggest a sample spreadsheet is in order

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Originally Posted by humdingaling

by multiple spreadsheets you mean workbooks or sheets within a workbook?

if you are going to do VBA
one way to write a code regardless of the sheet name is to declare worksheet as variable

Please Login or Register  to view this content.

that way each time you address ws it will be the sheet you had active at the time

if this isnt going to be plausible i suggest a sample spreadsheet is in order

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Thank you for your response,
I mean seperate workbooks. There will be quite a few
Of them so loading them all wont really be feasible.

I believe the data i need is also in the worksheets title but has extra characters with it. Such as the title will be abcd.za012345.xls and all i need is the za012345 would this be an easier way to figure out? All of the xls file will be stored in one place so will just need to figure out how to reference them to a cell and delete the first few digits. Ive been talking to people at work about it and theyve said its doable although they camt explain how. When im back at my pc i will upload some example sheets.
Thank you.