YTLE#77: INDEX INDIRECT MATCH functions for Payroll

Hi,

I need some heads up on how to combine some formulas. Here is my formula:

=INDEX(A5:T100,ROW(5:5),MATCH(L4,A4:T4,0))

Arrays are in 3 separate sheets (similar layout). Cell L3 would represent sheet name. So, whatever sheet name is in L3, formula is working with that sheet.

Thanks in advance!

ISO VB function to replace this long nested Excel function.

=INDEX(INDIRECT(LEFT($B17,2)&"_range" ),MATCH($B17,INDIRECT(LEFT($B17,2)&"_column" ),0),COLUMN(B17))

The function is a Index/Match lookup based on the contents of colB.
The table_array is one of three ranges - beginning with the first two letters of the lookup_string eg: Left("ME0507",2)&"_range" = ME_range
The match_array is one of three column ranges - beginning with the same two letters of the lookup string eg: ME_column
The column_index uses a column() function to allow the index to increment when copied (because Index() uses absolute index value)

I'd expect the VB function to look like =CustomIndex($B17,B17)
Thank you in advance for the code.

I have several worksheets and I'd like to pick up a random value from one of them and display it in a generic worksheet. I know how to do that if I can spell out the name of the worksheet, but when I have to read the worksheet name from a cell, I'm having problem.

For example I can do this with no problem :
=INDEX(ws3!$A$1:ws3!$A$4,INT(RAND()*4)+1,1)

where ws3 is the name of the worksheet and I know there are only 4 values stored on it in cells A1 thru A4. I can pick one of them randomly.

But if instead, the generic worksheet has a column with the worksheet names in column B, so if B2 says attrnames, in C2 I want to do the function I wrote above but taking the worksheet name from B2. I tried many things using indirect and was unable to do it.

Here is a picture of my generic worksheet

A B
1 ws1 RandomvaluepulledfromA1..A4 of ws1
2 ws7 RandomvaluepulledfromA1..A4 of ws7
3 ws3 RandomvaluepulledfromA1..A4 of ws3
4

How can I add a variable to this match and index statement

Each column has the value "week 01", "week 02" etc
which matches with a file name "week 01.xls", "week 02.xls" etc

So far I have:
Code:

=INDEX('L:\Customers\2009\[Week 20.xls]Checking list'!$AM$4:$AM$299,MATCH($A4,'L:\Customers\2009\[Week 20.xls]Checking list'!$C$4:$C$299,0))

I want the [week 20.xls] bit to be populated by variable in row 2 .


I have tried:
Code:

=INDEX('L:\Customers\2009\&indirect (AM$3)&Checking list'!$AM$4:$AM$299,MATCH($A4,'L:\Customers\2009\&indirect (AM$3)&Checking list'!$C$4:$C$299,0))

where AM3 = [Week 21.xls]
Martin

I have the following formula in cell AE10 in one workbook which, when the directory, workbook, and worksheet name from another file is hardcoded works fine in giving me the result from the appropriate indexed cell matching my criteria.

=IF(ISERROR(INDEX('F:\Test Excel Files\Sqm\[Timesheet Totals.xls]Pivot'!$A$2:$B$64,MATCH($B10,'F:\Test Excel Files\Sqm\[Timesheet Totals.xls]Pivot'!$A$2:$A$64,0),2)),"",INDEX('F:\Test Excel Files\Sqm\[Timesheet Totals.xls]Pivot'!$A$2:$B$64,MATCH($B10,'F:\Test Excel Files\Sqm\[Timesheet Totals.xls]Pivot'!$A$2:$A$64,0),2))

If however I am trying to use an INDIRECT statement in the formula because my directory path will change for each new project. Here what my formula using the INDIRECT statement looks like;

=IF(ISERROR(INDEX(INDIRECT("'"& $G$1 &"'" &"!" $A$2:$B$64,MATCH($B10,(INDIRECT("'"& $G$1 &"'" &"!" $A$2:$A$64,0),2)))))),"",INDEX(INDIRECT("'"& $G$1 &"'" &"!" $A$2:$B$64,MATCH($B10,(INDIRECT("'"& $G$1 &"'" &"!" $A$2:$A$64,0),2))))))

