Function instead of formula

[Lp12]

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

 

[keepITcool]

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 :

 

[Nick Hodge]

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]

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.

 

[keepITcool]

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 :

 

[Lp12]

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

 

[Bob Phillips]

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)