Thread: 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

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 here:
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

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
(Insert>Name>Define...)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" <Lp12@discussions.microsoft.com> wrote in message
news:30B3E404-C988-43E2-9019-11F2150C60C6@microsoft.com...
> 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

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
> (Insert>Name>Define...)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" <Lp12@discussions.microsoft.com> wrote in message
> news:30B3E404-C988-43E2-9019-11F2150C60C6@microsoft.com...
> > 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
>
>
>

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
> > (Insert>Name>Define...)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" <Lp12@discussions.microsoft.com> wrote in message
> > news:30B3E404-C988-43E2-9019-11F2150C60C6@microsoft.com...
> > > 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
> >
> >
> >

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
> > > (Insert>Name>Define...)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" <Lp12@discussions.microsoft.com> wrote in message
> > > news:30B3E404-C988-43E2-9019-11F2150C60C6@microsoft.com...
> > > > 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
> > >
> > >
> > >
>

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" <Lp12@discussions.microsoft.com> wrote in message
news:919AD8C8-9313-486E-A420-E76F0E7F1E44@microsoft.com...
> 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
> > > > (Insert>Name>Define...)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" <Lp12@discussions.microsoft.com> wrote in message
> > > > news:30B3E404-C988-43E2-9019-11F2150C60C6@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >