Forum Discussion

McJan2495's avatar
McJan2495
Copper Contributor
Jan 20, 2024

Excel, Formulas and Functions

I'm trying to set up a New Worksheet in an existing Workbook.  The New Worksheet has 7 columns where the 1st column is the date and the other 6 columns are linked to data in other worksheets in the same workbook.  The linked data on other worksheets is updated automatically via a weblink during the work week.  My problem: On the New Worksheet, How do I limit the update of the linked data on the New Worksheet to only a specific date listed in the 1st column? And finally, how do I lock in the linked data values on the spreadsheet the end of that date (so they won't change on the following day)?

I've tried 2 other suggestions that I found on the web, without success.

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 22, 2024

    McJan2495 

    To achieve the functionality you are describing in Excel, you can use a combination of formulas, such as VLOOKUP or INDEX/MATCH, and possibly a macro or VBA (Visual Basic for Applications) code to automate the process. Below is a step-by-step guide:

    1. Linking Data to the New Worksheet:
      • In the 1st column of your new worksheet, enter the specific date you want to display.
      • In the other 6 columns, use formulas to link the data from the other worksheets based on the date. For example, if the data is in a worksheet named "DataSheet," you might use a formula like =VLOOKUP($A2, DataSheet!$A:$G, 2, FALSE). Adjust the range and columns as needed.
    2. Limiting the Update to a Specific Date:
      • To ensure that the data updates only for the specified date, you can use an IF statement or other conditional logic in your linking formulas. For example:

    =IF($A2=TODAY(), VLOOKUP($A2, DataSheet!$A:$G, 2, FALSE), "")

    This formula will only fetch data if the date matches the current date (TODAY()). If not, it will display an empty string.

    1. Locking in Data at the End of the Day:
      • To "lock in" the data at the end of the day, you can use a macro or VBA code to replace the formulas with their values. This can be done manually or automatically at a specific time.

    Vba Code is untested, please backup your file before use.

    Sub LockInData()
    Dim ws As Worksheet
    Dim rng As Range

    ' Set the worksheet and range
    Set ws = ThisWorkbook.Sheets("YourNewWorksheet")
    Set rng = ws.Range("B2:G100") ' Adjust the range as needed

    ' Copy and Paste Special Values to lock in the data
    rng.Copy
    rng.PasteSpecial xlPasteValues

    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

    You can run this macro manually, or schedule it to run at a specific time using Excel's built-in features or Windows Task Scheduler.

    Remember to replace "YourNewWorksheet" with the actual name of your new worksheet, and adjust the ranges and formulas according to your data layout.

    Please note that the actual implementation might depend on the specifics of your data and workbook structure. The text and steps were edited with the help of AI.

    Try these steps, if any of them resolve the issue. If the problem persists, it might be helpful to provide more details about your Excel version and any specific actions or conditions that seem to trigger the racing lines. In this link you will find some more information about it:

    Welcome to your Excel discussion space!

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources