Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Combine Index+match Functions With Indirect Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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!

View Answers     

Similar Excel Tutorials

Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...
Best Lookup Formula in Excel - Index and Match
A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Match lookups offer you free ...
Combine Data from Multiple Worksheets in Excel
The easiest way to combine and consolidate data in Excel. Simple method to combine data from multiple worksheets i ...
Combine Values from Multiple Cells into One Cell in Excel
There are two easy ways to combine values from multiple cells in Excel. In order to do this, we need to do what is ...

Helpful Excel Macros

Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics







Hello, first post, hope someone can help...

My workbook has a sheet for each month (P1, P2, P3, etc...) and a summary sheet that looks at data in the most recent month sheet.

Each time I add a new month, I want a quick way to tell the summary sheet to refer to the new month sheet. I've got this to work up to a point by naming the data range in the month sheets, entering the range name required in a cell on my summary sheet and using INDIRECT to refer to that range. Then I only need change the cell with the range name on the summary sheet to get the formulas to look at the new sheet.

My problem is that the the data I want to look at on the month sheets can change position / size, so it would be useful to use INDEX, MATCH formulas to tell my summary where to look. I can't get INDEX, MATCH to work in conjunction with INDIRECT.

Example:
=INDEX(INDIRECT(C1),MATCH(A4,P11_2007!C17:C29,0),MATCH(C2,P11_2007!A17:N17,0))
This works because I have told it exactly where to look for MATCH - C17:C29; A17:N17, but if the location of that lookup changes, the formula will fail.

I tried:
=INDEX(INDIRECT(C1),MATCH(A4,INDIRECT(C1),0),MATCH(C2,INDIRECT(C1),0))
but it returns #n/a, the column and row lookups for MATCH won't work with INDIRECT.




Hello! First time user and poster. I've searched and seen several "indirect" functions but I still can't get a formula that accomplishes my goal.

This formula works for pulling info off one worksheet:
=INDEX(sheet!$1:$1048576,MATCH($A4,sheet!$A:$A,0),MATCH(C$2,sheet!$3:$3,0))

Now, I am trying to make this same formula a "sum" function over a range of worksheets (without having to simply add the same string above with each sheets name). The spreadsheets have to be index/matched because my two match criteria, month and account, are in different cell references across spreadsheets.

Any help would be appreciated!

Thanks,
Andy

Hello,

I have a range of data that I would like to conditionally format all cells that have an error to have white font. Unfortunately, I cannot use a function like this:

if(iserror(function,"",function))

because the formulas in this range are already too long to be able to duplicate for the 'value if false' argument. I have also tried using the conditional formatting tool by setting "Formula Is" equal to:
=ISERROR($A$1)
but this only whites out errors in cell A1 or column A (depending on what I selected.

I have pasted the formula below that is giving me the error if anyone has any ideas on how to modify the formula as well. It is basically an index match formula that is looking at several different data points on another worksheet and averaging them. Thanks in advance.

=AVERAGE(INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$2,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH( $C18,SRD!$A$4:$A$351,0),MATCH(M$3,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$4,SRD! $D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$5,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351, MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$6,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$ 7,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$13,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$ DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$8,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),M ATCH(M$9,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$10,SRD!$D$2:$DG$2,0)),INDEX(SRD !$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$11,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$ 351,0),MATCH(M$12,SRD!$D$2:$DG$2,0)))




Good Evening,

I currently have a spread sheet which has an editable field in C3, B3 & E3 are populated by an index match formula to Tab 2, the formula is =IF(C3<>"",INDEX('2'!AG:AG,MATCH(C3,'2'!B:B,0)),"") for B3 and =IF(C3<>"",INDEX('2'!AO:AO,MATCH(C3,'2'!B:B,0)),"") for E3
I now require A3 to perform an index match against the data in B3 to Tab 1 this is my current formula =IF(C3<>"",INDEX('1'!B:B,MATCH(B3,Schedule!B:B,0)),"") but it doesn't appear to working correctly.

If anyone can figure this out it would be much appreciated.

I have attached a sample of my spread sheet to help

many thanks in advance

index.xlsx



Hi there!!

The following is the problem I am encountering on excel 2010:

I have a quite complex worksheet and I named (static) some tables to scale down the complexity of the formula a bit.

In particular, in one sheet I have 25 tables (one for every company) and I named them accordingly. In another sheet I would like to be able to use an index match formula to retrieve some values from those tables I named (contained in the other sheet).

Now, no problem if in the index formula I write the name of the table as I created it, but I would like to make the process easier and create a reference to the name in another cell in the sheet. How can I do that?

Example:

