Dynamic Sum from One sheet to another

[Topcat04]

Hi All

Im trying to get the following formula to work across sheets without any luck.

=SUM(BB5INDIRECT(ADDRESS(ROW(),COLUMN(OFFSET($B$4,2,MATCH(Summary!$B$2,$B$4:$DA$4,1)))))))

This works fine when its located on the same sheet as the data (sheetname is UnitSales)

I want to use it on a Summary Sheet and tried changing the Formula to the below - but it returns an error.

=SUM(UnitSales!BB5INDIRECT(ADDRESS(ROW(),COLUMN(OFFSET(UnitSales!$B$4,2,MATCH(Summary!$B$2,UnitSales!$B$4:UnitSales!$DA$4,1)))))))

Your help to be able to get this to work on a Summary Sheet is appreciated,

 

[SkipVought]

Hi,

You may be making this too complicated.

Can you post a sample of what's on UnitSales?

Can you explain how you want this summarized not in terms of an Excel formula but functionally?

 

[Topcat04]

Hi Skip
Im trying to Sum up YTD sales to the required date that is populated on the summary sheet in cell B2.

Snippet of the sheet below starting in Column A and Row 4 (which has Dates along row 4, but they didn't come out very well in the sample below so I removed them)

GM - 302962 725 319 294 232
GM - 345407
GM - 303056 1073 373 148 124
GM - 302958 1174 681 733 573
GM - 303054 1220 544 265 215
GM - 342436 0 146 356 332
GM - 255656 448 160 207 235

I need it to be dynamic so that the date can be manipulated in the summary sheet and get the appropriate qty reported back.
Thanks for your help

 

[Andrzejek]

FYI - to PREserve spacing in your example, highlight the text and hit Pre tag

[pre]
GM - 302962 725 319 294 232
GM - 345407
GM - 303056 1073 373 148 124
GM - 302958 1174 681 733 573
GM - 303054 1220 544 265 215
GM - 342436 0 146 356 332
GM - 255656 448 160 207 235

[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Well the dates in row 4 would be pretty important!

So using that example, what would the summary look like?

 

[Topcat04]

Skip
this is the preserved snippet although the dates still are not lined up with the figures....but you get the idea.
[pre] 03-Jan-15 10-Jan-15 17-Jan-15 24-Jan-15
GM - 302962 725 319 294 232
GM - 345407
GM - 303056 1073 373 148 124
GM - 302958 1174 681 733 573
GM - 303054 1220 544 265 215
GM - 342436 0 146 356 332
GM - 255656 448 160 207 235
[/pre]

On the Summary page, literally at the moment is only the Date in Cell B2
which is 24/01/2015
As I note, it works fine on the sheet that contains the data, just not able to reference the sheet from another sheet and still get it to work correctly as it results in a #Value error.
Thanks for your support

 

[Topcat04]

Any help on why this formula will not work across sheets will be much appreciated.
Thanks.

 

[SkipVought]

I have to wait until I get to my laptop.

Just out of curiosity, are you returning one sum for each row from UnitSales?

 

[Topcat04]

Skip
I believe the answer to your question is yes...
Appreciate your help,

 

[SkipVought]

So those GM - numbers are not on your Summary sheet? Just a sum on each line???

 

[SkipVought]

So I'm going to assume that the GM - numbers are on each row in column A. NOT having those key values on the Summary sheet does not make sense.

So I NAMED the following ranges in the UnitSales sheet.

1) the GM - range: rUnit
2) the date range: rDates
3) the DATA range: rData

And the formula:
[tt]
=SUMPRODUCT((rDates>=$B$1)*(rUnits=$A2)*(rData))
[/tt]

And the results:
[pre]
Unit 1/24/2015
GM - 302962 232
GM - 345407 0
GM - 303056 124
GM - 302958 573
GM - 303054 215
GM - 342436 332
GM - 255656 235

[/pre]

 

[Topcat04]

Those GM numbers are on the unit sales....
The GM Numbers are the product codes.... the 4 columns of numbers are the unit sales (these are just a snippet as they are weekly sales figures)
there are currently 61 columns of sales (2014 and 9 weeks this year) but I want to dynamically sum up the current year to date numbers which the formula above does if on the same sheet as the data.
Hope that helps,

 

[SkipVought]

FYI,

You're doing an aggregation from an aggregation. Thats a bad idea!

Do you have access to the data that was used to generate UnitSales? THAT is what you ought to be using. Otherwise you will run into problems, and in this case, we had to do some kinda tricky range analysis.

You original approch, was really ugly, clumsy and a mainenance nightmare.

 

[Topcat04]

Skip
your second post came in as I was posting my response above - apologies.

I think I have confused you with the notation of the 'Summary Sheet' - that's going to be used for something different, its just currently where the 'control date' is situated.
Im going to give this formula a go and see if it gives me what I need...thanks for coming back to me. Your help is much appreciated.

 

[SkipVought]

Well you NEVER showed an example of the expected results!

So I had to GUESS.

Please get specific!

 

[Topcat04]

Thanks Skip
I do appreciate your help!
Ill post back again tomorrow - (its late here now and I have an early start)

 

[SkipVought]

I'm guessing again, so if ALL you have inn each row is a TOTAL...
[tt]
=SUMPRODUCT((rDates<=$B$1)*(rUnits=UnitSales!$A2)*(rData))
[/tt]

BTW, I had my equality backwards. Sorry.

 

[SkipVought]

Okay.

Prime example of 1) ignoring comments: Not posting clarifying data and 2) assuming a solution/method rather than posting cogent examples and explaining the requirements rather than HOW you think it ought to be done.

If this is NOT a summary sheet, then all the more need for an example of your expected results.

Again, if at all possible, you ought to be using non-aggregated source data to "summarize." Aggregating an aggregation is a problem and leads to further problems.

 

[Topcat04]

Skip
I have taken snippets of the spreadsheets and hope that this will help explain.
I have 3 main sheets at the moment
Link
Master - this has the 'source' data in it. This is pre formatted and provided as is from another source - so without touching and editing that data regularly which I don't want to have to do, Im stuck with that format.
UnitSales - this is where I collate just the weekly sales and are shown for the whole lifetime week by week.
12WeekSales - this is where the 'presentation data' is used and will be able to provide further analysis, such as Average weekly sales, YTD sales etc...but first is to be able to get the YTD sales formula working correctly before I can do the other calculations, as I expect many of them will just require a tweak from this one. So YTD is as it states, only summing from 03 Jan - 28 Feb currently but increasing week by week.

I hope this is more informative and is providing you with more of an insight into what Im trying to Achieve.
appreciate your support!

 

[zelgar]

I only see 2 image files, UnitSales and 12WeekSales. You didn't include the Master sheet.

If your Master sheet has daily data, I wouldn't be messing around with 3 different spreadsheets. Since it sounds like you don't want to mess around with the Master Sheet, I'd have only 1 other sheet that you'd use with a macro to do the following:
1. Copy the everything or only the "new" information from the Master Sheet to your presentation sheet.
2. In the presentation Sheet, create the quarterly reports
3. For getting information about information from the other sheets, either have a link to the other page or have the macro just put the data in the cell.

Sometimes using a macro is a WHOLE LOT EASIER than trying to manipulate a formula to do the work.