Hello, Good morning from Texas,
I wanted to ask for some help as I have spent hours searching the web trying to figure a formula and how to get this started. I have questions on how to calculate monthly storage for inventory being stored at a certain rate. But I would require the sheet to be fed from a main sheet in the same workbook. Let me try my best to explain this:
Sheet 1 (main datasheet)
Client, side mark, date received in, date released, item, measurements, daily storage, monthly storage, along with some other columns that have data needed for client purposes.
Sheet 2 (storage)
I would like to feed sheet 2 with some selected columns from sheet 1 (which I can do that by linking the columns and know how to do that)
The main sheet will be updated at any given time. Items were received on October 15th and at the end of the month, we charge for the number of days it was in the warehouse for October. When "Main Sheet" feeds "Storage Sheet" the storage will start from Oct 15th and end on October 31st. That seems simple enough for that month and we know how to get the data that far. Here is the issue and struggle, now November storage starts and items start arriving in November and we updated "Main Sheet" for November. Items came in on Nov. 2nd and the "Storage sheet" is updated. Items come in on Nov. 21st and so on. So now "Storage Sheet" will be updated with November items along with October items from "Main Sheet" But we need the "Storage Sheet" to calculate for the same items that came in in October but only charge from Nov. 1st to Nov. 30th. The formula inputted will show how many days it has been in storage and will charge from Oct. 15th to Nov. 30th.
Lastly, if some items leave the warehouse mid-month on the main sheet and we have a date release date inputted, the storage sheet will calculate the right amount of days if items came in on the current billing month and left in the same billing month. We will run into the issue above if we begin storage for the month of Dec. and a some of the items left in Nov. it will still show up in Storage sheet since it is being fed from Main Sheet and we cannot delete data on the main sheet since we will always need to keep record of all inventory. So we will want the storage sheet to no longer calculate storage if the item left the warehouse month(s) before and we don't know how to make a formula for that.
I understand that this is a lot and since I don't know much about excel except youtube and forums like on this site, I don't know if there is a simple formula for this or if this is too complicated. If this is a level of I am asking for too much, please let me know and I will understand, thanks.
I wanted to ask for some help as I have spent hours searching the web trying to figure a formula and how to get this started. I have questions on how to calculate monthly storage for inventory being stored at a certain rate. But I would require the sheet to be fed from a main sheet in the same workbook. Let me try my best to explain this:
Sheet 1 (main datasheet)
Client, side mark, date received in, date released, item, measurements, daily storage, monthly storage, along with some other columns that have data needed for client purposes.
Sheet 2 (storage)
I would like to feed sheet 2 with some selected columns from sheet 1 (which I can do that by linking the columns and know how to do that)
The main sheet will be updated at any given time. Items were received on October 15th and at the end of the month, we charge for the number of days it was in the warehouse for October. When "Main Sheet" feeds "Storage Sheet" the storage will start from Oct 15th and end on October 31st. That seems simple enough for that month and we know how to get the data that far. Here is the issue and struggle, now November storage starts and items start arriving in November and we updated "Main Sheet" for November. Items came in on Nov. 2nd and the "Storage sheet" is updated. Items come in on Nov. 21st and so on. So now "Storage Sheet" will be updated with November items along with October items from "Main Sheet" But we need the "Storage Sheet" to calculate for the same items that came in in October but only charge from Nov. 1st to Nov. 30th. The formula inputted will show how many days it has been in storage and will charge from Oct. 15th to Nov. 30th.
Lastly, if some items leave the warehouse mid-month on the main sheet and we have a date release date inputted, the storage sheet will calculate the right amount of days if items came in on the current billing month and left in the same billing month. We will run into the issue above if we begin storage for the month of Dec. and a some of the items left in Nov. it will still show up in Storage sheet since it is being fed from Main Sheet and we cannot delete data on the main sheet since we will always need to keep record of all inventory. So we will want the storage sheet to no longer calculate storage if the item left the warehouse month(s) before and we don't know how to make a formula for that.
I understand that this is a lot and since I don't know much about excel except youtube and forums like on this site, I don't know if there is a simple formula for this or if this is too complicated. If this is a level of I am asking for too much, please let me know and I will understand, thanks.