Forum Discussion

lauradelfosse's avatar
lauradelfosse
Copper Contributor
Jul 18, 2024

Index or Lookup? Subtraction and conditional formatting.

Hi!   I would like to automatically calculate the remaining inventory based on the selected product and the quantity entered. The inventory data is located in a separate table. For example: C3: ...
excel
Formulas and Functions
office 365
lauradelfosse's avatar
lauradelfosse
Copper Contributor
Jul 18, 2024

m_tarler both the first and your formula don't work either. I'm not sure if it's because the data is coming from a table or having the data validation active for one of the columns.

I sadly can't attacht a document here and due to policies cannot share the link, but I can provide screenshots, if that can help... I do in fact have Microsoft 365 for work. The Excel-version is 2406.

 

The dropdown-list comes from data validation on column J (Column I to K is a table).

This is the error I receive after trying the formula:

Roughly translated - "Problem found with this formula. Don't want to ype formula? If first item is = or -, Excel considers it as a formula,...." No extra information on why the formula contains an error. Same with the formulas from ChatGPT.

 

Thanks in advance again, hope this works without a link. If needed I can check regarding policies if there's another way.

 

 

m_tarler's avatar
m_tarler
Bronze Contributor
Jul 18, 2024
I see your from another country so my guess is that your settings use ";" instead of "," for the separator. try:
=XLOOKUP(C3; $J$2:$J$24; $I$2:$I$24; 0) - D3

  • lauradelfosse's avatar
    lauradelfosse
    Copper Contributor
    Jul 18, 2024

    m_tarler 

    Thank you, it works!

    I just have an error in regards to the value in colmn E3 now:

    Fault: Invalid name

     

     

     

Resources