Function instead of formula
 

Hi All,
I have a 2 sheets workbook.
One with row data and and the other is a summerize sheet full with functions
like INDEX and MATCH. Example: =INDEX('Current Month Budget
Data'!$B$5:$N$702,MATCH(B11,'Current Month Budget
Data'!$A$5:$A$702,0),MATCH("Actual MTD",'Current Month Budget
Data'!$B$4:$N$4,0)). I have hundreds of similar cells in the sum sheet.
The problem starts when i need to change the range of cells, so i need to
change and modify for hours. Im trying toi find a way to write a function
that will replace this formula but cant figure how to call this function
whitin a cell.
Any thoughts?
Thanks in advance

Function instead of formula
 

you can create some named ranges (via insert/name/define)
and edit the formulas to use those names
(this can be done via insert/names/apply)

e.g. editing the named range would suffice.

Step2 is define those names to dynamically resize
themselves when data is added.

start he
http://www.contextures.com/xlNames01.html


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Lp12 wrote :

Hi All,
I have a 2 sheets workbook.
One with row data and and the other is a summerize sheet full with
functions like INDEX and MATCH. Example: =INDEX('Current Month Budget
Data'!$B$5:$N$702,MATCH(B11,'Current Month Budget
Data'!$A$5:$A$702,0),MATCH("Actual MTD",'Current Month Budget
Data'!$B$4:$N$4,0)). I have hundreds of similar cells in the sum
sheet. The problem starts when i need to change the range of cells,
so i need to change and modify for hours. Im trying toi find a way to
write a function that will replace this formula but cant figure how
to call this function whitin a cell.
Any thoughts?
Thanks in advance

Function instead of formula
 

It's normally the extent of the data that is the issue in these scenarios,
in your example 702.

In these formulas you could help yourself by naming the ranges
(InsertNameDefine...)and using these in the formulas. Each week then you
would only need to update the range definitions a few times


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Lp12" wrote in message
...
Hi All,
I have a 2 sheets workbook.
One with row data and and the other is a summerize sheet full with
functions
like INDEX and MATCH. Example: =INDEX('Current Month Budget
Data'!$B$5:$N$702,MATCH(B11,'Current Month Budget
Data'!$A$5:$A$702,0),MATCH("Actual MTD",'Current Month Budget
Data'!$B$4:$N$4,0)). I have hundreds of similar cells in the sum sheet.
The problem starts when i need to change the range of cells, so i need to
change and modify for hours. Im trying toi find a way to write a function
that will replace this formula but cant figure how to call this function
whitin a cell.
Any thoughts?
Thanks in advance

Function instead of formula
 

Thanks a lot people for the cool hook, but i want to know if i can actually
transform this formula into a VBA code and from the cell i will call my
fuction with parameters?
Thanks a lot again.

"Nick Hodge" wrote:

It's normally the extent of the data that is the issue in these scenarios,
in your example 702.

In these formulas you could help yourself by naming the ranges
(InsertNameDefine...)and using these in the formulas. Each week then you
would only need to update the range definitions a few times


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Lp12" wrote in message
...
Hi All,
I have a 2 sheets workbook.
One with row data and and the other is a summerize sheet full with
functions
like INDEX and MATCH. Example: =INDEX('Current Month Budget
Data'!$B$5:$N$702,MATCH(B11,'Current Month Budget
Data'!$A$5:$A$702,0),MATCH("Actual MTD",'Current Month Budget
Data'!$B$4:$N$4,0)). I have hundreds of similar cells in the sum sheet.
The problem starts when i need to change the range of cells, so i need to
change and modify for hours. Im trying toi find a way to write a function
that will replace this formula but cant figure how to call this function
whitin a cell.
Any thoughts?
Thanks in advance

Function instead of formula
 

you could, but you shouldn't.
vba functions take far more time to recalculate than
normal excel functions. since you have a large sheet,
with many function calls i'd never do it.

to imprive performance:

use a few "helper cells" e.g.
for the column nr that you want to extract:
put MATCH("Actual MTD",'Current Month Budget Data'!$B$4:$N$4,0)
in a cell ( or range name) and point your fml to that cell.

LOTS and LOTS faster than repeating that string in each formula.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Lp12 wrote :

Thanks a lot people for the cool hook, but i want to know if i can
actually transform this formula into a VBA code and from the cell i
will call my fuction with parameters?
Thanks a lot again.

"Nick Hodge" wrote:

It's normally the extent of the data that is the issue in these
scenarios, in your example 702.

In these formulas you could help yourself by naming the ranges
(InsertNameDefine...)and using these in the formulas. Each week
then you would only need to update the range definitions a few times


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Lp12" wrote in message
...
Hi All,
I have a 2 sheets workbook.
One with row data and and the other is a summerize sheet full
with functions
like INDEX and MATCH. Example: =INDEX('Current Month Budget
Data'!$B$5:$N$702,MATCH(B11,'Current Month Budget
Data'!$A$5:$A$702,0),MATCH("Actual MTD",'Current Month Budget
Data'!$B$4:$N$4,0)). I have hundreds of similar cells in the sum
sheet. The problem starts when i need to change the range of
cells, so i need to change and modify for hours. Im trying toi
find a way to write a function that will replace this formula but
cant figure how to call this function whitin a cell.
Any thoughts?
Thanks in advance

Function instead of formula
 

Thanks a lot All,
That did the trick.
Can i dig even more and reference to the workbookname!SheetName! as well?
Instead of MATCH("Actual MTD",'Current Month Budget Data'!$B$4:$N$4,0)
It will be MATCH("Actual MTD",<Defined Name$B$4:$N$4,0) ?
Again thatnks a lot
"keepITcool" wrote:


you could, but you shouldn't.
vba functions take far more time to recalculate than
normal excel functions. since you have a large sheet,
with many function calls i'd never do it.

to imprive performance:

use a few "helper cells" e.g.
for the column nr that you want to extract:
put MATCH("Actual MTD",'Current Month Budget Data'!$B$4:$N$4,0)
in a cell ( or range name) and point your fml to that cell.

LOTS and LOTS faster than repeating that string in each formula.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Lp12 wrote :

Thanks a lot people for the cool hook, but i want to know if i can
actually transform this formula into a VBA code and from the cell i
will call my fuction with parameters?
Thanks a lot again.

"Nick Hodge" wrote:

It's normally the extent of the data that is the issue in these
scenarios, in your example 702.

In these formulas you could help yourself by naming the ranges
(InsertNameDefine...)and using these in the formulas. Each week
then you would only need to update the range definitions a few times


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Lp12" wrote in message
...
Hi All,
I have a 2 sheets workbook.
One with row data and and the other is a summerize sheet full
with functions
like INDEX and MATCH. Example: =INDEX('Current Month Budget
Data'!$B$5:$N$702,MATCH(B11,'Current Month Budget
Data'!$A$5:$A$702,0),MATCH("Actual MTD",'Current Month Budget
Data'!$B$4:$N$4,0)). I have hundreds of similar cells in the sum
sheet. The problem starts when i need to change the range of
cells, so i need to change and modify for hours. Im trying toi
find a way to write a function that will replace this formula but
cant figure how to call this function whitin a cell.
Any thoughts?
Thanks in advance

Function instead of formula
 

You can, but it will be slower again

=MATCH("Actual MTD",INDIRECT("'"&myName&"'!$B$4:$N$4"),0)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lp12" wrote in message
...
Thanks a lot All,
That did the trick.
Can i dig even more and reference to the workbookname!SheetName! as well?
Instead of MATCH("Actual MTD",'Current Month Budget Data'!$B$4:$N$4,0)
It will be MATCH("Actual MTD",<Defined Name$B$4:$N$4,0) ?
Again thatnks a lot
"keepITcool" wrote:


you could, but you shouldn't.
vba functions take far more time to recalculate than
normal excel functions. since you have a large sheet,
with many function calls i'd never do it.

to imprive performance:

use a few "helper cells" e.g.
for the column nr that you want to extract:
put MATCH("Actual MTD",'Current Month Budget Data'!$B$4:$N$4,0)
in a cell ( or range name) and point your fml to that cell.

LOTS and LOTS faster than repeating that string in each formula.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Lp12 wrote :

Thanks a lot people for the cool hook, but i want to know if i can
actually transform this formula into a VBA code and from the cell i
will call my fuction with parameters?
Thanks a lot again.

"Nick Hodge" wrote:

It's normally the extent of the data that is the issue in these
scenarios, in your example 702.

In these formulas you could help yourself by naming the ranges
(InsertNameDefine...)and using these in the formulas. Each week
then you would only need to update the range definitions a few times


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Lp12" wrote in message
...
Hi All,
I have a 2 sheets workbook.
One with row data and and the other is a summerize sheet full
with functions
like INDEX and MATCH. Example: =INDEX('Current Month Budget
Data'!$B$5:$N$702,MATCH(B11,'Current Month Budget
Data'!$A$5:$A$702,0),MATCH("Actual MTD",'Current Month Budget
Data'!$B$4:$N$4,0)). I have hundreds of similar cells in the sum
sheet. The problem starts when i need to change the range of
cells, so i need to change and modify for hours. Im trying toi
find a way to write a function that will replace this formula but
cant figure how to call this function whitin a cell.
Any thoughts?
Thanks in advance