Cross sheet column subtraction

Apoorva

I have two sheets where I am tracking inventory, one sheet contains data for material available, and the other for material used, given that not everyone has access to all sheets and I want to create a report where I can display the material available ( basically the difference), Is there a way to do a cross sheet column subtraction without linking cells? and even if i have to link cells can I link an entire column so that it auto updates whenever someone updates and adds a row?

Michael Culley

Do all inventory items have a unique product number or something along those lines?

You could do it a few ways but one way is to create a new column on your "material available" sheet where it pulls in the number of materials used. Then another column that subtracts them.

Does that sound like what you're looking for?

Apoorva

Hi Michael, yes they have their own lot numbers. Can I pull in an entire column in a way that I don't have to manually keep updating it and pulling in the new rows?

isabonita

I would use a Sheet Summary field that has a JOIN COLLECT formula (you would set up a cross sheet reference to the column in your material available) and then use a CHAR(10) (which is the newline) to put them into a list.

From there you can put those items into rows in the sheet (there are a couple of ways to do this) and then do INDEX MATCH formulas to look up the amounts for each lot number from each of your sheets for the report... if that seems like something that would work... I can give you some more details tomorrow.

Andrée Starå

Hi @Apoorva

I hope you're well and safe!

Here are a couple of excellent templates that can be great starting points.

https://www.smartsheet.com/marketplace/search#q=inventory&sort=relevancy&numberOfResults=25&f:@app_type=%5BApplication,Template,Template-Set%5D&f:@language=%5BEnglish%5D

Would any of those options work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!