Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-15-2017, 04:13 AM
hollies hollies is offline Consolidation or not? Windows 7 64bit Consolidation or not? Office 2016
Novice
Consolidation or not?
 
Join Date: Jun 2017
Location: Nottinghamshire
Posts: 25
hollies is on a distinguished road
Default 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
Attached Files
File Type: xlsx Latest Sales Figures..xlsx (12.7 KB, 8 views)
Reply With Quote
  #2  
Old 11-15-2017, 06:07 AM
NBVC's Avatar
NBVC NBVC is offline Consolidation or not? Windows 10 Consolidation or not? Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 11-15-2017, 07:03 AM
hollies hollies is offline Consolidation or not? Windows 7 64bit Consolidation or not? Office 2016
Novice
Consolidation or not?
 
Join Date: Jun 2017
Location: Nottinghamshire
Posts: 25
hollies is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
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.
Many thanks for your swift answer NBVC.

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
Reply With Quote
  #4  
Old 11-15-2017, 07:14 AM
ArviLaanemets ArviLaanemets is offline Consolidation or not? Windows 8 Consolidation or not? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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).
Reply With Quote
  #5  
Old 11-15-2017, 09:03 AM
NBVC's Avatar
NBVC NBVC is offline Consolidation or not? Windows 10 Consolidation or not? Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Quote:
When I put the code into the cell, I am prompted to open a new document! Is this correct?
This is happening because you don't have a sheet currently in your workbook called "Results". You would need to update the formula to point to the sheet that you entered my first formula in.

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.
Attached Files
File Type: xlsx Copy of Latest Sales Figures..xlsx (26.9 KB, 9 views)
Reply With Quote
  #6  
Old 11-15-2017, 09:33 AM
hollies hollies is offline Consolidation or not? Windows 7 64bit Consolidation or not? Office 2016
Novice
Consolidation or not?
 
Join Date: Jun 2017
Location: Nottinghamshire
Posts: 25
hollies is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 11-15-2017, 10:00 AM
NBVC's Avatar
NBVC NBVC is offline Consolidation or not? Windows 10 Consolidation or not? Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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.
Reply With Quote
  #8  
Old 11-15-2017, 10:07 AM
hollies hollies is offline Consolidation or not? Windows 7 64bit Consolidation or not? Office 2016
Novice
Consolidation or not?
 
Join Date: Jun 2017
Location: Nottinghamshire
Posts: 25
hollies is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
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.
Sorry no, I just want the result sheet to show the latest set of figures and as each week passes, I want to input that weeks data against Item A and for it to show on a new Row in the item sheet.

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
Reply With Quote
  #9  
Old 11-15-2017, 11:18 AM
NBVC's Avatar
NBVC NBVC is offline Consolidation or not? Windows 10 Consolidation or not? Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Quote:
I just want the result sheet to show the latest set of figures and as each week passes, I want to input that weeks data against Item A and for it to show on a new Row in the item sheet.
That would require VBA.

Quote:
input the new data in it's own sheet and have the result sheet pull that data into the correct cells
That can be done with formulas. See attached new sample.

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.
Attached Files
File Type: xlsx Copy of Latest Sales Figures..xlsx (29.1 KB, 8 views)
Reply With Quote
  #10  
Old 11-15-2017, 03:34 PM
hollies hollies is offline Consolidation or not? Windows 7 64bit Consolidation or not? Office 2016
Novice
Consolidation or not?
 
Join Date: Jun 2017
Location: Nottinghamshire
Posts: 25
hollies is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
That would require VBA.



That can be done with formulas. See attached new sample.

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.
Wow My mind is really whirring now.

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
Reply With Quote
  #11  
Old 11-16-2017, 06:03 AM
NBVC's Avatar
NBVC NBVC is offline Consolidation or not? Windows 10 Consolidation or not? Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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...
Reply With Quote
  #12  
Old 11-20-2017, 05:16 AM
hollies hollies is offline Consolidation or not? Windows 7 64bit Consolidation or not? Office 2016
Novice
Consolidation or not?
 
Join Date: Jun 2017
Location: Nottinghamshire
Posts: 25
hollies is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
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...
My apologies for not responding sooner, NBVC, but I have been out of action for some time.

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
Reply With Quote
  #13  
Old 11-20-2017, 05:45 AM
NBVC's Avatar
NBVC NBVC is offline Consolidation or not? Windows 10 Consolidation or not? Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

can you attach the workbook? (remove confidential info)
Reply With Quote
  #14  
Old 11-20-2017, 01:33 PM
hollies hollies is offline Consolidation or not? Windows 7 64bit Consolidation or not? Office 2016
Novice
Consolidation or not?
 
Join Date: Jun 2017
Location: Nottinghamshire
Posts: 25
hollies is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
can you attach the workbook? (remove confidential info)
Attached is a very stripped down version of what I am trying to achieve.

Many thanks for your help.

Rob
Attached Files
File Type: xlsx test sales 2018.xlsx (35.8 KB, 7 views)
Reply With Quote
  #15  
Old 11-21-2017, 08:04 AM
NBVC's Avatar
NBVC NBVC is offline Consolidation or not? Windows 10 Consolidation or not? Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consolidation or not? Consolidation of multiple sheets in Pivot Kelly Mathews Excel 1 10-15-2016 04:19 AM
Consolidation or not? Excel Consolidation Problem youngsc Excel 1 03-29-2010 06:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:57 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft