what's this procedure called ?

[Chris Davison]

Evening all,

What's this procedure called ?

activate B1
insert
names
define
refers to : =a1*1.25
name it "addtax"
add it

I know it's not naming a range and I don't think it's a UDF, don't they need VBA ?, what are these called ?

ta
Chris

[Jack in the UK]

Hi Chris...

Funny feel like we just spoke... remind me to email you a newsletter on UDF i have its the dogs business, A pal Dave Hawley did it, lift from his site or ill mail tommorow if you mail me you know ill forget else wise.

you are treading on the gurus world of formatting cells in UDF tyson stylie! and so UDF it is.. read the newsletter and you be a guru for sure.

PS UDF are not all VBA.. As i have told you verbally before VBA id mega cool but not always needed, Aladin and Dave will tell anyone this as will the top gurus in the Excel field.. VBA UDF are SLOOOOOOW!

In front end are fast.... but VBA willallow the really complex stuff like convertions functions cant do.. rememebr ALWAYS functions return a answer not take otr make actions... make sence! I do hope i do make some sence.

BTW for them wanting to track the work i talk of so hightly.. read it and rea reda and stick with..

see archive news letters cant recall the edition, see:

http://www.ozgrid.com.

Cheers Dave....

[Juan Pablo González]

Jack, one quick word.

A good designed UDF can kick *** on a lot of formulae... for example, try to do the FuzzyMatch challenge with formulas... it's doable, I agree, but, it will take A LOT of Excel formulas, and just one UDF !

So, they're not the Devil !!!

[Jack in the UK]

Im preplexed ......

I agree UDF is cool and fast buty not in VBA where that can be slooow! IN open play yes they are super cool, is that not what i said in my post?

I advise against UDF or VBA Custom function just as they can be slow and bloat sheet size, i have not said no good or dont use them, its my opionion... not that i am correct, just my way.

Im not arguing or disagreeing with you just dont agree, you your way me mine is that not the wonderful thing of this site diofferent opinions and ways to work?

Read my post i feel i do really agree with you comments bar why VBA if front end excel will do in UDF???

We may not agree but we will not disagree either.

[Aladin Akyurek]

On 2002-03-26 11:19, Chris Davison wrote:
Evening all,

What's this procedure called ?

activate B1
insert
names
define
refers to : =a1*1.25
name it "addtax"
add it

I know it's not naming a range and I don't think it's a UDF, don't they need VBA ?, what are these called ?

ta
Chris

Chris: The result is called a named formula. However, the mechanism that you use is the same as when you create a range name (dynamic or not). It's definitely not a UDF which are always written in VBA. But a UDF can also be written in C or LISP (which I'd like to have) provided that there are predefined hooks for C and/or LISP in Excel.

Aladin

[Chris Davison]

named formula...

thanks Aladin



My question now is that when I do this exact named formula in sheet 1, it works fine in any cell......

when I try it in sheet 2, nothing happens

looking at the named formula, it carries the =sheet1! precursor

is there any way to make it "relative" within different sheets ?

surely such a great weapon isn't confined to the sheet it was concieved on ?

many thanks
Chris

[Yogi Anand]

On 2002-03-27 12:23, Chris Davison wrote:
named formula...

thanks Aladin



My question now is that when I do this exact named formula in sheet 1, it works fine in any cell......

when I try it in sheet 2, nothing happens

looking at the named formula, it carries the =sheet1! precursor

is there any way to make it "relative" within different sheets ?

surely such a great weapon isn't confined to the sheet it was concieved on ?

many thanks
Chris

Hi Chris:
The NAMED FUNCTION, as Aladin expalined is an interesting one. You said you used the NAMED FUNCTION as SheetA1!*1.25
If it is named this way you can go to any worksheet in that workbook and key in anywhere
=Addtax ... it will result in sheet1!A1*1.25

However if you want it to read value of cell from the related worksheet, modify the NAMED FUNCTION to read as !A1*1.25

Now if you go to sheet1 and have entry 6 in in cell A1
Then keying -in anywhere in sheet1 will give you 6*1.25=7.5

However if you go to sheet2 and have 4 in cell A1
then keying-in Addtax anywhere in that sheet will now give you 4*1.25=5

soreplacing the sheet! with ! normalizes the sheet name

I apologize for the long winded description!

[Jack in the UK]

Hi Chris

Sounds like ive bben ripped apart! erm..

But

Of cause just as you would add A1 in one shhet to A2 in shhet 2, all the same.... cant recall if anchers as you call em i call em fixed are needed,, poss not try it...

Still call em UDF but thats my way and sure as you have seen call em XYZ we all know em the same.. maybe a street south london term needed if badly spelt one... UDF in open play non VBA that is... i must send you some your see what i mean.

VERY powerful..

same way we name cell ie VAT and put vat in cell and it calculates.. same idea all time saving and short cutting.

Take care mate...

[Aladin Akyurek]

On 2002-03-27 12:23, Chris Davison wrote:
named formula...

thanks Aladin

[img]/board/images/smiles/icon_smile.gif[/img]

My question now is that when I do this exact named formula in sheet 1, it works fine in any cell......

when I try it in sheet 2, nothing happens

looking at the named formula, it carries the =sheet1! precursor

is there any way to make it "relative" within different sheets ?

surely such a great weapon isn't confined to the sheet it was concieved on ?

many thanks
Chris
[img]/board/images/smiles/icon_smile.gif[/img]

Chris,

Start with an empty WB.
Activate B1 in some sheet.
Activate Insert|Name|Define.
Enter addtax as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=!A1*1.25

Click OK.

Put anywhere in any sheet a number and next to it:

=addtax

Aladin

[Chris Davison]

Yogi and Aladin,

great stuff ! thanks a million

Jack,

HIya mate - I call it "anchor" cos of the $ sign - it looks like a ship's anchor