MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS 4

[mschiff]

I am not a very advanced Excel user, so bare with me here. I am a commercial real estate broker and every time I do a lease transaction, I fill out a deal form in Excel for our bookings department. In each deal form workbook, I am required to fill in the dates when payments are due. I want to be able to pull the dates from ALL of the deal form spreadsheets located in my C:\Deal Forms folder (there are currently about 50) and have it either create a spreadsheet or pull it into a database like Access which will allow me to see all the dates at once. In other words, how can I design a spreadsheet or Database file that will remind me when a payment date is coming up? I have way to many deal form.xls files to keep track of. I need to some how create a summary list of these dates which corrispond to the Deal # field in the form. Somebody please help

 

[pmover]

mschiff,

you can create a link from all the form.xls files to a master workbook or access database (not too familiar with access!).

should the cell with the date be a named range or is the same address in all form.xls files, it can simplify the process. this makes for easy copying, except for changing the workbook name in formulas.

1) open a new workbook and a form.xls file.
2) activate the new workbook. to create a link, type "=" and then goto the form.xls workbook and select the cell with the date in it.
3) press enter.
4) recommend that you use the conditional formatting features (from the "Format" menu) to format the cell for notification if a certain date is reached. you might be experimenting here a little, but there are other postings in this forum that explain conditional formatting.

NOTE that when you save the new workbook, the link is established. when opening the workbook with all the dates, you will received a message box requesting to update links. select yes.

this is a crude technique.

quicken has a bill reminder feature that notify's the user when certain payments/other actions need to be done. perhaps you might think about that.

regardless, good luck and advise of further questions.
-pmover

 

[mschiff]

I appreciate your effort in trying to help, but that is not going to work. Each of the forms/workbooks are named something different. I need it to pull the data from the cell g4 in all workbooks (regardless of what file name they were saved as) that are located in the folder c:\Deal Forms. Your instructions assume that I have to manually enter the name of each workbook I am pulling information from into the formula of my Master Date Reminder List.xls. Also, I need it to create a list. Your formula just pulls the date data into the cell that I entered the link formula into. I would think there is an easy way to do what I am trying to accomplish here, I just am not very advanced with Excel and don't know where to begin. I have tried to search the Internet, but I am not really sure what this formula is even called. Is it a database lookup, SQL lookup, Vlookup? I have no clue.

 

[trainguy]

Sounds to me you should think of Quickbooks Pro. Visit the Intuit website and order a demo CD. You shouldn't have to rely on Excel for such a task.

tg

 

[mschiff]

I am trying to design this for use by everyone in my company - about 200 real estate agents. I cannot tell 200 people to go out and buy Quickbooks Pro. Of course there are lots of programs where you could set reminders (such as Outlook), but that is not the point. I know that this is something that Excel can do, I just don't know how. Someone out there must know.

 

[smcadman]

This will not solve your problem completely, but may lead you to some ideas. Start a new workbook, and call it whatever. In column A put in the name of the buyers. In column B, format the column to read in "5/15/03" date format. In column C, format it to be numbers with no decimal places.
In row 1, put in the column labels in respective order: "Buyer", "Due Date", and finally "Days Away".
Row 2, Column B input "=[deal_1.xls]Sheet1!$G$4)"
Row 2, Column C input "=B2-TODAY()"
(do not use quotes)
Now using conditional format for Row 2, Column C, choose:
"Cell value is" "Between" "-7" and "4".
Then choose the format button > pattern, then choose a color. Now whenever the due date is 7 days away, or 4 days over, it will turn colors to alert you.
Something that you will have to figure out is how to call a workbook that has spaces between the name. Notice the "deal_1" I used above (used underscore between words). If the workbook name has spaces between each word, it causes an error.
I know, I know, you don't want to input the name of each buyer manually into the formulas, but someone else will have to help you there, and on the bright side, you will only have to do it once.

Flores

 

[ivanlocke]

There is a fairly simple way of doing this. Hopefully as others have said your date cells are at the same address for each of your workbooks. However instead of manually linking each one by linking one, copying it, and then changeing the filename for each file, you should be able to write a macro to do this for you.

I don't have excel infront of me, but this is what you would be looking to do:
-read into array the directory list for specified directory (to make it easy you will probably just want the excel files you are dealing with and nothing else in the directory)
-for/next loop for number of files (length of array)
-in loop will copy from specified addresses from current file from array to your workbook and then advance the current cell (so you don't overwrite with the next files data)
-you can then make this macro run on loading the file

You should be able to get macro/vba help from excel or many websites (just search for excel macros).
Or if I remember when I get home, I can try writing it up.

 

[mschiff]

Thanks to both of you. I will try these suggestions and let you know how it turns out. Ivanlocke, this is a little over my head, but I will attempt to figure it out. Thanks again. B-)

 

[pmover]

i concur with ivanlocke and had originally consider using vba for this situation, but you clearly stated your excel skills were limited. so i opted not to pursue the matter using vba.

