# Can't figure out INDEX function code

#### noppojp

##### Board Regular
HELP - i can't figure out the G\$10

=IF(G\$6=0,"",INDEX('[T0205 FirstLast.xls]Time Sheet'!\$A:\$CJ,MATCH(\$C\$4,'[T0205 FirstLast.xls]Time Sheet'!\$D:\$D,0),G\$10))

Can ANYONE tell me what this means. i can't fiture it out.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### barry houdini

##### MrExcel MVP
The syntax of INDEX means that the first argument is your range

'[T0205 FirstLast.xls]Time Sheet'!\$A:\$CJ

the second argument determines the row within that range

MATCH(\$C\$4,'[T0205 FirstLast.xls]Time Sheet'!\$D:\$D,0)

and the third argument determines the column within that range

G\$10

INDEX then gives you the value where these intersect, so if G\$10 is 8, for instance, you'll be getting a value from column 8 of your range, i.e. column H, the row being determined by the MATCH function

#### noppojp

##### Board Regular

i'm not able to autofill any more. This is the first time in my experience with EXCEL that i'm not able to do this.

Is there a range limit that EXCEL has?

i'll give you what i have, and then tell you what i'm trying to do.

The formula i wanna copy across about 40 more times is:

=IF(NOT(\$A\$9=""),INDEX('F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!2:1229,MATCH(\$A\$9,'F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!\$A:\$A,0),90),"")

i basically want to add 40 more columns so that more projects can be added and time can be tracked to them.

i highlited the cell with the above formula, but the 90 will not change to 91 as i wish it to. Everything else in the formula should stay the same.

ANY IDEA what i can do to enable this final 90 to change to 91, & 92, etc.

HISTORY - > i've inherited a set of spreadhsheets to monitor people's time on projects. That's the purpose of the spreadsheet set. It came with 80 columns of projects, and i'd like to add more. i'm preparing for 2006, so i'm messing with it.

Kind regards

##### MrExcel MVP
noppojp said:
...

The formula i wanna copy across about 40 more times is:

=IF(NOT(\$A\$9=""),INDEX('F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!2:1229,MATCH(\$A\$9,'F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!\$A:\$A,0),90),"")

i basically want to add 40 more columns so that more projects can be added and time can be tracked to them.

i highlited the cell with the above formula, but the 90 will not change to 91 as i wish it to. Everything else in the formula should stay the same.

ANY IDEA what i can do to enable this final 90 to change to 91, & 92, etc.

...

In which cell is the first instance of the above formula that you want to copy across?

#### noppojp

##### Board Regular
Followup

Cell K10 i believe. i would have to get back to work to see it, but i believe that's the cell. There are about 80 projects. i believe i'm far from pressing the limits of EXCEL. It seems that it (the spreadsheet) was in some way blocked from having any more than that number of projects / columns. Perhaps i should send the file zipped to you (it's a big mutha). Free from viruses or spyware and all. i just need to know what to do about this limitation.

Cheers

#### noppojp

##### Board Regular
Follow-up....
NOT sure why i was not able to get an autofill. i had to go do it manually, as i just didn't see any other way.
Does Autofill have limitations, i.e., it is choosy about what it wants to fill?
Makes me sick to have had to do it manually.
Any thoughts on what would prevent Autofill from not wanting to increment the number above would be helpful.
Cheers from Tokyo.
CW

#### Peter_SSs

##### MrExcel MVP, Moderator
noppojp

With the formula as it is, I can see no reason why the 90 would change as you copy the formula across - it it just the number 90.

I haven't checked out the whole formula but if it starts in K10 and you want the 90 to increase by one each time you drag the formula one column to the right, then I suggest that you change the formula in K10 to this:
=IF(NOT(\$A\$9=""),INDEX('F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!2:1229,MATCH(\$A\$9,'F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!\$A:\$A,0),90+COLUMN()-COLUMN(\$K10)),"")
Now try dragging it across.

##### MrExcel MVP
Peter_SSs said:
noppojp

With the formula as it is, I can see no reason why the 90 would change as you copy the formula across - it it just the number 90.

I haven't checked out the whole formula but if it starts in K10 and you want the 90 to increase by one each time you drag the formula one column to the right, then I suggest that you change the formula in K10 to this:
=IF(NOT(\$A\$9=""),INDEX('F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!2:1229,MATCH(\$A\$9,'F:\directory\[Resource Allocation Planning.xls]Resource Allocation Plan'!\$A:\$A,0),90+COLUMN()-COLUMN(\$K10)),"")
Now try dragging it across.

Replace the IF(NOT(\$A\$9="") bit with IF(\$A\$9<>"" though.

Replies
12
Views
275
Replies
8
Views
257
Replies
8
Views
227
Replies
9
Views
275
Replies
2
Views
91

1,195,665
Messages
6,011,026
Members
441,579
Latest member
satishrazdhan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back