dannyok90
Board Regular
- Joined
- Aug 30, 2016
- Messages
- 115
All,
Could any body help me? im struggling to overcome a problem
please see the linked spreadsheer (you don't have to activate macros, they're just used for adding new rows - hopefully ill dev this into a userform interface)
We have a 'required quantity' column which is the exact amount we should hold of a particular asset.
Then we have an 'Qty at hand' column which is the quantity we have stored.
Then we have a column 'qty at suppliers/fitted'.
Both 'qty at hand' and 'qty at suppliers' do a sumifs to the asset movements sheet summing up column 'G' depending if the asset id matches and whether the assets are recorded as inwards or outwards.
back on the asset tracker tab the sumifs for both qty at hand and qty at suppliers fitted are counting as they should, but, I cant work out a way for the spreadsheet to understand that '5' out of '10' of 'asset123' are at the supplier and 10 are stored. Or '10' of '10' of 'asset123' has gone out but also 10 has come back in.
I'm thinking possibly using the required quantity column as a reference figure for adding subtracting the ins and outs.. I'm sure there is going to be some clever string of formulas to do this but I just cant get my head around it!
Thanks Guys,
Dan
https://filehost.net/ab0eb22e5ee30930
Ps Sorry for any bad spelling! the writing app on this site keeps messing up and not registering key strokes!!!
Could any body help me? im struggling to overcome a problem
please see the linked spreadsheer (you don't have to activate macros, they're just used for adding new rows - hopefully ill dev this into a userform interface)We have a 'required quantity' column which is the exact amount we should hold of a particular asset.
Then we have an 'Qty at hand' column which is the quantity we have stored.
Then we have a column 'qty at suppliers/fitted'.
Both 'qty at hand' and 'qty at suppliers' do a sumifs to the asset movements sheet summing up column 'G' depending if the asset id matches and whether the assets are recorded as inwards or outwards.
back on the asset tracker tab the sumifs for both qty at hand and qty at suppliers fitted are counting as they should, but, I cant work out a way for the spreadsheet to understand that '5' out of '10' of 'asset123' are at the supplier and 10 are stored. Or '10' of '10' of 'asset123' has gone out but also 10 has come back in.
I'm thinking possibly using the required quantity column as a reference figure for adding subtracting the ins and outs.. I'm sure there is going to be some clever string of formulas to do this but I just cant get my head around it!
Thanks Guys,
Dan
https://filehost.net/ab0eb22e5ee30930
Ps Sorry for any bad spelling! the writing app on this site keeps messing up and not registering key strokes!!!
Last edited:
