Displaying Current Day Inventory In Report

**evanmorris** · 12-11-2010, 02:14 PM

Hey I'm a newbie here at the excel forum and I tried to search for this answer and didn't see it, so I apologize if this has been asked before. But, here it goes...

I'm making a simple inventory spreadsheet for my yogurt shop. I have 3 different places where we have inventory (the store, storage unit, and a warehouse). Every morning we take inventory at the store and input it the info into that sheet. I would like to have a report tab that shows the current inventory (as of the morning) of each of places we have inventory.

The problem is that the current inventory shift one column every day. For example, we have inventory for 12/11/2010 in column H, then tomorrow we'll have inventory for 12/12/2010 in column I and it will continue for the month. So I would like for the information in the report to copy the inventory from column H on 12/11 and the column I on 12/12 and so on.

I hope that makes sense. It's kind of hard to explain in text. If you could help I'd really appreciate it.

**Blake 7** · 12-11-2010, 06:14 PM

Hi Evan

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Cheers

**evanmorris** · 12-11-2010, 10:45 PM

Originally Posted by Blake 7

Hi Evan

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Cheers

Ok thanks for the help. Here is a dummy sheet. On the report tab I'd like for the values to always be the most recent day from the "December 2010" worksheet. Is there anyway to make that happen?

**skyxliner** · 12-12-2010, 12:08 AM

What you could do is Sum the total USED each month to its own column. And use sumproduct to match the description on both sheets and deduct accordinly.

Only problem with this is, with multiple items and formulas, the saving and refreshing gets out of hand.

**Blake 7** · 12-12-2010, 07:23 AM

Hiya - Yes your requirement is certainley possible -

From what I can see there will be many ways to achieve this, some longer than others and maybe invloving a helper column.

However, off the top of my head using the index, columns and mod functions together may prove the most efficient.

I'm off out christmas shopping now but will have a think about it.

cheers

**Blake 7** · 12-12-2010, 12:35 PM

Hi

Here is a dummy sheet. On the report tab I'd like for the values to always be the most recent day from the "December 2010" worksheet. Is there anyway to make that happen?

As I said previously, there are many ways of skinning this cat and they may involve the Index or Lookup functions with ROWS/COLUMNS/MOD ect which a more knowledgable forum member than I may be able to help you with. If this is what you want then you could change your thread title to index/lookup function and I am sure a guru will assist you.

However, please see the attached.

It is the simplest and quickest soultion that I could think of to solving your problem and that is a very simple pivot table > see the "Report Sheet" tab. I'm not sure if you are familiar with PT's so all you need to do each day is populate the December Tab as per usual then change to the "Report Sheet" tab an select the date you want for the report and put it in the Values box. Remember to leave it as sum > right click in table >sumerasie Data by > Sum.

To re-fresh, right click anywhere in table and click refresh.

Hope this helps