Table name: "Company1" Sheet containing the table: "Sheet_Tables" Sheet containing the Index formula: "Sheet_Indexes" Formula that works: =index(Company1,match(...)

I would like to: - Use a cell in Sheet_Indexes (let's say cell A1) to create a reference to the table name, so: A1= Company1 - use the reference in the index formula to get to the table name instead of writing the full name of the table in every index formula, so: index(A1,match(...).

Does anyone know how to perform it?

I am trying with some indirect formula but it's not really working..

Thanks in advance!

Hi,

Whenever i input the following formula, a messagebox pops up and tells me, "Formula is too long". Can someone help me shorten this formula? In its current form it's 1535 character long and needs to be less than 1035.

VB:

=If(record_index3=1,(If(record_index=1,INDEX($H$551:$BC$566,MATCH($C$909,$G$551:$G$566,False),4),If(record_index=2,INDEX($H$568:$BC$583,MATCH($C$909,$G$568:$G$583,False),4),If(record_index=3,INDEX($H$585:$BC$600,MATCH($C$909,$G$585:$G$600,False),4),INDEX($H$759:$BC$774,MATCH($C$909,$G$759:$G$774,False),4)))))),If(record_index3=2,(If(record_index=1,INDEX($H$603:$BC$618,MATCH($C$909,$G$603:$G$618,False),4),If(record_index=2,INDEX($H$620:$BC$635,MATCH($C$909,$G$620:$G$635,False),4),If(record_index=3,INDEX($H$637:$BC$652,MATCH($C$909,$G$637:$G$652,False),4),INDEX($H$776:$BC$791,MATCH($C$909,$G$776:$G$791,False),4)))))),If(record_index3=3,(If(record_index=1,INDEX($H$655:$BC$670,MATCH($C$909,$G$655:$G$670,False),4),If(record_index=2,INDEX($H$672:$BC$687,MATCH($C$909,$G$672:$G$687,False),4),If(record_index=3,INDEX($H$689:$BC$704,MATCH($C$909,$G$689:$G$704,False),4),INDEX($H$793:$BC$808,MATCH($C$909,$G$793:$G$808,False),4)))))),If(record_index3=4,(If(record_index=1,INDEX($H$707:$BC$722,MATCH($C$909,$G$707:$G$722,False),4),If(record_index=2,INDEX($H$724:$BC$739,MATCH($C$909,$G$724:$G$739,False),4),If(record_index=3,INDEX($H$741:$BC$756,MATCH($C$909,$G$741:$G$756,False),4),INDEX($H$810:$BC$825,MATCH($C$909,$G$810:$G$825,False),4)))))),If(record_index=1,INDEX($H$827:$BC$842,MATCH($C$909,$G$827:$G$842,False),4),If(record_index=2,INDEX($H$844:$BC$859,MATCH($C$909,$G$844:$G$859,False),4),If(record_index=3,INDEX($H$861:$BC$876,MATCH($C$909,$G$861:$G$876,False),4),INDEX($H$878:$BC$893,MATCH($C$909,$G$878:$G$893,False),4)))))) 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I was able to Name $C$909 to NM and change FALSE to 0 and shorten the names of record_index and record_index3, but that only got me to 1185. It's the MATCH and INDEX functions taking up space I guess.

Thanks in advance for any help.

Excel Lurker

Hi all,

I am trying to insert a formula to a cell with the line below.

ActiveCell.FormulaR1C1 = "=IF(OR($E$6=Balance!$H$26,$E$6=Balance!$H$27,$E$6=Balance!$H$28),INDEX(INDIRECT("'XA"&$E$6&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$6&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$6&"'!$E$14:$GH$14"),0)),INDEX(INDIRECT("'XA"&$E$6&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$6&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$6&"'!$E$14:$GH$14"),0))+INDEX(INDIRECT("'XA"&$E$7&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$7&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$7&"'!$E$14:$GH$14"),0)))"

The issue I am having is that VBA code comments out the part of the formula that comes after ', see below.

