Can't figure out INDEX function code

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
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.

:confused:
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top