Multiple sheets that doesn't exist yet

[Cavatar]

Hi there everyone,

I am struggling to get this working,

My goal is to have existing workbooks to display in one workbook.
The main workbook is called "Outstanding"
Then I have multiple workbook numbers from 0 to unlimited but only a few are active at a time.
So currently I have 47 workbooks active named under numbers: 6547, 6586, 6588, 6589, 6606, etc etc
As these work orders are completed they are moved to where I no longer need them and should not be displayed in the Outstanding workbook.
New workbooks being created in the future should automatically be "called up" and be "seen" in the Outstanding workbook as soon as it appears in the same folder as the Outstanding Workbook.

Currently when a new workbook named 6758 is created I have to go into the Outstanding workbook and manually enter and link the 6758 cells so that it will display in the Outstanding workbook and once a workbook for example 6547 is completed I have to manually go into Outstanding workbook and delete those linked cells where it is displaying.

I tried manually putting in the future numbers of workbooks that will still be created in the future but that doesn't work as it throws out REF errors.

Is there a way I can do this? Does anyone know?

The code in the Outstanding workbook that calls up the information is as follows:

='C:\Sheets\[6722.xls]Sheet1'!B17

So I have tried manually changing the 6722 to future numbers but that doesn't work as it doesn't exist yet and REF error is shown.

Is there anyone way for this to be automated? for excel to "see" when a new workbook is present and put them in the Outstanding workbook? and also for the Outstanding workbook to "see" other ones like 6547 is no longer there and don't display it any more??

I open the Outstanding Workbook only at the end of the day and press the "update" button in the window that pops up so that isn't a problem for me, I don't need "live" tracking, only updated once I open the Outstanding workbook at the end of the day.

Hope someone will be able to help

Thank you so much
Cav

 

[Cavatar]

Hi there all,

It seems from what I have read on this forum with other posts, I have not given all the information needed, it looks like most people are able to do something similar to what I want to do by using VBA code but sadly I have absolutely ZERO knowledge and experience in VBA and so I would really appreciate it if someone could show me how this is done.

I will give more information about what I need which I didn't know to mention in my previous post.

I am using Excel Home and Business 2016 on Windows 8.

I have 47 workbooks (as I complete them they might go to 13 or 25 or it might go up to 42, it depends what is happening in the day), all the information is on their Sheet1's only, which I need to put into one workbook sheet called Outstanding on it's Sheet1.
Where the first sheet1 (6547.xls) ends, the next workbook (6586.xls) Sheet1 must start so that when I open Outstanding.xls it will throw out all the workbooks sheet1's under each other.

Each workbook like 6547.xls has specific cells that needs to be shown in the Outstanding.xls.
I4
D10
A16:I16
and
L16:M16

There are 20 possible rows down for
A16:I16
and
L16:M16

as the line items change down the rows.

Another thing is that if there is nothing in the row A20 for example then nothing in that row must be displayed in Outstanding.xls

So I would have listed items in Column A16, A17, A18, A19 then it stops and then there are items in A22 and then again in A28, then the outstanding sheet1 must only display those 6 rows and the blank rows inbetween must be ignored. So it will only display the row's information if there is something in that row in column A.

I can't install Mini-Sheet on my work PC so I took a picture of what the workbook looks like which the Outstanding.xls Sheet1 is pulling the data from.

If the working exist then:
I4's information in Outstanding.xls all goes into Column A
D10's information in Outstanding.xls all goes into Column B
A16 to I16 and L16 and M16 information goes into Column C,D,E,F,G,H,I,J,K,L,M,N
If there is something in A17 then the Outstanding.xls sheet1 should pull the rest of row 17 to it's columns.
If there is something in A18 then the Outstanding.xls sheet1 should pull the rest of row 18 to it's columns
and so on until it reaches row 20 which is A35 and if that is blank then it should start on the next excel workbook.

Hope this makes more sense and please feel free to ask questions if I missed anything you might need to know.

Thank you so much in advanced.
Cavatar