what's this procedure called ?

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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....
 
Upvote 0
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 !!! :biggrin:
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
:)
 
Upvote 0
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!
 
Upvote 0
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...
 
Upvote 0
On 2002-03-27 12:23, Chris Davison wrote:
named formula...

thanks Aladin

/board/images/smiles/icon_smile.gif

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
/board/images/smiles/icon_smile.gif

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
 
Upvote 0
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

:wink:
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top