Formula in excel

Post Reply
avi
Posts: 2
Joined: Tue Jun 10, 2008 7:14 pm

Formula in excel

Post by avi »

Hi there,

any new technic to hide/not override the DBRW formula in excel. the cell need to be writeable.

Thanks a bunch,

Avi
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Formula in excel

Post by Steve Vincent »

sorry Avi not sure i understand the question, are you trying to find a way of protecting a sheet but still allowing a user to enter data to TM1 via a DBRW?
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
osteveo
Posts: 12
Joined: Fri May 16, 2008 7:03 pm
OLAP Product: TM1
Version: 9.0 SP3
Excel Version: 2003

Re: Formula in excel

Post by osteveo »

I use a couple of ways without pass word protection. I'll hide the column inwhich the dbrw formula is in, then I'll take out the row & column headers ( Under excel 2003 Tools, Options, view)so that the person doesn't really know it is missing.

My personal favorite is to format the cells format white, and put that formula out of the normal print area just reference the result of the dbrw in the viewing area. Our users try to muck up what they can see initially, but not items they can't see (maybe they don't want to take the time to trace to the formula).

Another manner is to put all your formulas on one sheet and reference the results in different sheet. Then you can pass protect the formula sheet, and subsequently hide the sheet (Under excel 2003 Format, Sheet, Hide).

The first 2 methods usually help with the casual user, but more competent excel users will see through it.

Hope this is in line with what you were asking.
avi
Posts: 2
Joined: Tue Jun 10, 2008 7:14 pm

Re: Formula in excel

Post by avi »

Thanks a bunch!!
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Formula in excel

Post by Steve Vincent »

similar question here;

i have a large sheet where there are groups of about 8 rows with diffeerent formula. there are around 50 of these groups of formula. in some cases, users will hide groups of formula with a button, and in those cases the "effect" i wish to get is to remove the DBRW formula in the hidden cells, to cut down the amount of calculating it does.

currently this works via some VBA and a formula that puts a 1 in a column for each row that is hidden. an IF statement in front of the DBRW looks at this value to either show the formula, or show nothing. works perfectly except one major thing - users can not type a value over a DBRW with an IF statement in front of it and send the data to TM1. its sending data via a different means, but i'd like to retain the send ability of the DBRW whilst also only calculating unhidden rows. The nature of the sheet would make creating the formula by VBA a nightmare, as the layout is not consistant.

Any other ways around it?
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
osteveo
Posts: 12
Joined: Fri May 16, 2008 7:03 pm
OLAP Product: TM1
Version: 9.0 SP3
Excel Version: 2003

Re: Formula in excel

Post by osteveo »

Sorry, not that I can of.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Formula in excel

Post by Steve Rowe »

Sounds like you already have an event that handles the hide and unhide event.

How about in the VBA
When the row is hidden cell.value=0 'i.e. remove the DBRW from the cell
When the row is unhidden copy formula from another section onto the recently unhidden stuff..'Put the DBRw back

Provided the relative referenceing in the DBRW is similar you should just be able to copy the DBRW around? Even if they are all different what I've done before is have a set of DBRW formulas held set as "templates" in the excel workbook, when I need to replace the DBRW I just copy it in from the template area.

HTH, Cheers Steve
Technical Director
www.infocat.co.uk
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Formula in excel

Post by Steve Vincent »

and thats the problem, the formulae are not consistant to be able to do that. It's pulling from 3 sources on different lines, but the required layout of the file means its a kind of zebra effect ( type a / type b / type c, type a / type b / type c etc) and not only that, each of the types is a little different depending on where in the sheet it is, and each section of the sheet is a varying size. i didn't want to overcomplicate the sheet as it needs to be editiable by others in my office, but they are nowhere near as happy editing VBA as i am...

Guess i'm stuck with a half way house, but i thought i'd at least ask, you never know.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Kerry372
Posts: 7
Joined: Fri Jun 13, 2008 5:51 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Weymouth, MA

Re: Formula in excel

Post by Kerry372 »

Maybe you could write some vba to do a find and replace of =d with '=d on the hidden rows. If you insert the single quote it won't calculate and then you can remove it when you want it to calculate.
Kerry Pollock
IBM Certified Devoloper - Cognos TM1
Post Reply