Hi, I'm working on an Excel 2003 spreadsheet that looks at a list of outstanding jobs and compares with a wallplanner showing when maintenance is next due in a building. It contains 2 worksheets: 'Wallplanner' and 'Outstanding Jobs.'

The formula is:

=INDEX(Wallplanner!A:AL,MATCH('Outstanding Jobs'!A2,Wallplanner!A:A,0)-1,1+MATCH(TRUE,INDEX(Wallplanner!$B2:$AL2<>"",,),0))

The formula works perfectly for the top row on the spreadsheet but when I use the fill handle to copy down the rest of the column, I get incorrect answers on all the rows below. I have also tried the copy and paste method but get the same result. How could I edit this formula to make it copy down correctly? Could it have something to do with the dollar signs? I've never used a formula this complex and every change I've made has either had no effect or ruined it.

If it helps, here's what the worksheets look like...

The Wallplanner worksheetsheet:

Asset Number Date Date Date etc...

2001 4m

2002 12m

2003 4m

2004 6m

2005 4mThe Outstanding Jobs worksheet:

Asset Description Maintenance Next Due Date

2001 Job A Formula goes in this column

2002 Job B

2003 Job C

2004 Job D

2005 Job E

2006 Job F

2007 Job GThe formula is supposed to look at each job in the Outstanding Jobs worksheet, then search for a matching asset number on the Wallplanner worksheet. Then it looks along the row for the first non-blank cell and then up the column from the non-blank cell at the date in the column header. This date shows when the maintenance is next due. The date should appear next the job in the Outstanding Jobs worksheet.

I'm very stuck and urgently need to get this out of the way as it's holding me back. Any help you can give is greatly appreciated.

Thanks.

✔ Best Answer

please can you explain in stages how the formula works?This is going to be a bit complicated.

The complete formula is:

=INDEX(Wallplanner!$A$1:$E$6,1,MATCH(TRUE,INDEX(Wallplanner!$B2:$E2<>"",,),0)+1)

First:

The INDEX() function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns.

The function can have a syntax of:

INDEX(ARRAY, Row_Num, [Column_Num])

In your formula, the ARRAY is the range of cells: Wallplanner!$A$1:$E$6

We already know that the Row_Num has to be the first row. That is where your Dates are located and that's where we want to pick the data from.

So the Row_Num is hard coded to Row 1.Your Column_Num is partly hard coded into the formula, we know the row because your Asset Numbers are all sorted in the same way:

2001 on both sheets is on Row 2

2002 on both sheets is on Row 3

2003 on both sheets in on Row 4

etc.This makes the formula less robust, because if the Asset Numbers were NOT arraigned in the order they are, the formula falls apart.

To determine what column to pick, along the row, the MATCH() function is used.

The MATCH() function searches for a specified item in a range of cells, and then returns the

relative positionof that item in the range.The function can have a syntax of:

MATCH(Lookup_Value, Lookup_Array, [Match_Type])

In your formula, the Looup_Value is the string TRUE because the Lookup_Array is a nested INDEX() function that is testing for a NULL cell

INDEX(Wallplanner!$B2:$E2<>"",,) this will return the location of the first NON blank cell, as a TRUE of FALSE string.

In the case of the B2:E2 Array the return value for the first NON black cell is 2, remember the 2 is a

relataive positionwithin the ARRAY, not the actual column number.The Match_Type is 0, because we are looking for an Exact Match, so that gives us a MATCH function of:

MATCH(TRUE,INDEX(Wallplanner!$B4:$E4<>"",,),0)

To get the

actualcolumn C, which is the 3rd column, we must add 1 at the end of the MATCH() so we get 2 + 1 equals 3 or Column CMATCH(TRUE,INDEX(Wallplanner!$B4:$E4<>"",,),0)+1

It is a bit complicated, especially the Nested INDEX().

Any more questions please feel free to ask.

MIKE

message edited by mmcconaghy

