Struggling to merge / consolidate new information into a master file

[WEBBERR]

I've been struggling with a particular problem and would summarise it as follows There is additional information in the attached file)

I receive workbooks from individuals/Users each month, each workbook contains 1 work sheet. they are all identical in layout when sent out but may have "Issues" when they come back to me. what I di is store them all in a folder along with a master file which has a copy of all the sheets, and 2 summary pages. I import them by copy/paste to update the summary. sometimes I have additional sheets to add into the summaries, and sometimes older ones need to drop out, which means I regularly have to update the summaries which is very time consuming.

I wondered if there were a way to create a dynamic range which was an offset from a cell on the summary sheet to the same place on all of the other worksheets in the book without specifically naming them as when new ones are added they would then not need specifically linking in.

the first summary is different altogether and looks at 2 specific rows only (again from each of the tabs) and subtracting one from the other to get a balance.

Sorry if this is not a very good explanation

there is a bit of code in there which auto names the sheet tab according to the contents of a specific cell which I use to ease the naming of the tabs in a recognisable form. which you are welcome to if it is of any use to you.

Thank you to anyone that can help.

 

[vletm]

@WEBBERR I'm not so sure what do You really mean, but I'll try ...
Your file has three fixed name sheets! ( and I did some backups for me tests. )
You can use max ten projects; no matter of names.
After move ... copy ... however, You have Your projects in this file.
Press [ Do It ] (Summary By Category)-sheet ...
Your projects summary will counted to sheets 'Summary By Category' and
'Cash Flow By Project'. ... just one 'Click'!
I really think that there were some 'mistakes' with some formulas, sorry.
Check 'Cash Balance' row! You missed 'BlueYonder'-values, did You?
Actually, there are only few formulas left.
So far, I left only 'light orange'-cells some formulas - You could check those too?
About Chart ... if You have any number of projects (1..10),
the Chart should show all of those.

Did You mean something like this? Ideas?
... actually You don't need to copy 'manually' those projects to this file.
It can do 'automatic' too.

 

[WEBBERR]

this code is very clever!, would you put comments in the code to explain what the bits are doing? I am very grateful for your help.

I Have up to 100 projects at any one time. which part sets the limit at 10, what is the reason for the limit?

 

[WEBBERR]

I already have written some code that is able to list out the files in the folder and import them into the WB. I was struggling to get the summaries to look at the newly imported sheets. your work is amazing!!

 

[vletm]

@WEBBERR sample's max ten projects is just one value, I can change it to 100. (You has named two projects) Only, maybe the Chart don't like it and You can add those added 90 datas to that Chart.
Before, I'll start to commenting the code, I would like to get answers to my questions about formulas. ex. The 'Cash Balance' row and 'Light orange' -cells. Because 'all formulas' will be within code, all values will always overwrite.
And also, I need You code, how do You import files to this workbook. If something don't match then it'll need less fixing later.
Ideas?

ps. Your newest : http://forum.chandoo.org/threads/dynamic-link-between-sheets.26718/ can solve almost like this: http://forum.chandoo.org/threads/tracking-accounts.26662/

 

[vletm]

@WEBBERR ver2
1) Max 100 projects
2) to modes,
a) form workbook as before
b) with preset list of files, can select projects (needs those two 'datafiles')
Still needs those 'light orange'-formulas.
Ideas?

 

[WEBBERR]

Hi sorry for the delay in replying. I have looked at your comments in regard to the missing formulas. The sheets come to me from different sites and sometimes they have errors when they come back to me. the cash balance is the (total value - the total cost)

your list tab is very similar to mine, except mine contains the area (start and end points) that the source data is in. my code is in a module, and copies the selected area from the source file into the summary file.

I'm still trying to understand your code I like the way in which new tabs are automatically added to the summary page, that was beyond my expectations

What is "2) to modes" I don't understand what you mean by this.

My source files are stored in the same folder as the summary file. I have 2 parts of code, the first creates the list of files that are in the folder that need to be opened and the data extracted, and the second opens each file in (the list) in turn, and copies the selected range, and pastes (results of formulas) onto the correct tab in the summary file. I was hoping then that the formulas on the summary (when perfected) would add them all together.

What does your code do? when you say no need for those two sheets with this version, does it open the file and add the figures to the summary without importing the information? so that they stay outside the summary file altogether? but not linked, just with the totals included? if so this is very clever.

You have gone beyond what I expected to obtain from the forum and I am very grateful. I would like to learn your skill very much

 

[vletm]

@WEBBERR
1) So 'my cash balance' is right, what about other 'missing formulas'?
2) What are 'start and end points'?
3) Ouch! '2) to modes' should written like 2) TWO modes
... 1st mode, like Your, You copy Your max 100 projects to this workbook and press [DoIt]!
.. 2nd mode, You could use

• -sheet to run Your max 100 projects WITHOUT copy/paste or whatever to this workbook with one click [DoIt] on 'List'-sheet.
  You have to have make a list of files and mark which projects do You want to join to this Workbook.
  Did You test this '2nd mode' with those two sample project-files?
  >> 2nd Mode - no copy nor paste - needs select from list - to get the result.
  and as You notice, no need to use all possible files from that folder.
  Ideas?

 

[WEBBERR]

Ah, 2Mode. You mean "Module", Your English is much better than my German!

This is the code I am using to create my list of files:-

Option Explicit

Sub GetFileNames()

Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$

Dim UserId As String

UserId = Environ("UserName")


InitialFoldr$ = "C:\Users\" & UserId & "\Desktop" & "\Site Returns Black And Whites\" '<<< Startup folder to begin searching from

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
Range("B2").Select
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF([@[File Name]]<>0,ROW()-1,"""")"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF([@[Item No]]>0,SUBSTITUTE(Myfullname(),myname(),""""),"""")"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=""$c$5"""
Range("E2").Select
ActiveCell.FormulaR1C1 = "=""$AO$72"""
Range("F2").Select
ActiveCell.FormulaR1C1 = "=LEFT([@[File Name]],6)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=""$C$5"""
Range("I9").Select

End Sub


I like the idea of the second code very much I can use the above to create the list, in conjunction with your code to select which files and then click "do it" to bring the information in that I need.

The missing formulas are the result of anticipated figures changing to actuals on a monthly basis, but they are only subtotalling the form for information.

the Black Background white font boxes are subtotals of the items above them, the orange cells are highlighting the area that the users fill in. sometimes they put formulas in there but what is important is the result of the formulas. any formula in an orange cell can be replaced with its result.

I understand your code a little better now but it is much more advanced than my own, I have not been writing code for very long. each step takes me a long time, as I am learning as I go.

Thank you once again for you help you are very helpful