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