(INDIRECT(" 'XA

Thanks in advance.


Need some help here!

Week 3 if Data has been entered Week 4 returns an error N/A.

If no data is entered example H17 then the formula fine

What I'm looking for in week 4 is not to return an N/A error if no data has been entered.

Any help much appreciated.


Killer

* B C D E F G H I J K 14 * Start Week 1 Week 2 Week 3 Week 4 15 Rob W. $20 PIT v IND X OAK X #N/A # 16 Alene $20 NYJ X NE v GB X #N/A # 17 Steve W. $20 SF P IND X * * * *
Spreadsheet Formulas Cell Formula D15 =IF(INDEX('W1'!$AQ$10:$AQ$49, MATCH($B15,'W1'!$B$10:$B$49,0))="", "",INDEX('W1'!$AQ$10:$AQ$49,MATCH($B15,'W1'!$B$10:$B$49,0))) E15 {=IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$54:AL$54,0)),"v",IF(INDEX('W1'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(D15,'W1'!C$51:AL$51&'W1'!C$52:AL$52)),0))="Push","P","X")))} F15 =IF(INDEX('W2'!$AQ$10:$AQ$49, MATCH($B15,'W2'!$B$10:$B$49,0))="", "",INDEX('W2'!$AQ$10:$AQ$49,MATCH($B15,'W2'!$B$10:$B$49,0))) G15 {=IF(F15="","",IF(ISNUMBER(MATCH(F15,'W2'!C$54:AL$54,0)),"v",IF(INDEX('W2'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(F15,'W2'!C$51:AL$51&'W2'!C$52:AL$52)),0))="Push","P","X")))} H15 =IF(INDEX('W3'!$AQ$10:$AQ$49, MATCH($B15,'W3'!$B$10:$B$49,0))="", "",INDEX('W3'!$AQ$10:$AQ$49,MATCH($B15,'W3'!$B$10:$B$49,0))) I15 {=IF(H15="","",IF(ISNUMBER(MATCH(H15,'W3'!C$54:AL$54,0)),"v",IF(INDEX('W3'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(H15,'W3'!C$51:AL$51&'W3'!C$52:AL$52)),0))="Push","P","X")))} J15 =IF(INDEX('W4'!$AQ$10:$AQ$49, MATCH($B15,'W4'!$B$10:$B$49,0))="", "",INDEX('W4'!$AQ$10:$AQ$49,MATCH($B15,'W4'!$B$10:$B$49,0))) K15 {=IF(J15="","",IF(ISNUMBER(MATCH(J15,'W4'!C$54:AL$54,0)),"v",IF(INDEX('W4'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(J15,'W4'!C$51:AL$51&'W4'!C$52:AL$52)),0))="Push","P","X")))} D16 =IF(INDEX('W1'!$AQ$10:$AQ$49, MATCH($B16,'W1'!$B$10:$B$49,0))="", "",INDEX('W1'!$AQ$10:$AQ$49,MATCH($B16,'W1'!$B$10:$B$49,0))) E16 {=IF(D16="","",IF(ISNUMBER(MATCH(D16,'W1'!C$54:AL$54,0)),"v",IF(INDEX('W1'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(D16,'W1'!C$51:AL$51&'W1'!C$52:AL$52)),0))="Push","P","X")))} F16 =IF(INDEX('W2'!$AQ$10:$AQ$49, MATCH($B16,'W2'!$B$10:$B$49,0))="", "",INDEX('W2'!$AQ$10:$AQ$49,MATCH($B16,'W2'!$B$10:$B$49,0))) G16 {=IF(F16="","",IF(ISNUMBER(MATCH(F16,'W2'!C$54:AL$54,0)),"v",IF(INDEX('W2'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(F16,'W2'!C$51:AL$51&'W2'!C$52:AL$52)),0))="Push","P","X")))} H16 =IF(INDEX('W3'!$AQ$10:$AQ$49, MATCH($B16,'W3'!$B$10:$B$49,0))="", "",INDEX('W3'!$AQ$10:$AQ$49,MATCH($B16,'W3'!$B$10:$B$49,0))) I16 {=IF(H16="","",IF(ISNUMBER(MATCH(H16,'W3'!C$54:AL$54,0)),"v",IF(INDEX('W3'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(H16,'W3'!C$51:AL$51&'W3'!C$52:AL$52)),0))="Push","P","X")))} J16 =IF(INDEX('W4'!$AQ$10:$AQ$49, MATCH($B16,'W4'!$B$10:$B$49,0))="", "",INDEX('W4'!$AQ$10:$AQ$49,MATCH($B16,'W4'!$B$10:$B$49,0))) K16 {=IF(J16="","",IF(ISNUMBER(MATCH(J16,'W4'!C$54:AL$54,0)),"v",IF(INDEX('W4'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(J16,'W4'!C$51:AL$51&'W4'!C$52:AL$52)),0))="Push","P","X")))} D17 =IF(INDEX('W1'!$AQ$10:$AQ$49, MATCH($B17,'W1'!$B$10:$B$49,0))="", "",INDEX('W1'!$AQ$10:$AQ$49,MATCH($B17,'W1'!$B$10:$B$49,0))) E17 {=IF(D17="","",IF(ISNUMBER(MATCH(D17,'W1'!C$54:AL$54,0)),"v",IF(INDEX('W1'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(D17,'W1'!C$51:AL$51&'W1'!C$52:AL$52)),0))="Push","P","X")))} F17 =IF(INDEX('W2'!$AQ$10:$AQ$49, MATCH($B17,'W2'!$B$10:$B$49,0))="", "",INDEX('W2'!$AQ$10:$AQ$49,MATCH($B17,'W2'!$B$10:$B$49,0))) G17 {=IF(F17="","",IF(ISNUMBER(MATCH(F17,'W2'!C$54:AL$54,0)),"v",IF(INDEX('W2'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(F17,'W2'!C$51:AL$51&'W2'!C$52:AL$52)),0))="Push","P","X")))} H17 =IF(INDEX('W3'!$AQ$10:$AQ$49, MATCH($B17,'W3'!$B$10:$B$49,0))="", "",INDEX('W3'!$AQ$10:$AQ$49,MATCH($B17,'W3'!$B$10:$B$49,0))) I17 {=IF(H17="","",IF(ISNUMBER(MATCH(H17,'W3'!C$54:AL$54,0)),"v",IF(INDEX('W3'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(H17,'W3'!C$51:AL$51&'W3'!C$52:AL$52)),0))="Push","P","X")))} J17 =IF(INDEX('W4'!$AQ$10:$AQ$49, MATCH($B17,'W4'!$B$10:$B$49,0))="", "",INDEX('W4'!$AQ$10:$AQ$49,MATCH($B17,'W4'!$B$10:$B$49,0))) K17 {=IF(J17="","",IF(ISNUMBER(MATCH(J17,'W4'!C$54:AL$54,0)),"v",IF(INDEX('W4'!C$54:AL$54,MATCH(1,--ISNUMBER(SEARCH(J17,'W4'!C$51:AL$51&'W4'!C$52:AL$52)),0))="Push","P","X")))} Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4




Hi,

Looking to Improve the formula in the tons/mo column D63 through D67, that is while the INDEX/MATCH approach is working, I believe to be a bit clumsy and not as flexible. Is there an alternative worksheet function combination that will allow the lookup and summing portion on non-contiguous ranges instead of summing multiple INDEX/MATCH's? Perhaps INDIRECT, ROW, OFFSET etc.

To allow for display, I hid rows that have other data in them, thus the rows must be preserved. Also, in the January 2008 case there are no values in the first range, but there will be in other months/sheets so I need to include this range as well.


January 2008

