Formula Fix

[Carl_61]

@m_tarler 

Formula Change/Fix

Key Log Fix

The formula worked perfectly fine until I added 2 columns.  I'm just trying to adjust 2 numbers but the formula will not let me.  This may be a data validation issue but I am not sure.  Here is the formula:  =INDEX('Hard Keys Inventory'!$A:$AA,XMATCH(LEFT([@Room],3),LEFT('Hard Keys Inventory'!$A:$A,3)),SWITCH(MID([@Room],4,1),"",3,"A",11,"B",19)).  The 2 numbers I needs changed are 11 & 19 at the end of the formula.  The 11 needs to be changed to 12 and the 19 needs to be changed to 21 I believe.  When I change the numbers I get the following message "This field is calculated using a formula and should not be entered manually. Please click 'CANCEL'". This message is an Error Alert in Data Validation with a Style of Stop.  I just need to know how to fix this issue.

Would you please help me out here?

Carl

[#2]

Hi Carl, so sry I see you left me a bunch of PMs and because the systems doesn't prompt me anymore I haven't seen them.
I remember that 'error' message was part of the data validation we applied. You will need to turn off the data validation to make the change (or you can trick it / bypass it by copying the formula and then just paste it onto the cell). I can try to look into your PMs and this specific issue later (maybe over my lunch time) and see if I can give you more detailed info.

[#3]

Thank you Matt. The one thing I forgot to ask was, does this Index Function auto fill the column like the =LET Function or does it have to be drug down?

Carl

[#4 · Accepted solution]

Thanks Matt,

Figured that out. Changed style to Information and it let me change it. I then changed it back to Stop. Works fine now. You give me inspiration thru your replies.

Carl