Protected sheet killing VLOOKUP from updating

Hello,

First post, still learning excel. Running Excel 2024 on MacOS. My issue is I have two sheets, First sheet labeled "Calculator", Has a drop down list that pulls a list of products from a Price/Quantity table on the second sheet labeled "Transfers". Depending on what product is selected in the drop down list (B3), and what Quantity is entered in (G3) cells (E3, F3, and I3) populate with the data attached to that product. Everything works if the sheets are not protected. When I protect the sheets, the VLOOKUP and INDEX formulas located in (E3, F3, I3) are not dynamic and do not register any new products added to the table on "Transfer" sheet.

CELL E3 Contains:

Please Login or Register  to view this content.

CELL F3 Contains:

Please Login or Register  to view this content.

CELL I3 Contains:

Please Login or Register  to view this content.

So my drop down list is still dynamic, but those 3 cells are not when the sheet is protected.

I attached an example file. So basically to test, you would add a new row to the table on "Transfer" Sheet. Enter Product data in Columns A,B,C,D. And when you select the new entry from the drop down list, E3, F3, and I3 updates correctly.

Thanks,
Joey

Click on Review tab when you open this excel file then click on Unprotect Sheet, hope that solve the problem

Thanks for the reply. I guess I should have stated that I want the sheets to be protected so the formulas in CELLS E3, F3, and I3 don't get erased. The only cells I have unlocked are the cells that require input.

Oh I guess I never noticed. It's 2021, newest version 16.81. I tried making the change suggested by AiGW, still same result for me. Did you try entering a new product on Transfer sheet and then selecting it from the B3 drop down list? The formulas in CELL E3, F3, I3 are static when sheet protection is turned on. So if you were to enter a new entry on Transfer sheet. CELLS E3, F3, and I3 do not update with correct table range on Transfer sheet.

CELL E3 didn't update to:

Please Login or Register  to view this content.

CELL F3 didn't update to:

Please Login or Register  to view this content.

CELL I3 didn't update to:

Please Login or Register  to view this content.

Does excel kill the dynamic functionality of the function when the cell that has those particular functions inside them? I double checked my settings to make sure automatic calculations are enabled. All other formulas in locked cells function correctly when my sheet is protected These are the only cells having this issue.

Thanks for the help
Joey

AiGW, Thank you! I cannot believe how simple the fix was. I just always assumed I needed to use the exact range of the table and excel would scale the formula accordingly just like it does when the sheet is unprotected. Thank you that fixes my issue. Do you have any light on why these particular formulas do not scale dynamically when the sheet is protected?

Thanks again,
Joey

SOLUTION FOUND