* B C D E F G H I J K L 5 January 2008 * * * * * * * * * * 6 * 21 Pollutant Emissions @ Power Setting (lb) * * * Total (tons) Emissions @ Power Setting (lb) * * * Total (tons) 22 CO 0 0 0 0 0.00 0 0 0 0 0.00 23 VOC 0 0 0 0 0.00 0 0 0 0 0.00 24 NOX 0 0 0 0 0.00 0 0 0 0 0.00 25 SO2 0 0 0 0 0.00 0 0 0 0 0.00 26 PM 0 0 0 0 0.00 0 0 0 0 0.00 36 Pollutant Emissions @ Power Setting (lb) * * * Total (tons) Emissions @ Power Setting (lb) * * * Total (tons) 37 CO 76 10 9 4 0.05 197 4 11 7 0.11 38 VOC 6 1 1 1 0.00 16 0 1 0 0.01 39 NOX 18 68 453 342 0.44 43 32 733 805 0.81 40 SO2 1 2 7 4 0.01 4 1 11 9 0.01 41 PM 6 9 30 17 0.03 16 5 46 38 0.05 42 * * * * * * * * * * * 51 Pollutant Emissions @ Power Setting (lb) * * * Total (tons) Emissions @ Power Setting (lb) * * * Total (tons) 52 CO 196 9 23 20 0.124 546 7 24 14 0.296 53 VOC 10 0 1 1 0.01 45 0 1 0 0.02 54 NOX 31 27 454 466 0.49 120 51 1569 1557 1.65 55 SO2 3 1 10 9 0.012 11 2 24 18 0.027 56 PM 12 5 42 37 0.05 45 8 98 74 0.11 57 * * * * * * * * * * * 60 * * * * * * * * * * * 61 * Pollutant Summary * * * * * * 62 * Pollutant * tons/mo ave lbs/day * * * * * * * 63 * CO 0.579 37.37 * * * * * * * 64 * VOC 0.043 2.76 * * * * * * * 65 * NOx 3.384 218.34 * * * * * * * 66 * SO2 0.059 3.81 * * * * * * * 67 * PM 0.245 15.82 * * * * * * * 68 * * * * * * * * * * * 69 * Number of Days This Month: * * 31 * * * * * *
Spreadsheet Formulas Cell Formula D63 =INDEX($L$52:$L$56,MATCH(C63,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C63,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C63,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C63,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C63,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C63,$B$22:$B$26,0)) E63 =D63*2000/$F$69 D64 =INDEX($L$52:$L$56,MATCH(C64,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C64,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C64,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C64,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C64,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C64,$B$22:$B$26,0)) E64 =D64*2000/$F$69 D65 =INDEX($L$52:$L$56,MATCH(C65,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C65,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C65,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C65,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C65,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C65,$B$22:$B$26,0)) E65 =D65*2000/$F$69 D66 =INDEX($L$52:$L$56,MATCH(C66,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C66,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C66,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C66,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C66,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C66,$B$22:$B$26,0)) E66 =D66*2000/$F$69 D67 =INDEX($L$52:$L$56,MATCH(C67,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C67,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C67,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C67,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C67,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C67,$B$22:$B$26,0)) E67 =D67*2000/$F$69 F69 =DAY(DATE(YEAR($B$5),MONTH($B$5)+1,1)-1)

Excel tables to the web >> Excel Jeanie HTML 4




Hello, I am trying to combine the Index/Match functions with the Concatenate Function.

I have two sheets (Sheet 1, Sheet 2). I want cell A1 in Sheet 1 to index and match a cell in Sheet 2. Sheet 2's name will change several times throughout the year, so I just want to be able to type the name of Sheet 2 into a cell B1 in Sheet 1 and have A1 reference that file name from the name in B1.


The Index/Match formula is simple (and works) if the file name for Sheet 2 stayed static. In this example, A1 is a color. It will reference the cell in Sheet 2 that matches "Fruits" and return to me the item in the Items Column.
INDEX('C:\Files\Spreadsheets\Sheet2.xls'!Items,MATCH($A$1,'C:\Files\Spreadsheets\Sheet2.xls'!Fruits,0))


Since the name is not static, I need to use the concatenate function to type in the new year and month (displayed as 2013.10 for October 2013) that each file is saved by. The file name would be "List 2013.10.xls" Next month it would be "List 2013.11.xls"

