Excel, Formulas and Functions

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.