secondly, it would take some effort (an hour or two) in accomplishing what you are pursuing using vba and then there is the possibility of problems occurring (do not know what other users can/will unknowingly do), thus further complicating the situation.

if you wish to pursue the vba option, have the macro cycle through all the workbooks in specified path (c:\deal forms\...), obtain needed information from individual workbooks, and place needed information (dates, etc.) in new workbook. then conditional format the cells and save new workbook.

additional code could be written to provide a notification to the users, but excel would need to be activated (either by user or when pc/windows is started. otherwise, the user will need to open the file on a regular basis and observe the formats.

regardless, your situation is not that complicated and it is doable, but the effort by individuals is noteworthy. a crude, but simple way is expained by smcadman and myself (although not described in detail).

i do not have the time to work with you on this matter. perhaps in a week or so, i may.
good luck!
-pmover

 

[mschiff]

Thanks. I appreciate all your comments and help.

 

[smcadman]

Here is something else to add to my "crudeness". Open Notepad and write "dir > file-list.txt" (without quotes)and save as "Make list of files.bat" (again without quotes, and .bat is the file type, not .txt). Now put all of your deal quotes in one folder and put the "Make list of files.bat" in that folder and double click it. It will make a .txt file called "file-list" containing all the contents in that folder.
Now in Excel, choose open, then in file types choose "All Files", then choose file-list.txt. The Text Import Wizard dialog box will pop-up. Choose "fixed width" then "finish".
The last column should contain your Deal Form Names.xls, you can delete the other columns.
This is a very quick way of getting all of your names into Excel without knowing VBA.

Flores

 

[mschiff]

Thanks smcadman. I just got back into town and read this. I will give it a go - looks like a great idea. Thanks again for all of your help.

 

[SteelDesignerSV]

I had a similar problem. If all of the spreadsheets are in a common folder that helps. and it generally helps to have named cells or ranges so that it is easier to find stuff. Basically you just make a macro that opens up each excel file in the folder find the data then and write it to your mastersheet. If you want it to be hidden you can create a hidden instance of excel to open the files and pass the information then close the object. You can also automate it to run every night or something like that.

 

[ab123456]

I see its over a month now since you posted, hopefully the following may help if you have not solved the problem already.

This code searches for every excel file in the folder "c:\forms" and reads the value of the cell A4 in each workbook. You can then add additional code to write the data to wherever you want to put it.

Sub macro2()
Dim data(100)
filepath = "C:\forms\*.xls"
form = Dir(filepath)
no = 1
Do While form <> &quot;&quot;
Workbooks.Open form
data(no) = Workbooks(form).Sheets(&quot;sheet1&quot.Cells(4, 1).Value
no = no + 1
form = Dir
Loop

End Sub

 

[mschiff]

ab123456,

This is great. Thank you so much. I tried the other suggestions people gave, but they did not work very well (perhaps I was not doing them correctly). Thanks for taking the time to actually write out the code for me. I will give it a go and let you know how it works. Thanks again.

 

[ab123456]

mschiff,

This improved? code, will put links into a spreadsheet rather than just values. It also means that when the macro has finished you havent got numerous workbooks open.

filepath = &quot;C:\forms\*.xls&quot;
form = Dir(filepath)
Do While form <> &quot;&quot;
ActiveCell.Value = &quot;='c:\forms\&quot; & &quot;[&quot; & form & &quot;]sheet1'!$a$4&quot;
form = Dir
If form = ActiveWorkbook.Name Then form = Dir
ActiveCell.Offset(1, 0).Activate
Loop

 

[mschiff]

ab12345,

This worked great. Thank you so much. Since it ends with a loop, I obviously have to make a seperate macro for each field I am pulling date from. For example, I have a &quot;Date Due&quot; and an &quot;Amount Due&quot; field and need to pull both fields (they are not located next to each other so I cannot do a range). I created one macro that fills in the Date Due and another that fills in the Amount Due. Is this the way I should be doing it? If so, how do I make the second macro run automatically after the first macro is finished running?

 

[joerd]

You can fill both fields at the same time, which will probably be the fastest solution:

    filepath = "C:\forms\*.xls"
    myrow = 3    'the first row to be used in the collection sheet
    form = Dir(filepath)
    Do While form <> &quot;&quot;
        Cells(myrow,1).Value = &quot;='c:\forms\&quot; & &quot;[&quot; & form & &quot;]sheet1'!$a$4&quot;
        Cells(myrow,2).Value = &quot;='c:\forms\&quot; & &quot;[&quot; & form & &quot;]sheet1'!$c$4&quot;
        form = Dir
        If form = ActiveWorkbook.Name Then form = Dir
        myrow = myrow + 1  'go to the next row
    Loop

I have replaced the ActiveCell and .Activate statements by a row counter (myrow) which starts at 3, but you can modify this of course. The macro will fill column A (= column 1) with a formula referring to $A$4 in the source sheets, and column B (=column 2) with a formula referring to $C$4. Modify as needed ;-)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[mschiff]

Looks Great! Thanks, I'll give it a go.