+ Reply to Thread
Results 1 to 6 of 6

Script for finding data on another tab and then changing it

  1. #1
    Registered User
    Join Date
    02-14-2019
    Location
    Scottburgh, South Africa
    MS-Off Ver
    365
    Posts
    6

    Smile Script for finding data on another tab and then changing it

    Hi,

    I have a tab with a FILTER formula on it that filters relevant order information from another tab in the same spreadsheet. I need to ad buttons on every row so that when the user clicks on it, it goes to the source data and changes the cell in the corresponding row from "open" to "CLOSED MANUALLY". My macro is not working though, can someone please have a look at my script to see what I'm doing wrong?

    function Try() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getCurrentCell().offset(0, -3).activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Form responses'), true);
    spreadsheet.getCurrentCell().setValue('CLOSED MANUALLY');
    spreadsheet.getCurrentCell().offset(1, 0).activate();
    var previousSheetIndex = spreadsheet.getActiveSheet().getIndex() - 1;
    if (previousSheetIndex <= 0) { previousSheetIndex = spreadsheet.getSheets().length; }
    spreadsheet.setActiveSheet(spreadsheet.getSheets()[previousSheetIndex - 1], true);
    };

    I tried to copy the order number in the macro as it's unique, and then search for it on the other sheet with find and replace but it doesn't work...

    Thanks a million

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,830

    Re: Script for finding data on another tab and then changing it

    It would be easier to test possible solutions if you could attach a copy of your file. De-sensitize the data if necessary. Explain in detail what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    02-14-2019
    Location
    Scottburgh, South Africa
    MS-Off Ver
    365
    Posts
    6

    Re: Script for finding data on another tab and then changing it

    Hi,

    Thank you, I have attached it to this reply to your response. My need is to have a list of open orders on the open orders tab that gets automatically extracted from the rest of the data in the Form responses tab as per attached. The user needs to change the status of the order on the source (Form responses) tab from open to closed though and it takes them too much time to find it. So I need to ad something like a button with a macro next to each row in the Open orders tab so that it will automate that exercise for them. So if they click on the button in open orders then it needs to change the order on the Form responses sheet to closed. I don't mind if you change my filtering formula on the open orders sheet as I see it's full of errors at the moment but that's not my main concern at all.

    Please let me know if you need more information.

    Thanks again
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,830

    Re: Script for finding data on another tab and then changing it

    I assume that the formulas in the Open Orders sheet are used to pull data from the Form responses sheet. If that is the case, you could do without the formulas and have a macro transfer the open orders to the Open Orders sheet. You could add a drop down list in column E of the Form responses sheet where the user could select either "Open" or "Closed". When "Open" is selected, the data for that order would automatically be transferred to the Open Orders sheet. Similarly, you could add the same drop down in the Open Orders sheet and when "Closed" is selected, the status on the Form responses sheet would be changed. If you need to delete an order that has been changed from "Open" to "Closed" in the Open Orders sheet, that can be done as well. How does this sound to you?

  5. #5
    Registered User
    Join Date
    02-14-2019
    Location
    Scottburgh, South Africa
    MS-Off Ver
    365
    Posts
    6

    Re: Script for finding data on another tab and then changing it

    Unfortunately the Form responses sheet ads a new row for every data entry. So I can edit existing data in it, but I can't set up a formula or rule that will apply for new orders as they come in because the new row will be exempt from all existing formulas etc. (and formatting)

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,830

    Re: Script for finding data on another tab and then changing it

    Some of your order numbers in column D of the Form responses sheet have a leading space (D9, D19,D65, D76 for example). You may want to remove the extra space. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Open Orders sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change the word "Open" to "Closed" in any cell in column E and exit the cell. The corresponding cell in the Form responses sheet will be changed to "Closed". If you notice a delay, it is caused by all the formulas that need to be calculated and also the circular reference error that is generated by the formulas.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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