Forum Discussion
Formula 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
- 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
- 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.- 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 - 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
