Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Link to a databse (Excel XP / 2002)

    I have a spreadsheet of say 5 tenant rental payments due each month that word pulls from to print out rental notice requests for payment. These 4 tenants have yearly increases in their rent at different times in the year ie. one increases in Jan another increases in OCt etc
    Is there a way that the excel rental spreadsheet can check against a database or another spreadsheet that this is now Oct and the rent should be increased to "x" amount for this Tenant. Is there a good way of doing this to save me time of checking each month manually which tenants need to be increase???
    Jerome

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    If you could post a censored part of your data, we could provide a working example of a macro that does what you need.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    Spreadsheet A:
    X technologies Photographer
    rent front $1,749.00 rent $1,643.00
    water $0.00 water $0.00
    waste $18.32 waste $18.32
    Total $1,767.32 Total $1,661.32
    The totals ie 1767.32 and 1661.32 are then linked to a word letter merge Dear Computer you owe for Feb 1767.32 and Dear Photographer you owe......
    The difficulty for me is knowing wheter the rent should be increased this month as determined by a lease.
    I can store in any way, ie spreadsheet B or database B, that makes it easy that say Feb 2003 the rent for the photographer is now 1693 and in March the rent for the computer is now 1752.and in year 2004 etc.as determined by their leases, I would have to enter say 5 years of increases for each tenant.
    Do you see the problem I am having, I just do not want to keep manually checking another spreadsheet, eg spreadsheet B, to see if it is the required month to increase which tenant and then by how much.for spreadsheet A that the word letter does a letter merge from. I have access database, but I don't think that will be of big help???
    Jerome

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    Did my reply make sense????
    Jerome

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to a databse (Excel XP / 2002)

    It made sense for what was there. Your example does not have any info on lease dates or dates to increase so there was not much we could do with it.

    Also it is best if you attach an example worksheet with all the pertinent info so that we may look at it and play without having to create one from scratch

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    Steve:
    I have attached a limited spreadsheet including copies of the area of leases that show increases. I am looking for insight on how to best structure a computer automation to check for rent increases each time I send a monthly invoice for payment of rent. that will also increase the rent without any input from me. I can keep a separate spreadsheet, or use an access database. Again the main spreadsheet links to a word document for invoices. It is updating the main excel spreadsheet each month with the correct rent amount that is the area I am stuck on both structurally and mechanically
    Thanks for any help
    Jerme
    Attached Files Attached Files
    • File Type: xls c.xls (40.0 KB, 1 views)

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to a databse (Excel XP / 2002)

    I am still not sure what you want. Have a look at this workbook and see if gives you some ideas.

    Given a renter row number (in my example) obtained through a cbo, and a date you can get the renter information.
    I gave each renter a type 1 or type 2 based on the 2 tables you provided.

    I have lookup table of rents, costs etc for for the 2 types you listed.

    All the renter info will change based on the date

    Steve
    OOPS!: EDITED to Add attachment
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    <P ID="edit" class=small>(Edited by jrklein on 22-Jan-03 20:13. )</P>Greetings
    Your sheet comes close to what I am looking for and I appreciate all your help

    My constraints so you understand are the following
    1. The date at the top is a month ahead of todays date so when I bill in January for February the word merge automatically shows Rent due for FEBRUARY IS
    Attached Files Attached Files
    • File Type: xls c.xls (23.0 KB, 1 views)

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to a databse (Excel XP / 2002)

    Since I only understand about 50% of your workbook, I still don't know what you really want.
    i don't understand where many of the numbers come from , waste, water, how they get divided up, etc. If those numbers change every month, they need to have a place for entry.

    Also the spreadsheet is very convoluted. Many of the numbers are not numbers and many of your dates are NOT dates! I also don't see the underlying logic for some of the placement of numbers.

    I think you really need to sit down and provide us with some detailed info of WHAT you NEED and WHAT data you will have now and in the future before we can provide any help.

    When I find some time, I will take another stab at it, but it looks like it is not going to be high on my to-do list since the majority of my working with it is going to be trying to figure out what the numbers mean and what they will be in the future.

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    Steve:
    My apologies; forget everything but the 7 or 8 separate Tenant rent fields, (water, waste tax, not relavant to what I need so just forget) Your original spreadsheet seemed to be almost there but a drop down for individual tenants needs to be for all tenants to be seen at once. IF I CAN PAY YOU SOMETHING TO BECOME HIGHER IN THE PRIORITY LET ME KNOW IF THAT IS A GOOD IDEA..I have attached a modification of your sheet to show more of what I need. The increase is great but can it work on Month and Year as not all increases are as of Jan 1st.
    Can the spreadsheet allow for growth of tenants
    Thanks.
    Jerome
    Attached Files Attached Files
    • File Type: xls d.xls (18.5 KB, 1 views)

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to a databse (Excel XP / 2002)

    I am a little offended. I do this as a volunteer and I help to those requesting it. I have a full time job, a family, and many other posters that I try to answer. SImple answers come first, ones that I have to play with take time to sit down and understand.

    The biggest problem is trying to understand what is requested.

    Based on your last attachment (which was mine with just one of the "tables" from yours, you seem to ONLY want a solution with the "rent from" which is relatively straightforward to calculate. I built nothing in to get any of the other items (you hadn't provided enough in your other sheet to make anything useful anyway)

    I redesigned your table and put in a calulated column to mark for each Tenant, which is the "current". This will change as the date changes (TRY IT by changing the date).

    The rent from is just a lookup into that table. I added two entries and made them "no value" since 2 of the tenants do NOT have current rent info in the table. The rent in the table does not start until Oct 2003, and you didn't give the rent that started in Oct 2002!

    The tenant name above the "rent from" MUST match the entries in the col A of the Table. As you add more entries you must copy the contents of col D to complete the table. You must also edit the "size" the Named range RentTable to include any new items. Insert name - define, choose the name and edit the range.

    Hope this helps,
    Steve
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    <P ID="edit" class=small>(Edited by jrklein on 23-Jan-03 23:26. )</P>Steve:
    This is wonderful, just what I wanted, the water and tax cells I can add and are independent of the lease. My apologies, i did not mean to offend. I have had wonderful experience in this lounge, and I don't want to take advantage of the lounger's time, so sometimes the contributor has suggested that in order to allow him to spend extra time that I pay him. The results have always been well worth it. Again it was only a suggestion so as not to interfere with your responsibilities to your job and family.
    Thanks again and thanks for advising how to add to the table. Lastly is it possible for readability to highligt the current tenant line in yellow see attached
    Jerome
    Attached Files Attached Files
    • File Type: xls c.xls (16.0 KB, 1 views)

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to a databse (Excel XP / 2002)

    See attached. I used cond format
    (format - conditional format - and set the formula to look for it NOT being an NA in the column

    I also added some additional rows to "expand the range"
    If you continue inserting above that last row, the "table name" will automatically expand. You will still have to copy the "formula" however.

    Steve
    Attached Files Attached Files

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Link to a databse (Excel XP / 2002)

    <P ID="edit" class=small>(Edited by jrklein on 24-Jan-03 12:29. )</P>Steve:
    phenomenal, very user friendly, just what I wanted THANKS FOR EXTENDING THE TABLE AUTOMATICALLY, A REAL HELP.
    Two last questions 1.I use the following formula =DATE(YEAR(B12),MONTH(B12)+1,1) (Where b12 holds the current system date) as the main date so that it yields the following month for billing, Example I am billing in January 20th for February rent due, so the letter will merge Feb automatically. Will your formulas work on this formula or do I need to adjust. I tried using it late last night but did not seem to work, but was too tired, and too unintelligent to see why it was not.
    2. More IMPORTANTLY IS it easy to add a comment SUMMARY box, text summary box, area of the spreadsheet OR pop up box etc or whatever, telling the User that this month that Tenant A increased from x dollars to y dollars and that Tenant D did the same. or if too hard just that Tenant A and Tenant D had their rent increased this month?????? to add to the comfort level that rental adjustments were made???
    Again my sincerest thanks.*********** It is wonderful.
    Jerome

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to a databse (Excel XP / 2002)

    1) it will grab from the table the date the amounts in place for WHATEVER the date is F1. You could put next YEAR's date in and the NEW amounts will be extracted (and Highlighted)
    2) I added a row in the spreadsheet and above each tenants name a message will be displayed if the MONTH in F1 equals the MONTH of the START Date. I also added a startdate range (this will also expand as the rows are added)
    It displays "Rent Inc from $x" You can expand the text if you want. I didn't include the CURRENT rent since that is IN the table and the text, I though was getting too long.

    Steve
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •