I am writing an inventory list for my office, which is combined with a borrow list. What I want to do, is change the number of items in the inventory list, once an item has been borrowed. For instance, if I have 2 hard drives listed on stock, I want the spreadsheet to automatically decrement that value, when I enter that data in the borrow list. Any ideas how can I do that?

  • 1
    Do you really, really need to change the value in the spreadsheet, or is it good enough to display the adjusted value?  Hint: the latter is much easier. – Scott Nov 17 '15 at 11:36
  • I'm not really sure which one will be better, to be honest. I will edit the post with a link to the file, because I have the feeling I haven't asked my question properly. – Nikolay Panev Nov 17 '15 at 21:51
  • People are reluctant to download and open files from unknown people because of the risk of malware.  It would be better if you would just describe your problem more clearly.  It might help if you type in a textual representation of your spreadsheet, as was done here and here, reducing the data to the bare minimum necessary to illustrate/illuminate your question.  And if you don’t need to change the value in the spreadsheet, I suggest that you focus on finding a way to display the adjusted value. – Scott Nov 17 '15 at 22:07

It sounds like the COUNTIF() function would be useful. You can use it to count how many cells in a range match some value. Say an inventory ID is in A1, borrowed item IDs are in G1:G20, and you want B1 to say how many of A1 were borrowed. I don't have Excel on this machine, but something like this in B1:

  • This looks like its gonna help, but does this function also change the value of A1? I'll try to upload a mockup file of what I have done till now, To clarify better on what im trying to achieve. – Nikolay Panev Nov 17 '15 at 10:05
  • No, this wouldn't affect A1 in any way. Also, I've had a chance to check this in Excel since then, and it looks right. Note the four $s in the range, making it absolute so it doesn't change when you copy the formula to other cells. – Tom Zych Nov 17 '15 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.