Solved Excel 2003 formula won't copy down a column correctly

October 22, 2013 at 07:01:01
Specs: Windows XP
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 4m

The 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 G

The 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.


See More: Excel 2003 formula wont copy down a column correctly

Report •


✔ Best Answer
October 25, 2013 at 05:45:31
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 position of 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 position within 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 actual column 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 C

MATCH(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

http://www.skeptic.com/

message edited by mmcconaghy



#1
October 22, 2013 at 13:03:33
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.


Report •

#2
October 23, 2013 at 01:20:58
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              4m

Outstanding 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 E

Thanks :-)

message edited by Clare1234


Report •

#3
October 24, 2013 at 16:08:51
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

http://www.skeptic.com/


Report •

Related Solutions

#4
October 24, 2013 at 16:24:20
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

http://www.skeptic.com/


Report •

#5
October 24, 2013 at 17:40:55
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.


Report •

#6
October 25, 2013 at 01:41:20
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. :-)


Report •

#7
October 25, 2013 at 05:45:31
✔ 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 position of 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 position within 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 actual column 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 C

MATCH(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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Ask Question