+ Reply to Thread
Results 1 to 10 of 10

Macro to fill in a static form with formulas using the next row of a reference sheet

  1. #1
    Registered User
    Join Date
    01-20-2021
    Location
    Sydney, Australia
    MS-Off Ver
    2016
    Posts
    4

    Macro to fill in a static form with formulas using the next row of a reference sheet

    Hi Gurus,
    I am fairly new to macros in t hat its been a while since i have worked with them and have forgotten alot....
    I have a dynamic form in worksheet "Part Form" and a data dump from an app in worksheet "App Data import"
    I have formulas in the static cells fill in the "Answers" from the app Data import that all reference the same row number but am looking to get a macro if possible whereby if I run the macro it tells the formulas to reference the next row down in all of the formulas.
    I hope this makes sense and I apologise if I haven't included all things required.

    L
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    No macro required.
    I have changed all formulas. Spin button added to O4 cell. It is linked to P4 cell. When the spin button is operated P4 changes.
    If P4=3, 3rd row data will be selected
    If P4=8, 8th Row and so on.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,944

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    Or perhaps this:

    Please Login or Register  to view this content.
    BSB

  4. #4
    Registered User
    Join Date
    01-20-2021
    Location
    Sydney, Australia
    MS-Off Ver
    2016
    Posts
    4

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    Thanks so much, this works great, I will have to apply the formula changes to all of the "answer cells in the full form document to test its full functionality and cannot thank you enough for your assistance.

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,944

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    Just out of interest, which approach is the one that did what you need?

    BSB

  6. #6
    Registered User
    Join Date
    01-20-2021
    Location
    Sydney, Australia
    MS-Off Ver
    2016
    Posts
    4

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    Hi BSB, so far I have used spin button solution, I haven't tried out the code you gave me yet but I will try it and let you know how it goes (or ask many more questions LOL)
    Thanks again

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,944

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    I wasn't sure what it actually was you were trying to do with the data. If just skipping from one record to another is the aim then the SpinButton approach is certainly an effective method.
    For some reason I had it in my head that you were printing multiple invoices or similar hence the code to loop through all records and perform an action on each.

    If you're using this workbook like a database, i.e. the form acts as a front end for viewing and editing the data, then you might want to look into UserForms. They give a much more polished feel to this type of project and often make things easier.

    BSB

  8. #8
    Registered User
    Join Date
    01-20-2021
    Location
    Sydney, Australia
    MS-Off Ver
    2016
    Posts
    4

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    Hi BSB,
    I will be needing to use the code as the Data dump from the app doesn't like the workbook to contain the spin button. The actual form is 131 field entries so its going to be a long code......
    If I have issues may I grab your help when you are available?

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,944

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    Sorry, only just seen this reply. Of course, happy to help.

    Depending on what the formulas you use and the layout of the form there may be better methods than my suggestion above, but won't know for sure without seeing it in context and having some more of an idea on what you're trying to achieve.

    BSB

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Macro to fill in a static form with formulas using the next row of a reference sheet

    Quote Originally Posted by Lmel58 View Post
    I will be needing to use the code as the Data dump from the app doesn't like the workbook to contain the spin button. The actual form is 131 field entries so its going to be a long code.....
    If you have problem with spin button remove the button and enter the row number in P4 directly. It works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto fill static cell on separate sheet based on changing cells in another sheet
    By lawrence24 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-15-2014, 12:31 PM
  2. Macro for a form control button to reset scroll bars to a static column of numbers
    By urbphoto in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2014, 04:13 PM
  3. Static date doesn't work if I use form to fill a table
    By Numbi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2013, 07:36 AM
  4. Macro to automatically fill in a sheet form multiple workbooks
    By jimmyd270682 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-30-2013, 02:39 PM
  5. [SOLVED] Dragging formulas with a static cell reference??
    By JMcDon1007 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-04-2013, 02:01 PM
  6. Macro - Add row, auto fill text and formulas with relative OFFSSET, Sheet reference functi
    By DaliosPerlai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2012, 10:47 AM
  7. Replies: 0
    Last Post: 06-05-2006, 02:45 AM

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