Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Sep 25, 2024

Formula Fix

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

 

  • 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
  • m_tarler's avatar
    m_tarler
    Steel Contributor
    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.
    • Carl_61's avatar
      Carl_61
      Iron Contributor
      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
    • Carl_61's avatar
      Carl_61
      Iron Contributor
      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

Resources