#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Index/Match in VBA

I havea fairly Complex index match that is used to prepopulate a
schedule. I am pulling names into a column from another sheet based on
a job classification.

The index/match formula looks something like this:

IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,
zSchedule!$M$1:$M$600,0),1)=J$3, INDEX(zSchedule!$C$1:$C$600,
MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

J$3 verifies the job classification (it is the heading of a column in
the sheet - so a cell reference here).

Is there anyone that help me indentify how to write his in VBA? I want
to use it in a macro so my employees can paste in the formula to
visible cells and then hard value it.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Index/Match in VBA

Selection.Formula = "=IF(INDEX(zSchedule!$P$1:$P$600, " & _
"MATCH($D" & Selection.Row & ",zSchedule!$M$1:$M$600,0),1)=" & _
Cells(3, Selection.Column).Address(True, False) & _
", INDEX(zSchedule!$C$1:$C$600, MATCH($D" & _
Selection.Row & ", zSchedule!$M$1:$M$600,0),1), """")"


This assumes that formula in the upper left of the selection should refer to column D of the same
row (the $D4), and to the same column, row 3 (The J$3), so the formula as given in your example,
will be produced if the upper left of the selection is cell J4.... so the formula in J4 (produced by
the code above) is

=IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,zSchedule!$M$1:$M$600,0),1)=J$3,
INDEX(zSchedule!$C$1:$C$600, MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
I havea fairly Complex index match that is used to prepopulate a
schedule. I am pulling names into a column from another sheet based on
a job classification.

The index/match formula looks something like this:

IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,
zSchedule!$M$1:$M$600,0),1)=J$3, INDEX(zSchedule!$C$1:$C$600,
MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

J$3 verifies the job classification (it is the heading of a column in
the sheet - so a cell reference here).

Is there anyone that help me indentify how to write his in VBA? I want
to use it in a macro so my employees can paste in the formula to
visible cells and then hard value it.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default Index/Match in VBA

Thank you!


Bernie Deitrick wrote:
Selection.Formula = "=IF(INDEX(zSchedule!$P$1:$P$600, " & _
"MATCH($D" & Selection.Row & ",zSchedule!$M$1:$M$600,0),1)=" & _
Cells(3, Selection.Column).Address(True, False) & _
", INDEX(zSchedule!$C$1:$C$600, MATCH($D" & _
Selection.Row & ", zSchedule!$M$1:$M$600,0),1), """")"


This assumes that formula in the upper left of the selection should refer to column D of the same
row (the $D4), and to the same column, row 3 (The J$3), so the formula as given in your example,
will be produced if the upper left of the selection is cell J4.... so the formula in J4 (produced by
the code above) is

=IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,zSchedule!$M$1:$M$600,0),1)=J$3,
INDEX(zSchedule!$C$1:$C$600, MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
I havea fairly Complex index match that is used to prepopulate a
schedule. I am pulling names into a column from another sheet based on
a job classification.

The index/match formula looks something like this:

IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,
zSchedule!$M$1:$M$600,0),1)=J$3, INDEX(zSchedule!$C$1:$C$600,
MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

J$3 verifies the job classification (it is the heading of a column in
the sheet - so a cell reference here).

Is there anyone that help me indentify how to write his in VBA? I want
to use it in a macro so my employees can paste in the formula to
visible cells and then hard value it.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Index/Match in VBA

You're welcome... I'm guessing it worked OK ;-)

Bernie
MS Excel MVP


"Nimish" wrote in message
oups.com...
Thank you!


Bernie Deitrick wrote:
Selection.Formula = "=IF(INDEX(zSchedule!$P$1:$P$600, " & _
"MATCH($D" & Selection.Row & ",zSchedule!$M$1:$M$600,0),1)=" & _
Cells(3, Selection.Column).Address(True, False) & _
", INDEX(zSchedule!$C$1:$C$600, MATCH($D" & _
Selection.Row & ", zSchedule!$M$1:$M$600,0),1), """")"


This assumes that formula in the upper left of the selection should refer to column D of the same
row (the $D4), and to the same column, row 3 (The J$3), so the formula as given in your example,
will be produced if the upper left of the selection is cell J4.... so the formula in J4 (produced
by
the code above) is

=IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,zSchedule!$M$1:$M$600,0),1)=J$3,
INDEX(zSchedule!$C$1:$C$600, MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
I havea fairly Complex index match that is used to prepopulate a
schedule. I am pulling names into a column from another sheet based on
a job classification.

The index/match formula looks something like this:

IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,
zSchedule!$M$1:$M$600,0),1)=J$3, INDEX(zSchedule!$C$1:$C$600,
MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

J$3 verifies the job classification (it is the heading of a column in
the sheet - so a cell reference here).

Is there anyone that help me indentify how to write his in VBA? I want
to use it in a macro so my employees can paste in the formula to
visible cells and then hard value it.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index/Match not working frosterrj Excel Worksheet Functions 6 May 22nd 06 06:18 PM
Index/Match Multiple Criteria EstherJ Excel Discussion (Misc queries) 2 March 31st 06 12:54 PM
Possible index/match problem? smoore Excel Worksheet Functions 3 February 23rd 06 11:48 PM
How do I display more than one match in a Index/Match formula? Trish Excel Worksheet Functions 0 September 26th 05 10:21 PM
Index/Match Help singsunnyboy7 Excel Discussion (Misc queries) 1 July 26th 05 10:18 PM


All times are GMT +1. The time now is 12:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"