Your help with this issue is much appreciated... I'm sure there is a simple solution but,I am stumped.
I have created a workbook with a Summary worksheet and a macro to create worksheets that are named with a Date (e.g., 27Jan2019). Dates are selected from a drop-down list and the macro is executed to create the worksheets as needed. Data is entered in the worksheet after the new worksheet has been created.
The Summary worksheet has column headers labeled with each of the dates listed in the drop-down list. I have entered formulas in each column to retrieve the data from the respective worksheets as it is entered. Below is my lookup formula in K2:
=IFERROR(VLOOKUP($A2,'31Jan2019'!$C$7:$G$66,5,0),J2)
Prior to the 31Jan2019 worksheet being created, the formula correctly returns an error so the value of J2 is displayed. After the 31Jan2019 worksheet has been created and new data entered where a change is expected, cell K2 still evaluates to an error.
The formula does not calculate until I place the cursor in each cell and select enter. I've tried Ctrl > Alt > F9 and Ctrl > Alt > Shift > F9 and neither work. The workbook is set to calculate formulas automatically.
What do I need to do to get the vlookup formula to calculate automatically? Is there a line of code I can add when I create the new worksheet to activate or calculate the formula in the Summary worksheet for all cells?
Again, thanks for your assistance.
I have created a workbook with a Summary worksheet and a macro to create worksheets that are named with a Date (e.g., 27Jan2019). Dates are selected from a drop-down list and the macro is executed to create the worksheets as needed. Data is entered in the worksheet after the new worksheet has been created.
The Summary worksheet has column headers labeled with each of the dates listed in the drop-down list. I have entered formulas in each column to retrieve the data from the respective worksheets as it is entered. Below is my lookup formula in K2:
=IFERROR(VLOOKUP($A2,'31Jan2019'!$C$7:$G$66,5,0),J2)
Prior to the 31Jan2019 worksheet being created, the formula correctly returns an error so the value of J2 is displayed. After the 31Jan2019 worksheet has been created and new data entered where a change is expected, cell K2 still evaluates to an error.
The formula does not calculate until I place the cursor in each cell and select enter. I've tried Ctrl > Alt > F9 and Ctrl > Alt > Shift > F9 and neither work. The workbook is set to calculate formulas automatically.
What do I need to do to get the vlookup formula to calculate automatically? Is there a line of code I can add when I create the new worksheet to activate or calculate the formula in the Summary worksheet for all cells?
Again, thanks for your assistance.