It's hard to tell how your data is laid out since you didn't include any Column letters or Row numbers. Please click on the following line and read the instructions found via that link, then repost your example data. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Sorry - I'm new to all this. Hopefully this will clear things up...

Wallplanner looks like this:A B C D E 1 Asset No. 18/11/13 25/11/13 02/12/13 09/12/13 2 2001 4m 3 2002 12m 4 2003 4m 5 2004 6m 6 2005 4mOutstanding Jobs worksheet looks like this:

A B C 1 Asset No. Work Description Maintenance Next Due Date 2 2001 Job A Formula goes in this column 3 2002 Job B 4 2003 Job C 5 2004 Job D 6 2005 Job EThanks :-)

message edited by Clare1234

Not sure why the formula you posted worked,

cause I could not get it working, but try this:With your data as shown in post # 2

On worksheet Outstanding Jobs, Cell C2 enter the formula:

=INDEX(Wallplanner!$A$1:$E$6,ROW()-MATCH('Outstanding Jobs'!A2,Wallplanner!$A$1:$A$6,0)+1,MATCH(TRUE,INDEX(Wallplanner!$B2:$E2<>"",,),0)+1)

Drag down to C6.

I have modified your Ranges to a more manageable length.

Using entire columns as a range simply wastes processing,

unless you really do have data on all 65,536 rows.MIKE

Here's a shorter version that seems to work also: =INDEX(Wallplanner!$A$1:$E$6,1,MATCH(TRUE,INDEX(Wallplanner!$B2:$E2<>"",,),0)+1)

MIKE

Hey, Mike! It's worse than you think...

Excel 2007 and beyond:

Worksheet size 1,048,576 rows by 16,384 columns

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you! It works!

Just for future reference, please can you explain in stages how the formula works? Chances are I'll be asked to modify the table in future which will probably mess with the formula, so it could be useful to understand it better.Many thanks. :-)

please can you explain in stages how the formula works?This is going to be a bit complicated.

The complete formula is:

=INDEX(Wallplanner!$A$1:$E$6,1,MATCH(TRUE,INDEX(Wallplanner!$B2:$E2<>"",,),0)+1)

First:

The INDEX() function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns.

The function can have a syntax of:

INDEX(ARRAY, Row_Num, [Column_Num])

In your formula, the ARRAY is the range of cells: Wallplanner!$A$1:$E$6

We already know that the Row_Num has to be the first row. That is where your Dates are located and that's where we want to pick the data from.

So the Row_Num is hard coded to Row 1.Your Column_Num is partly hard coded into the formula, we know the row because your Asset Numbers are all sorted in the same way:

2001 on both sheets is on Row 2

2002 on both sheets is on Row 3

2003 on both sheets in on Row 4

etc.This makes the formula less robust, because if the Asset Numbers were NOT arraigned in the order they are, the formula falls apart.

To determine what column to pick, along the row, the MATCH() function is used.

The MATCH() function searches for a specified item in a range of cells, and then returns the

relative positionof that item in the range.The function can have a syntax of:

MATCH(Lookup_Value, Lookup_Array, [Match_Type])

In your formula, the Looup_Value is the string TRUE because the Lookup_Array is a nested INDEX() function that is testing for a NULL cell

INDEX(Wallplanner!$B2:$E2<>"",,) this will return the location of the first NON blank cell, as a TRUE of FALSE string.

In the case of the B2:E2 Array the return value for the first NON black cell is 2, remember the 2 is a

relataive positionwithin the ARRAY, not the actual column number.The Match_Type is 0, because we are looking for an Exact Match, so that gives us a MATCH function of:

MATCH(TRUE,INDEX(Wallplanner!$B4:$E4<>"",,),0)

To get the

actualcolumn C, which is the 3rd column, we must add 1 at the end of the MATCH() so we get 2 + 1 equals 3 or Column CMATCH(TRUE,INDEX(Wallplanner!$B4:$E4<>"",,),0)+1

It is a bit complicated, especially the Nested INDEX().

Any more questions please feel free to ask.

MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History