Where cell G1 contains the following text "F:\Test Excel Files\Sqm\[Timesheet Totals.xls]Pivot" without the quotes. Windows XP running Excell 2003 returns a formula error and when OK is selected highlights the entire formula.

Can someone assist me in the correct formating of the formula?

=INDEX(INDIRECT("'"&C1&"'!"& $A$1:$L$69 ),MATCH($A2,INDIRECT("'"&C1&"'!"& $A$1:$A$69 ),0),MATCH($B5,INDIRECT(("'"&C1&"'!"& $1:$1 ),0)))/$B$1

Hi, with formula above I am trying to pull data from a worksheet: name in C1. The problem is that it pulls ranges in the same master worksheet. Any suggestions?

Hi
I am trying to develop an Excel 2007 workbook to keep track of various projects. The workbook (attached) has five tabs:
ProjectSummary - a brief executive overview
Timeline - a Gantt chart of the resources for each project
ResourcePlan - A list of projects, with the resource requirements for a particular month
BudgetResourceDays - list of resources and the total usage for a pacticular month
BudgetResourceCostSumm - list of resources with total cost for a particula month

I am trying to develop the Timeline/Gantt chart. For example, the Online Forms project uses rows 5 to 16 (last blank cell in column A) in the ResourcePlan. I currently have to copy/paste the calculation in (eg =SUM(ResourcePlan!G5:G16) etc).

I think it should be possible to look up the relevant rows in the ResourcePlan tab, total them using a combination of MATCH, INDIRECT and INDEX, but I haven't managed to get this to work.

Any ideas?

Regards
Robert

Ok,
I have two workbooks. For this example we will call one workbook data.xls and the other is results.xls. The data.xls has 13 tabs (one for each month and total tab YTD). I want to use results to pull data from certain spots on data.xls.

Clear as mud yet?


I attached sample results on this thread.

I want to use a formula to pull data from the table based on index/match but want to use indirect. Here is my index function:


=INDEX([data.xls]Jun!$B$2:$D$4, MATCH(A2,[data.xls]Jun!$A$2:$A$4,0),MATCH(A1,[data.xls]Jun!$B$1:$D$1,0))

Now, I do not want data.xls AND/OR Jun to be static. I want to use something like this:

=INDEX(["&A3&".xls]Jun!$B$2:$D$4, MATCH(A2,["&A3&".xls]Jun!$A$2:$A$4,0),MATCH(A1,["&A3&".xls]Jun!$B$1:$D$1,0))

OR

=INDEX([data.xls]"&A$&"!$B$2:$D$4, MATCH(A2,[data.xls]"&A4&"!$A$2:$A$4,0),MATCH(A1,[data.xls]"A4&"!$B$1:$D$1,0))

SO my question is how do I use an indirect within my index/match???

I DO NOT want to use an indirect function that uses name ranges. I would need to name ranges constantly and would take away from my "automation" of this data entry spreadsheet. Anyone got any ideas? I will use an INDIRECT if I knew how to use indirect/match to find ranges of cells vs defined names...?

Thank you in advance to anyone who can help!

Hi all,
I wonder if you can use the Index- Match feature as part of the Indirect formula to find a column?
James

I currently have an issue where I want to look up a tab based upon an input cell and then index / match to return a single value. Not sure If I am going about it correctly but hopefully you can help me out. Thinking it could also be done with the offset function but the fields constantly change order, but not name.

=iferror((index(INDIRECT("'"&B1&"Data '!"&"$K$15:$KT$1514"),match(1,INDIRECT("'"&B1&"Data'!"&"$B$16:$B$1514"= a11),0),match(INDIRECT("'"&B1&"Data'!"&"$K$15:$KT$15"="Inventory for "&"N9", 0)),0)

Cell B1 contains the current month
Cell a11 contains the SKU to look up in the list
Cell N9 is the date - needs to be dynamic so I can drag it across time

I need to be able to get to the right tab based upon the input cell in B1 and match based upon sku number and the name of the heading cell where the data is contained that will be dynamic due to the dates changing.

Thanks in advance