Function instead of formula

L

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
 
K

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

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
 
L

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.
 
K

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

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top