MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
(OP)
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





RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
tg
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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.
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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 <> ""
Workbooks.Open form
data(no) = Workbooks(form).Sheets("sheet1").Cells(4, 1).Value
no = no + 1
form = Dir
Loop
End Sub
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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.
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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 = "C:\forms\*.xls"
form = Dir(filepath)
Do While form <> ""
ActiveCell.Value = "='c:\forms\" & "[" & form & "]sheet1'!$a$4"
form = Dir
If form = ActiveWorkbook.Name Then form = Dir
ActiveCell.Offset(1, 0).Activate
Loop
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
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 "Date Due" and an "Amount Due" 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?
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS
filepath = "C:\forms\*.xls"
myrow = 3 'the first row to be used in the collection sheet
form = Dir(filepath)
Do While form <> ""
Cells(myrow,1).Value = "='c:\forms\" & "[" & form & "]sheet1'!$a$4"
Cells(myrow,2).Value = "='c:\forms\" & "[" & form & "]sheet1'!$c$4"
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.
RE: MAKING A LIST OF DATES WHICH ARE PULLED FROM MULTIPLE SPREADSHEETS