Running Balance Formula for Filtered worksheet

[CautionMP]

Short story: Does anyone know how to write the formula for and worksheet cell that adjusts when a filter is applied. For the sake of discussion lets say I'm calculating a loan balance (well that is exactly what I'm claculating) that is based on the previous balance [R-1] plus current activity [C-1] that adjusts when a filter is applied to the sheet.

I'm using Excel 2000.

Long Story: I'm turning Excel into a database because my users need a database and don't know how to 'use' Access. Pivot Tables are out because my users don't know how to 'use' pivot tables either.

I am developmentally constipated and just plain tired of this project, I did scan the forum for an answer but did not see anything that related to this particular request.

Thanks in advance,
CMP

 

[SkipVought]

Hi,

PivotTables are EXACTLY what you need, even if your users can't spell it!

YOU, CMP, YOU...

set up the pivot tables, probably with page fields and let the PT calculate the results. You could make the PT more friendly with your own drop down, the results of which you could set the CurrentPage property of the PT.

A PT is just a report!

But, geez, don't reinvent the wheel!

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[CautionMP]

Skip,
Thanks, what I need is a database but that has been ruled out.

I have had the PivotTable discussion, the PivotTable argument, the 'this would be much easier if I could use a' PivotTable plea and no dice.

No crap, if the end users see the PivotTable toolbar pop-up they are shaken so badly they have to go home early.

Anyone else got an idea?

CMP

PROGRAMMING; a cure for the stupid user.

 

[SkipVought]

OK, then use your Excel Workbook as s database.

Caveat: One table per sheet, one row of heading per table, contiguous data.

On a separate sheet...

Data/Get External Data/New Database Query/Excel files -- YOUR WORKBOOK -- one of your tables, [next],[next],[next], select the EDIT QUERY option and [finish]

Now yer in the MS Query QBE query grid. Drag in the fields you want. Add other tables (sheets) and link.

Add one or more criteria -- if you need a VARIABLE criteria (ie on that you'll assign on-the-fly, put the value insid brackets like [What Customer?] the actual verbage will help you later.

File/return results to Excel.

Meanwhile, back in Excel
...now you're back on the sheet, but BEFORE your finish, you need to set the PARAMETER (like [What Customer?]) to a CELL reference on another sheet (a reference sheet) where, for instance, the Selected Customer Value would be stored. You can also check off where the query will fire when this value changes.

Post back if you need more tips.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[01Patrik]

Or, if they just want the PT functionality, toolbars etc to disappear, use a PT, but copy / paste special: values before sending them the report (perhaps remove the top three rows, etc)

Simply record those actions, and save the code in a template...

This way you can you the PT functionality, but yhey'll never even know how the report was created!

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[Gavona]

If you total a column in a list using =Sum(range) you get the same answer even if some of the data is hidden by means of filters. If however you use =subtotal(9,range). This will display the total of just the visible cells. Sounds to me as if that might just do the job for you.

Thanks,

Gavin

 

[CautionMP]

I want to thank everyone for the input they provided.

Here is what I came up with, it's ugly, but it works.

=IF($B$2<>"All",SUMIF($B$6:INDIRECT(ADDRESS(ROW(),4,4)),$B$2,$D$6:INDIRECT(ADDRESS(ROW(),4,4))),IF(ISNUMBER(F5),F5+D6,D6))

First IF() checks $B$2 for a filter value.
The INDIRECT(ADDRESS(ROW(),4,4) returns a relative reference based on the position of the current cell in the table.

Now that I have gone through the drill of getting this to work I just now starting to understand the tips listed in other threads on similar subjects.

If I had to do this again I would probably use the hidden column true/false trick and a sraight forward SumIf() or Subtotal() statement.

Thanks agian,
CMP