Fill Handle Problems (fill data from 2 rows)

Hey,

my last problem got solved with this Forum so I'll try my luck again. I'm working on a sheet for my university. Everything is working fine now, but i cant use the fill handle/ fill data option of Excel the way i want to.

=INDEX(INDIREKT("[HOURS.xlsm]PLAN!"&ADRESSE(9;3165+4*(SPALTE(A1)-1))&":"&ADRESSE(384;3165+4*(SPALTE(A1)-1)));VERGLEICH($A12;INDIREKT("[HOURS.xlsm]PLAN!"&ADRESSE(9;3164+4*(SPALTE(A1)-1))&":"&ADRESSE(384;3164+4*(SPALTE(A1)-1)));0))

This would be my first column. In the second Column I need some other ours but on the same day. I just realised ours are sometimes split. So the formula for the second row would look something like this:

=INDEX(INDIREKT("[HOURS.xlsm]PLAN!"&ADRESSE(9;3165+4*(SPALTE(B1)-1))&":"&ADRESSE(384;3165+4*(SPALTE(A1)-1)));VERGLEICH($A12;INDIREKT("[HOURS.xlsm]PLAN!"&ADRESSE(9;3164+4*(SPALTE(B1)-1))&":"&ADRESSE(384;3164+4*(SPALTE(A1)-1)));0))

So the A1 needs to change to B1 and it does if i only pull one row. If i start pulling two rows it changes to C1 immediatly. Why? and how do i get excel to do what i want :D.


Any suggestions?

thx for any help
Joel

When copied, A1 becomes B1 become C1 becomes D1......

Maybe use SPALTE($A1) so that does not happen.

Or copy it once, then use find and replace to change SPALTE( to SPALTE($

Or describe what you are trying to do and get a better formula

Since you are using (SPALTE(A1) - 1) - (COLUMN(A1) - 1) in English - you could use this in its place

INT((SPALTE(A1)-1)/2)

That will give you 0 0 1 1 2 2 (effectviely the same as A1 A1 B1 B1 C1 C1 but when you copy you will get A1 B1 C1 D1 E1 F1)

If that is not the pattern you need, post the pattern you want for the index of the copied formula...

Without seeing a sample file I can only guess that part of the reason that you are getting wrong numbers is that the second formula has mismatched cell references. =INDEX(INDIREKT("[HOURS.xlsm]PLAN!"&ADRESSE(9;3165+4*(SPALTE(B1)-1))&":"&ADRESSE(384;3165+4*(SPALTE(A1)-1)));VERGLEICH($A12;INDIREKT("[HOURS.xlsm]PLAN!"&ADRESSE(9;3164+4*(SPALTE(B1)-1))&":"&ADRESSE(384;3164+4*(SPALTE(A1)-1)));0))
It seems as if the letters that I have highlighted in red should be B's to match the un-highlighted SPLATE(B1)... parts.