I came up with this formula but it is not working...can anyone tell me where I am going wrong?
INDEX("'"&CONCATENATE("C:\Files\Spreadsheets\",B1,".xls")!Items, Match($A$1,("'"&CONCATENATE("C:\Files\Spreadsheets\",B1,".xls")!Fruits,0))

In this, Cell B1 = "List 2013"

Can anyone help me here??

Thanks in advance

I've been using a long If formula in order to eliminate #n/a values:

=IF(ISNA(INDEX(INDIRECT(D$1&"!$A:$AZ"),MATCH($A3,INDIRECT(D$1&"!$A:$A"),0),MATCH(D$2,INDIRECT(D$1&"!$1:$1"),0))),"",INDEX(INDIRECT(D$1&"!$A:$AZ"),MATCH($A3,INDIRECT(D$1&"!$A:$A"),0),MATCH(D$2,INDIRECT(D$1&"!$1:$1"),0)))


Is there a shorter way to elimate the if statement and do the following:

For each cell in selection
.value=INDEX(INDIRECT(D$1&"!$A:$AZ"),MATCH($A3,INDIRECT(D$1&"!$A:$A"),0),MATCH(D$2,INDIRECT(D$1&"!$1:$1"),0))
Eliminate all #n/a
Next cell


Hi all,

I've got a drop down list which is linked to a source sheet called "Mapping", and on this sheet I have selected my range for Data Validation for a Drop Down list.

However, there are certain values which are repeated, so I've used some IF, COUNTIF, ROWS and INDEX formulas to sort it out and remove duplicate entries.

In the table, below, the data is stored in Column C, the first part of my calculations is in Column A, and then the second part is in Column B - which I want to use as the source for my drop down list. The only problem that I have is that instead of blanks cells there are 0s in the cell as below. Could someone help me fix it? (cell C2 is intentionally blank)


Mapping

* A B C 1 * * Entity 2 * * * 3 1 210 210 4 1 220 210 5 1 230 210 6 2 240 220 7 2 250 220 8 3 0 230 9 3 0 230 10 3 0 230 11 4 0 240 12 5 0 250 13 6 0 * 14 7 0 * 15 8 0 * 16 9 0 * 17 10 0 * 18 11 0 * 19 12 0 * 20 13 0 * 21 14 * * 22 15 * * 23 16 * * 24 17 * * 25 18 * * 26 * * * 27 * * *
Spreadsheet Formulas Cell Formula A3 =A2+IF(COUNTIF(C$3:C3,C3)>1,0,1) B3 =IF(ROWS(A$3:A3)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A3),A:A,0))) A4 =A3+IF(COUNTIF(C$3:C4,C4)>1,0,1) B4 =IF(ROWS(A$3:A4)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A4),A:A,0))) A5 =A4+IF(COUNTIF(C$3:C5,C5)>1,0,1) B5 =IF(ROWS(A$3:A5)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A5),A:A,0))) A6 =A5+IF(COUNTIF(C$3:C6,C6)>1,0,1) B6 =IF(ROWS(A$3:A6)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A6),A:A,0))) A7 =A6+IF(COUNTIF(C$3:C7,C7)>1,0,1) B7 =IF(ROWS(A$3:A7)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A7),A:A,0))) A8 =A7+IF(COUNTIF(C$3:C8,C8)>1,0,1) B8 =IF(ROWS(A$3:A8)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A8),A:A,0))) A9 =A8+IF(COUNTIF(C$3:C9,C9)>1,0,1) B9 =IF(ROWS(A$3:A9)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A9),A:A,0))) A10 =A9+IF(COUNTIF(C$3:C10,C10)>1,0,1) B10 =IF(ROWS(A$3:A10)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A10),A:A,0))) A11 =A10+IF(COUNTIF(C$3:C11,C11)>1,0,1) B11 =IF(ROWS(A$3:A11)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A11),A:A,0))) A12 =A11+IF(COUNTIF(C$3:C12,C12)>1,0,1) B12 =IF(ROWS(A$3:A12)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A12),A:A,0))) A13 =A12+IF(COUNTIF(C$3:C13,C13)>1,0,1) B13 =IF(ROWS(A$3:A13)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A13),A:A,0))) A14 =A13+IF(COUNTIF(C$3:C14,C14)>1,0,1) B14 =IF(ROWS(A$3:A14)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A14),A:A,0))) A15 =A14+IF(COUNTIF(C$3:C15,C15)>1,0,1) B15 =IF(ROWS(A$3:A15)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A15),A:A,0))) A16 =A15+IF(COUNTIF(C$3:C16,C16)>1,0,1) B16 =IF(ROWS(A$3:A16)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A16),A:A,0))) A17 =A16+IF(COUNTIF(C$3:C17,C17)>1,0,1) B17 =IF(ROWS(A$3:A17)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A17),A:A,0))) A18 =A17+IF(COUNTIF(C$3:C18,C18)>1,0,1) B18 =IF(ROWS(A$3:A18)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A18),A:A,0))) A19 =A18+IF(COUNTIF(C$3:C19,C19)>1,0,1) B19 =IF(ROWS(A$3:A19)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A19),A:A,0))) A20 =A19+IF(COUNTIF(C$3:C20,C20)>1,0,1) B20 =IF(ROWS(A$3:A20)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A20),A:A,0))) A21 =A20+IF(COUNTIF(C$3:C21,C21)>1,0,1) B21 =IF(ROWS(A$3:A21)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A21),A:A,0))) A22 =A21+IF(COUNTIF(C$3:C22,C22)>1,0,1) B22 =IF(ROWS(A$3:A22)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A22),A:A,0))) A23 =A22+IF(COUNTIF(C$3:C23,C23)>1,0,1) B23 =IF(ROWS(A$3:A23)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A23),A:A,0))) A24 =A23+IF(COUNTIF(C$3:C24,C24)>1,0,1) B24 =IF(ROWS(A$3:A24)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A24),A:A,0))) A25 =A24+IF(COUNTIF(C$3:C25,C25)>1,0,1) B25 =IF(ROWS(A$3:A25)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A25),A:A,0)))

Excel tables to the web >> Excel Jeanie HTML 4


Thanks in advance.




Hi I am new to this forum so apologies if I am not posting in the right way.
I am trying to setup an formula to look for an invoice number across 10 bank transaction sheets (different currencies) and return the amount paid.
The formula needs to search the first currency sheet e.g. gbp, then the next e.g usd etc etc and return the amount paid if it finds the invoice number.

The IFERROR works referring to 2 sheets but I am having trouble adding any more sheets to it. $A$3:$F$10 is the range on each bank sheet to search

