Hi
I am hoping someone can help me.
Basically i have an existing sheet within excel named Money. It has the Months Jan01 - Jan30 in columns so goes from A - AZ....
On a seperate sheet i simply want to reference these months so the formula would be say in my new sheet and within cell A1 the following;
=Money!A$2 (so selecting Money tab, column A, row 2. This works perfectly.
However, now i want to pull down this formula to the next row which produces exactly the same;
=Money!A$2, this means i need to change manually the A to a B and so on! Looking for some advice on how i can do this easier?
I know i could create the AA - AZ in a seperate column using something like the following '=IF(RIGHT($A1,1)="Z", CHAR(CODE(LEFT(A1,1))+1),LEFT(A1,1))&CHAR(65+MOD(CODE(RIGHT(A1,1))+1-65,26))
So say i have AA in a field A1 using above method. I could then create my formula to make =Money<Reference to A1(AA)$2, not sure if this is possible or makes sense?
Anyway hoping you can understand what i am trying to do and i would really appreciate your help, the transpose works perfect if it was actual figures but when dealing with formula its more difficult, although this scenario in Excel i would have thought would be quite common. Thanks in advance!
P
I am hoping someone can help me.
Basically i have an existing sheet within excel named Money. It has the Months Jan01 - Jan30 in columns so goes from A - AZ....
On a seperate sheet i simply want to reference these months so the formula would be say in my new sheet and within cell A1 the following;
=Money!A$2 (so selecting Money tab, column A, row 2. This works perfectly.
However, now i want to pull down this formula to the next row which produces exactly the same;
=Money!A$2, this means i need to change manually the A to a B and so on! Looking for some advice on how i can do this easier?
I know i could create the AA - AZ in a seperate column using something like the following '=IF(RIGHT($A1,1)="Z", CHAR(CODE(LEFT(A1,1))+1),LEFT(A1,1))&CHAR(65+MOD(CODE(RIGHT(A1,1))+1-65,26))
So say i have AA in a field A1 using above method. I could then create my formula to make =Money<Reference to A1(AA)$2, not sure if this is possible or makes sense?
Anyway hoping you can understand what i am trying to do and i would really appreciate your help, the transpose works perfect if it was actual figures but when dealing with formula its more difficult, although this scenario in Excel i would have thought would be quite common. Thanks in advance!
P