I have the following simple Excel spreadsheet:
A B C D E F G H I J
1 Jan Feb Mar Apr May Jun Jul Aug
2 5.000 4.000 5.000 8.000 9.000 0
3
4 $C$2:$J$2
5
6
In Cell C4 I use the following formula to get the cell references from Row 2:
C4 = "$"&LEFT(ADRESS(1,COLUMN(C2),4),1+(COLUMN(C2)>26))&"$"&ROW(C2)&":$"&LEFT(ADRESS(1,COLUMN(J2),4),1+(COLUMN(J2)>26))&"$"&ROW(J2)
All this works fine so far.
However, my target ist now to make the cell reference more dynamically. As you can see in my example there is either a 0 or no number for Jun, Jul, Aug. Therefore, I also want that the cell reference is not going further then May (Cell G2). Once there is a number > 0 for June the cell reference should automatically go until Cell H2 and so on ...
I tried to go with the formula from the answer here. However, since I only have Office 2007 availalbe I cannot use a formula that contains the AGGREGAT function as in the answer in the link.
Is there any other solution for Office 2007?
Excel 2007 (US English version) does not have the LINKS or ADRESS functions. But an English version of the formula which will return the last address in row 2 that contains a value greater than 0:
= ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))
In your example that formula returns $G$2.
Examine HELP for the LOOKUP function for an explanation of how this works (when lookup_value is greater than any of the items in the array being tested).
I'm not sure if you want to detect or merely hard code the first address. To hard code, merely:
= "$C$2:" & ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))
or perhaps some variant of:
= ADDRESS(2,3) & ":" & ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))