=IFERROR(INDEX(usd!$A$3:$F$10,MATCH(B3,INDEX(usd!$A$3:$F$10,0,4),0),5),INDEX(eur!$A$3:$F$10,MATCH(B3,INDEX(eur!$A$3:$F$10,0,4),0),5))

I have tried adding ,IFERROR (obviously changing the sheet refs) and &IFERROR as well but it keeps saying error or too many arguments.

=IFERROR(INDEX(usd!$A$3:$F$10,MATCH(B3,INDEX(usd!$A$3:$F$10,0,4),0),5),INDEX(eur!$A$3:$F$10,MATCH(B3,INDEX(eur!$A$3:$F$10,0,4),0),5)),IFERROR(INDEX(aud!$A$3:$F$10,MATCH(B3,INDEX(aud!$A$3:$F$10,0,4),0),5),INDEX(nzd!$A$3:$F$10,MATCH(B3,INDEX(nzd!$A$3:$F$10,0,4),0),5))

This is probably really simple but any help is much appreciated.

Many thanks

First, I want to say Thank You again to everybody for all the help & patience. Is there a source somewhere with sample formulas? The formula wizard is only helpful for single functions, not if I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.

I got some great help on indexing a range from another spreadsheet, but when I tried to use it again on a different range & copied & pasted and changed the ranges, I get #REF!, which I know means I screwed something up, I'm just not sure what & I don't know what the 1's & 0's mean.

=INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))

Thanks once again. If any of the guru's need help with their golf game, let me know & I'm happy to reciprocate!!!!!


I am trying to build a formula by adding "=" and the text of an Index,Match,Match formula. The latter is stored on a separate sheet and I am building the formula on another. I have tried a number of ways with no luck. The cell ends up showing =(Index, Match, Match) rather than running the formula.

Thanks for your help.

Eric


I'm in the process of redoing a spreadsheet as it runs very slowly. I'm after advise on which formulas are the quickest/slowest as the spreadsheet contains a fair number.

1. Should I be using vlookup or index/match?

2. I have a number of sumproduct formulas are these quicker than using sumif?

3. I use indirect in some formulas which picks up a sheet name. There are 12 sheets which are identical in format. Would it be better to paste the data from the appropriate one into a master sheet. I can then use a simple formula rather than including the indirect formula.

This is an example of one of the formulas I'm using

HTML Code:

=IF(Options!$E$9="Summary",1,INDEX('Fill'!$B$5:$CC$24,MATCH($B$7,'Fill'!$A$5:$A$24,0),MATCH(C$5,'Fill'!$B$3:$CC$3,0)))


Any advise on which are the quickest/slower formulas would be appreciated.


My brain is frozen right now and I cant figure out what I am doing wrong.

I want to look up value week 1, match the person, and return the value. From there I was going to do some addition and dividing of diffrent columns. Here is the chart:

week1 LECKENBY, MCKINSEY 45 468.77 24.17 444.6 419.57 82 week1 ARTHUR, YEVETTE 40 1837.47 108.25 1729.22 1627.66 367 week1 JARMIN, SARA 13 1514.83 71.39 1443.44 1355.49 281

This formula gives me the sum and division I want, but it does not look up week1

=(INDEX($B$3:$M$31,MATCH($P22,$B$3:$B$31,0),4)+INDEX($B$3:$M$31,MATCH($P22,$B$3:$B$31,0),6))/INDEX($B$3:$M$31,MATCH($P22,$B$3:$B$31,0),7)

For some reason I can't get this formula to return a value, it is just a #ref error

=VLOOKUP(Q21,$A$3:$H$31,INDEX(B3:H31,MATCH(P22,B3:B31,0),4),1)

Any ideas how to combine the 2 formulas into one would be great.

I am using Windows 7 and Excel 2010

Thanks


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!


I'm using the following code to add up the values in the cells of the intersections between a variable row and the 7th, 28th, 49th and 70th columns of a sheet .......

=SUM(VALUE(INDEX(INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:CJ400"),MATCH($A$1,INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:A400"),0),7)),VALUE(INDEX(INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:CJ400"),MATCH($A$1,INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:A400"),0),28)),VALUE(INDEX(INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:CJ400"),MATCH($A$1,INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:A400"),0),49)),VALUE(INDEX(INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:CJ400"),MATCH($A$1,INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:A400"),0),70)))

it's a compilation of a single formula (this one only finds the value of the intersection of a variable row and the 7th column ....

