Summing across unknown range in unknown cell!

Hey folks,

I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!

I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.

Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.

Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?

Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).

Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!

Many thanks!
AR

Hey thanks for the reply. I think a pivot table might work but I've never programmed one using a macro before. At which point would it be created/updated? Would I then pull the value out of the pivot table and store it in my separate excel sheet?

It's something to think about but I'm not sure how difficult it would be... I'd like everything to be automatic, i.e. requiring no human interaction at all after entering initial values.

You can set the Pivot Table data source to be a Dynamic Named Range - (ie one that adapts as you add/remove data to your source sheet) - for info. on creating such a range see the link in my sig. to Contextures tutorial.

You can as part of the User Form routine refresh the PT once the data has been added - thus no action on part of end-user is required other than entering the data as is presently the case.

Originally Posted by agentred

Will this summary row cause a problem in the pivot table?

Given the summary row can be distinguished from the non-summary rows by means of various columns - eg Commodity is blank - you can filter out these rows quite easily.

Will missed dates... ?

No. In fact this lack of functionality is often a gripe for people with PT's as users often "want" to see data for each day even if it does not exist (ie the opposite of yourself) ... without "dummy data" for the missing days at source this isn't really possible with a Pivot Table - it will only really display that which exists @ source.

Yes you will need to use VBA but I thought from earlier posts this was already the case (user form) ?

If you are using VBA userform for data entry to update the Table then I would suggest coding a refresh as part of that routine else you are looking at using the Worksheet_Change event, eg:

Please Login or Register  to view this content.

Change that in red to reflect

a) name of your Table
b) name of the sheet on which PT resides
c) name of the PT itself

To insert the above, right click on the sheet containing your Table, select View Code and paste above into resulting window thereafter ensuring macros are enabled.

The above may not work for you 100% but in that case we would really need to see the proposed layout etc of both data & PT before being able to comment / advise further.

Alright excellent!

Thank you again for the advice, I've managed to create a table and pivot table which auto update as necessary.

BUT (and there's always a but) I need to do calculations with the results of the pivot table and have the results updated in the same row. So for instance,

Row Date Trades Profit Net Profit Total Capital
1 1/1/2009 7 $70 (Profit - Trades*0.02) $50 + profit
2 1/2/2009 6 $80 (Profit - trades*0.02 ) Total capital from 1/1/2009+profit

So the first three columns in blue are from my pivot table, and they update automatically from the data. But how do I get the next two columns to work? Any ideas? Is it even possible? Because net profit references the value above as well as the value from a pivot table field.

What I've thought of doing is creating another macro, which is semi-automatic, that is, you click on the net profit cell and run the macro. It then uses offsets to calculate net profit and Total capital. I'm currently writing that up, but is there an easier way?

I mean, thanks to all your help I've come this far, I can probably calculate the last couple fields by hand if I have to but... to see perfection just inches away from my grasp...!

Have attached a demo sheet in case the table above doesn't make things very clear... ...And I just realised that a 2007 pivot table isn't compatible with excel 2003 sheets so I hope nobody minds the sheet being an xlsm

You can add Calculated Items etc to Pivot Tables but they can get quite convoluted quite quickly and also lead to undesired layouts.
Therefore I'd probably err on your side and opt to add the calculations on the fly as part of the routine though I would add reverse the position of the Total Capital and Return on Capital columns (such that Return looks to left for Total Capital rather than to the right).

In basic terms... this would be a revised PT Update routine to insert the formulae etc - it assumes that the layout of the PT is rigid horizontally (not vertically of course)

Please Login or Register  to view this content.

If you expect the PT to contract vertically as well as expand you would need to add some code to remove surplus (legacy) calculations.

Apologies, I overlooked that when I was setting up, on which basis perhaps simplest to revise to:

Please Login or Register  to view this content.

Also if you don't want calcs for the Grand Total rows you can resize the range

Please Login or Register  to view this content.

i.e. RC[-1,-1] would mean : minus 1 row and minus 1 column to find the relative number ...

I think you mean R[-1]C[-1]....

agentred, for a very brief overview of R1C1 notation see a very basic outline I posted here: http://www.excelforum.com/2171545-post2.html
(there are plenty of better, more thorough resources on the net if you wish to google etc...)

Good. Please remember to mark thread as solved.

Incidentally...

HTH is forum speak for Hope That Helps ... also note the R1C1 syntax you used was incorrect on which basis I added a link for you to review at your leisure.

The code provided works without issue on your sample file so I'm afraid you will need to upload a further example in which it is failing... I would expect a #VALUE! error only if certain referenced cells are generating text rather than numbers (ie coercion error).

You're not using the formula I gave you... ??

The formula provided previously will take the current Total Capital G41 and subtract from that the current P/L C41 -> that value is equiv. to your opening Total Capital balance.

The reason I provided that approach was for this very reason !
(ie your opening balance is not available on the row prior to first transaction so for consistency calculate Opening balance by taking End Balance less Periodic Movement)


(i should add the reason I was confused it wasn't working was because in post # 19 you said you had modified it but then repeated the formula I had given you which should have worked - ie you did not detail the formula used above (which won't))

I notice you may have multiple sheets and it looks like you are intending to repeat the code for each sheet ... given the layouts of each sheet are

a) seemingly identical (columns specifically)
b) table names are consistent and match sheet names (TradesTablemmm)
c) pivot table names are consistent and match sheet names (SummaryPivotmmm)

(by sheet name consistency I mean the sheets too are named using mmm construct and therefore you can from sheet name determine both the appropriate Table range & Pivot Table for that sheet)

then you should consider using the Workbook level Sheet Change event and thus have only routine which will be applied to all monthly sheets.

The below, pasted into ThisWorkbook, would replace all code in both Oct & Nov sheets (ie that code should be removed in full)

Please Login or Register  to view this content.