+ Reply to Thread
Results 1 to 18 of 18

Press button to open form for data entry and then send matching data to another sheet

  1. #1
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Press button to open form for data entry and then send matching data to another sheet

    Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.

    Thanks in advance,
    Andrew
    Last edited by drewship; 05-19-2009 at 11:34 AM.

  2. #2
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    Unable to upload file...something wrong with the forum uploader. I can send via email...will keep trying to upload.

    Thanks,
    Andrew

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Press button to open form for data entry and then send matching data to another s

    Hello drewship,

    When I experience problems with the server, logging out of the forum and logging back on usually solves the problem.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    Thanks Leith Ross, but even after a reboot, I get a 'Database Error' when I try to upload my 1.5 MB zip...will keep trying...

    Andrew

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Press button to open form for data entry and then send matching data to another s

    Hello Andrew,

    A few others have posted similar problems with uploading. Looks like it is on the Forum's server side. I'll let admin know.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Press button to open form for data entry and then send matching data to another s

    I've just uploaded a file, are you sure the file does not exceed the forum limits? Try zipping it.

    Also, take a look at the databaseform here

    http://www.excel-it.com/vba_examples.htm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    I just tried to upload a blank txt document from home (previous attempts were from work, which have been successful in the past) and received the following error message:

    upload test.txt:
    Upload of file failed.

    I tried the 1.5MB zip file again with the same database error so it looks to me like it is the forum servers as Leith Ross indicated.

    I will try from work again tomorrow.

    Thanks,
    Andrew

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Press button to open form for data entry and then send matching data to another s

    Hello Andrew,

    A message has been sent to the Admin. I haven't heard word back yet on the server status. If you still have problems tomorrow, email me the file and I'll post it for you. My email is [email protected]

  9. #9
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    Thank you. Hopefully it will be fixed by the time I get to work.

    Andrew

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Press button to open form for data entry and then send matching data to another s

    I've uploaded several files with no problems. have you checked file size as I suggested earlier? You can always use file sharing site like yousendit.com, posting the download link here

  11. #11
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    royUK,
    I did reverify the size: 1.50 MB (1,582,726 bytes) zip file. It still is not uploading and since yousendit.com is blocked at work, I will email it to Leith Ross in the hopes it can be uploaded by a moderator.

    Thanks,
    Andrew
    Attached Files Attached Files
    Last edited by Leith Ross; 05-06-2009 at 02:49 PM. Reason: Added workbook

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Press button to open form for data entry and then send matching data to another s

    Hello Andrew,

    After getting locked up several times while attempting to upload your file, I have concluded the file size limit for Zip files is wrong. Instead of 99.7 MB it is 997 KB. It was necessary to remove to the conditional formatting and about half of the formulae on the "Products" sheet. I will inform Admin about this error.

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Press button to open form for data entry and then send matching data to another s

    Leith... wonder if you want to now remove your email address so it's not so "public" and open to spam?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Press button to open form for data entry and then send matching data to another s

    Hello NBVC,

    Thanks for being concerned about the spamming issue. I have several safeguards in place for spam protection.

  15. #15
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    Thanks Leith!!

    Andrew

  16. #16
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    Here is a slightly updated version of the file I need help with and my original request.

    Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.

    Also, I need to keep a running total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.

    Thanks in advance,
    Andrew
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    Been working on the second part of my request since that seemed easier for me, but when I tried to combine the IF and SUMIF statements in what I thought was a logical and correct format, I did not get teh expected result.

    =IF(Distribution!M:M=5,(SUMIF(Distribution!E:E,A151,Distribution!F:F)),0)

    Since the '=SUMIF(Distribution!E:E,A151,Distribution!F:F)' already works by itself, I thought I conld combine it with 'IF'.

    Logically, for me anyway, this would check column 'M' on the Distribution tab and everytime a '5' was found, the SUMIF function would run.

    The May section of the FY09 tab displays a Qty of 0 instead of the values indicated on the sheet which should be 7, 12, and 14 respectively. How can I use 'IF' to check a column?

    Thanks in advance!!

    Andrew
    Attached Files Attached Files
    Last edited by drewship; 05-07-2009 at 12:11 PM. Reason: corrected the correct values the spreadsheet should be showing

  18. #18
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press button to open form for data entry and then send matching data to another s

    All,
    Although I am still having a couple calculation issues, the question this thread was started for has been solved. I have attached my latest workbook to help others who may have similar problems.

    Thanks to everyone who assisted!!!

    Andrew
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1