=SUM(VALUE(INDEX(INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:CJ400"),MATCH($A$1,INDIRECT("'"&$C$1&"Year11Maths"&RIGHT($F$1,1)&"Term1Unit"&$E7&"'!A4:A400"),0),7))

The existing formula is far too large as I have about 500 of these cells with this formula and it is likely to slow the computing process unecessarily.

Is there a way I can reduce the length of the formula?


Hey all,

I am trying to record a macro to insert a Formula Array but It doesnt work. I think that the Formula is too big, is there an easy way to split it as i have a lot of formulas, or is there another way to do it?

Thanks

Tom

Code:

Selection.FormulaArray = _
"=SUM(((INDEX('CONTROL SHEET'!1:9999,2,(MATCH("TS",'CONTROL SHEET'!1:1,0)))):(INDEX('CONTROL SHEET'!1:9999,((MATCH(TRUE,INDEX(ISBLANK(INDEX('CONTROL SHEET'!$1:$60000,0,(MATCH("INVAC",'CONTROL SHEET'!$1:$1,0)))),0,0),0))-1),(MATCH("TS",'CONTROL SHEET'!1:1,0))))="E")*(((INDEX('CONTROL SHEET'!1:9999,2,(MATCH("T2M_INVDT",'CONTROL SHEET'!1:1,0)))):(INDEX('CONTROL SHEET'!1:9999,((MATCH(TRUE,INDEX(ISBLANK(INDEX('CONTROL SHEET'!$1:$60000,0,(MATCH("INVAC",'CONTROL SHEET'!$1:$1,0)))),0,0),0))-1),(MATCH("T2M_INVDT",'CONTROL SHEET'!1:1,0)))))="")*((INDEX('CONTROL SHEET'!1:9999,2,(MATCH("DTC",'CONTROL SHEET'!1:1,0)))):(INDEX('CONTROL SHEET'!1:9999,((MATCH(TRUE,INDEX(ISBLANK(INDEX('CONTROL SHEET'!$1:$60000,0,(MATCH("INVAC",'CONTROL SHEET'!$1:$1,0)))),0,0),0))-1),(MATCH("DTC",'CONTROL SHEET'!1:1,0))))<>"")*((INDEX('CONTROL SHEET'!1:9999,2,(MATCH("T2M_FEE",'CONTROL SHEET'!1:1,0)))):(INDEX('CONTROL SHEET'!1:9999,((MATCH(TRUE,INDEX(ISBLANK(INDEX('CONTROL SHEET'!$1:$60000,0,(MATCH("INVAC",'CONTROL SHEET'!$1:$1,0)))),0,0),0))-1),(MATCH("T2M_FEE",'CONTROL SHEET'!1:1,0))))))"





Dear All,
I do have a Warehouse Management System(WMS) in excel with formulae as follows.
Could somebody help me with the formulae which can yield the desired result as shown in Column I?

Sheet1 (2)

  A B C D E F G H I 1 Article Bin Location Bin Qty Unique Article Identifier Indent Article Number Indent Qty All BIN Locations All Bin Qty Proposed Result 2 2213469 AA1 1 2213469-1 2213468 4 AF1/BF3// 2/1// AF1-2/BF3-1// 3 2213468 AF1 2 2213468-1 2213465 3 FE2/GH6/HI7/ 2/1/3/ FE2-2/GH6-1/HI7-3/ 4 2213468 BF3 1 2213468-2           5 2213464 DF6 4 2213464-1           6 2213465 FE2 2 2213465-1           7 2213465 GH6 1 2213465-2           8 2213465 HI7 3 2213465-3          
Spreadsheet Formulas Cell Formula D2 =A2&"-"&SUMPRODUCT(--($A$2:A2=A2)) G2 =IF(ISBLANK(E2),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1))) H2 =IF(ISBLANK(E2),"",IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1))) D3 =A3&"-"&SUMPRODUCT(--($A$2:A3=A3)) G3 =IF(ISBLANK(E3),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-4"),D:D,FALSE),1))) H3 =IF(ISBLANK(E3),"",IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E3,"-1"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E3,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E3,"-2"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E3,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E3,"-3"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E3,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E3,"-4"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E3,"-4"),D:D,FALSE),1))) D4 =A4&"-"&SUMPRODUCT(--($A$2:A4=A4)) G4 =IF(ISBLANK(E4),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-4"),D:D,FALSE),1))) H4 =IF(ISBLANK(E4),"",IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E4,"-1"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E4,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E4,"-2"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E4,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E4,"-3"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E4,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E4,"-4"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E4,"-4"),D:D,FALSE),1))) D5 =A5&"-"&SUMPRODUCT(--($A$2:A5=A5)) G5 =IF(ISBLANK(E5),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-4"),D:D,FALSE),1))) H5 =IF(ISBLANK(E5),"",IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E5,"-1"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E5,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E5,"-2"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E5,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E5,"-3"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E5,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E5,"-4"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E5,"-4"),D:D,FALSE),1))) D6 =A6&"-"&SUMPRODUCT(--($A$2:A6=A6)) G6 =IF(ISBLANK(E6),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-4"),D:D,FALSE),1))) H6 =IF(ISBLANK(E6),"",IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E6,"-1"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E6,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E6,"-2"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E6,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E6,"-3"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E6,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E6,"-4"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E6,"-4"),D:D,FALSE),1))) D7 =A7&"-"&SUMPRODUCT(--($A$2:A7=A7)) G7 =IF(ISBLANK(E7),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-4"),D:D,FALSE),1))) H7 =IF(ISBLANK(E7),"",IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E7,"-1"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E7,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E7,"-2"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E7,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E7,"-3"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E7,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E7,"-4"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E7,"-4"),D:D,FALSE),1))) D8 =A8&"-"&SUMPRODUCT(--($A$2:A8=A8)) G8 =IF(ISBLANK(E8),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E8,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E8,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E8,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E8,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E8,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E8,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E8,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E8,"-4"),D:D,FALSE),1))) H8 =IF(ISBLANK(E8),"",IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E8,"-1"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E8,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E8,"-2"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E8,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E8,"-3"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E8,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(C:C,MATCH(CONCATENATE(E8,"-4"),D:D,FALSE),1)),"",INDEX(C:C,MATCH(CONCATENATE(E8,"-4"),D:D,FALSE),1)))

Excel tables to the web >> Excel Jeanie HTML 4




Hi All,

so i am using the following formula...
=IF(P15A1C="TRUE","\"&ROUND(INDEX(INDIRECT($C5),DG$2,1),2),"")

which matches up to this :
=IFERROR(MATCH(DG3,Room_Mix,0),"-")

in DG3 it has 5A1C

I have used this formula for the first 31 cells and it has worked fine... using the same formula just changing the row number for the index and obviously the match cell to pull what i want.

However once i get past 31 it just returns #REF however i am using the EXACT same format and formulas for the first 31..

If i change the match in DG3 to one of the previous 31 it pulls through that correct value so i know it isnt the formula as such, but im not sure what it could be?


Any ideas?

I have a workbook with worksheets named by peoples names. There are cells in my summary sheet that contain all the names of the worksheets (or peoples names).

Each worksheet has a table in it that I am trying to search one column to find a specific name and then report if there is text in another column in the same row. If I use the following formula:

=IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!f10"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!h10"))))=TRUE,1,0)

