#1
|
|||
|
|||
Consolidation or not?
I have a workbook with 25 sheets, each one relates to an item with specific contacts, information and date based sales etc.
Each Monday, when I get the new sales figures for each item, I input them individually into each sheet. What I have been trying to achieve (without success) is a "Results Sheet" with the latest weekly totals from all of the other sheets. That way, whenever I need the latest figures, I can just look at the one sheet and see all of them at a glance. As I am typing this post another idea has occurred to me. Is it possible to type the information into the one "Results Sheet" and have that populate the individual sheets on the next available row down in the column, ie "Date" and "Sales". The "Percent" and "Left" cells would be calculated by a formula in the individual sheets. That way I only need to open the one sheet from the start. Attached is a small example of my sheets (with spurious figures) Many thanks for any help/advice on this. Rob |
#2
|
||||
|
||||
Say your results table is in a sheet called "Results", with column headers starting at A2 and figures below.
Add a "helper" formula in column F at F3, =A3&"_"&COUNTIF(A$3:A3,A3) copied down as far as you want in order to capture future data. (You can hide this column) Now in the sheet for Item A, beginning at A2 enter formula: =IFERROR(INDEX(Results!B:B,MATCH("A"&"_"&ROWS($A$2 :$A2),Results!$F:$F,0)),"") copied across 4 columns and down as far as you want to capture future data. Repeat for other items replacing the "A" with the corresponding Item. Now when you type new row in Results sheet, it would populate in next row of corresponding item. |
#3
|
|||
|
|||
Quote:
I have got the top line ok but I am struggling with the second. When I put the code into the cell, I am prompted to open a new document! Is this correct? My sheets are all in the one document. Also, as there is already some data in some previous weeks cells, and my empty rows are starting in different rows on each item, does this really complicate the issue. Would it be better to go back to the start date at the end of September (Row 11) and input all of the info into the Result Sheet, one week at a time to build up the historical data. Thanks Rob |
#4
|
|||
|
|||
Take a step further!
You need a sheet, where you enter all sales information for all items. And you need a report sheet, where you select an item, and all needed info about this item is read from first sheet (really you can have any number of different report sheets with different kind of reports). |
#5
|
||||
|
||||
Quote:
Secondly, my solution is based on you starting from scratch. However, if the data in the other sheets is identical to what is in your results sheet, you should be able to clear the data from those sheets, enter my formula in each of the sheets (making them correspond to the correct item) and then the data from the results sheet should re-appear. I attached a sample from your sample. I created the Results sheet, where you enter the info.. and the item A sheet shows you the extracted "Item A" only items. Make a copy of your original workbook for backup before applying these suggestions. |
#6
|
|||
|
|||
Thanks NBVC
I have played with this a little and I'm struggling to get it to work in my file but I'll keep at it for a while. However, in your sample, when I input the new data for item A, it overwrites the first item on the list (Row 11) whereas I want it to enter the data in the first vacant row so that I can see the historical data, this could be Row 16 etc. Some items will have a history of only 3 weeks and others 9 weeks. Like this Date Sold Percent Left 14/08/2017 254 21.78 912 21/08/2017 262 22.47 904 28/08/2017 276 23.67 890 04/09/2017 285 24.44 881 11/09/2017 305 26.16 861 18/09/2017 335 28.73 831 25/09/2017 384 32.93 782 06/11/2017 611 52.40 555 13/11/2017 634 54.37 532 Rob |
#7
|
||||
|
||||
As soon as you type an "A" in column A, row 11 of "Results" sheet, you should see column F show "A_4". This means this is the 4th occurance of "Item A", and therefore it should add a 4th row to the Item A sheet. It should not overwrite anything.
My sample is assuming you are adding new rows in the results sheet (even for same Item). It does not assume you are overwriting previous information. |
#8
|
|||
|
|||
Quote:
If this cannot be done, is it better to input the new data in it's own sheet and have the result sheet pull that data into the correct cells. At the moment, if I want to send out the sales information, I have to look into each items sheet to find the latest numbers then copy and paste each one into another table. Rob |
#9
|
||||
|
||||
Quote:
Quote:
Formula in the Results sheet, starting at B3: =IFERROR(INDEX(A!A:A,MATCH(10^10,INDIRECT("'"&$A3&"'!$A:$A"))),"") copied down and across as far as necessary. Note, this requires the item sheetnames to match exactly your list in column A, so that you don't have to hard code the sheetname in each row of formulas. |
#10
|
|||
|
|||
Quote:
Sorry to be a nuisance but which bit of the formula (which A) do I need to replace with the name of the sheet? Thanks again Rob |
#11
|
||||
|
||||
The one I highlighted in red. The In your sample A3 contains an "A". If you have a sheet named "A", the formula will indirectly reference that sheet so that it get the info required. As you drag the formula down, it changes to A4 and therefore it should get the info from sheet named "B", and so on...
|
#12
|
|||
|
|||
Quote:
My first sheet is named Pen and the 2nd is named Pencil. I have tried changing the $A3 in to both of the names but cannot get it to work and cannot work out what I am doing wrong. The columns I am trying to read the info from are A B C & D and the input columns on the result sheet are B C D & E. Does this make a difference? Thanks,. Rob |
#13
|
||||
|
||||
can you attach the workbook? (remove confidential info)
|
#14
|
|||
|
|||
Attached is a very stripped down version of what I am trying to achieve.
Many thanks for your help. Rob |
#15
|
||||
|
||||
I realized when I entered the formula in your sheet that I screwed up the formula. The index part should be indirectly referenced too....
=IFERROR(INDEX(INDIRECT("'"&$A3&"'!A:A"),MATCH(10^ 10,INDIRECT("'"&$A3&"'!$A:$A"))),"") copied down repeat for each column changing the A:A in the INDEX part to respective column in other sheet. You may need to format the results to match the type of data you are pulling. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Consolidation of multiple sheets in Pivot | Kelly Mathews | Excel | 1 | 10-15-2016 04:19 AM |
Excel Consolidation Problem | youngsc | Excel | 1 | 03-29-2010 06:49 AM |