Can't figure out INDEX function code

[noppojp]

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.

 

[barry houdini]

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]

QUESTION ABOUT RANGE LIMITS:

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.

Any help you can provide would be great.

Kind regards

 

[Aladin Akyurek]

...

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]

ADVERTISEMENT

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]

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]

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.

 

[Aladin Akyurek]

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.