It works and I'll return a "1" for cells in column F that have text and a "0" for columns that don't.

However, I am trying to sum up the cells in F that have text and when I enter:

{=SUM(IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!F10:F200"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!H10:H200")))),1,0))}

it returns "1" when that should not be the answer. Am I doing something with the array in my INDIRECT formula? I used this similar application for another purpose in this workbook and refereced the array through INDIRECT the same way (but didn't use MATCH or INDEX) and it worked fine. This was the formula:

{=IF(SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0))>0,SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0)),"-")}

Any help would be appreciated. Thanks!


Dear All,
The following is the sample WMS(with the formulae) which is being maintained & updated in excel.

I am looking for formulae in Column G which can return the count of BIN against the article number in column E.

For example the formula in G2 should return a value of 2 since the indented article number-2213468(Cell E2) is present in 2 different BINs,namely AF1 & BF3 in column B.

Similarly the formula in G3 should return a value of 1 since the indented article number-2213469(Cell E3) is present in 1 BIN namely AA1 in column B.

Could somebody help with the formula in column G which can return the desired result?

Thanks

Sheet1

  A B C D E F G 1 Article Bin Location Bin Qty Unique Article Identifier Indent Article Number All BIN Locations Remarks 2 2213469 AA1 1 2213469-1 2213468 AF1/BF3////// 2 3 2213470 AZ1 2 2213470-1 2213469 AA1/////// 1 4 2213468 AF1 3 2213468-1 2213465 GH6/HI7////// 2 5 2213468 BF3 1 2213468-2       6 2213465 GH6 1 2213465-1       7 2213465 HI7 1 2213465-2      
Spreadsheet Formulas Cell Formula D2 =A2&"-"&SUMPRODUCT(--($A$2:A2=A2)) F2 =IF(ISBLANK(E2),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-5"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-5"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-6"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-6"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-7"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-7"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-8"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-8"),D:D,FALSE),1))) D3 =A3&"-"&SUMPRODUCT(--($A$2:A3=A3)) F3 =IF(ISBLANK(E3),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-4"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-5"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-5"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-6"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-6"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-7"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-7"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E3,"-8"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E3,"-8"),D:D,FALSE),1))) D4 =A4&"-"&SUMPRODUCT(--($A$2:A4=A4)) F4 =IF(ISBLANK(E4),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-4"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-5"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-5"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-6"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-6"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-7"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-7"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E4,"-8"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E4,"-8"),D:D,FALSE),1))) D5 =A5&"-"&SUMPRODUCT(--($A$2:A5=A5)) F5 =IF(ISBLANK(E5),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-4"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-5"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-5"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-6"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-6"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-7"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-7"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E5,"-8"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E5,"-8"),D:D,FALSE),1))) D6 =A6&"-"&SUMPRODUCT(--($A$2:A6=A6)) F6 =IF(ISBLANK(E6),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-4"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-5"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-5"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-6"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-6"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-7"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-7"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E6,"-8"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E6,"-8"),D:D,FALSE),1))) D7 =A7&"-"&SUMPRODUCT(--($A$2:A7=A7)) F7 =IF(ISBLANK(E7),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-4"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-5"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-5"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-6"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-6"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-7"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-7"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E7,"-8"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E7,"-8"),D:D,FALSE),1)))

Excel tables to the web >> Excel Jeanie HTML 4




I currently use the following Index function, which works fine as it is:

=INDEX((XSD500_10_volts!$B$42:$M$47),(MATCH($D33,XSD500_10_volts!$B$42:$B$47,0)),(MATCH(E$32,XSD500_10_volts!$B$42:$M$42,0)))

I'm trying to get the sheetname to insert automatically from a cell ref (in this case A14)

I currently have the following:

=INDEX(((INDIRECT("a14")&"!$B$42:$M$47")),(MATCH($D42,(INDIRECT("a14")&"!$B$42:$B$47"),0)),(MATCH(E$32,(INDIRECT("a14")&"!$B$42:$M$42"),0)))

When running checks, the sheetname appears correctly in the Evaluate formula box, but the formula itself doesn't appear to know what to do with it & returns #VALUE.

Am I on the right lines with Indirect, or is it not suitable for what I'm trying to do?

Thanks

Jon

edit:

Just seen a new post which uses concatenate with the indirect function, which seems to work:

=INDEX((INDIRECT(CONCATENATE("'",$A$14,"'!$B$42:$M$47"))),(MATCH($D42,(INDIRECT(CONCATENATE("'",$A$14,"'!$B$42:$B$47"))),0)),(MATCH(E$32,(INDIRECT(CONCATENATE("'",$A$14,"'!$B$42:$M$42"))),0)))