Referencing a cell for a row

[tonydepo]

Ok. I am going to try to explain what I need.


A B C D
1 Month D
2
3
4 Current JAN FEB
5 Sales 300 200 300
6 Expenses 50 100 50


Ok, every month I need to have the current month column equal the
current month. Lets say Feb is my current month, so I need b5 and b6
to relate to the Feb column which is d5 and d6. The current formula in
b5 is =c5. Every month I need to go thru every cell in the B column and
change it to the next current month, for example, Lets say I just got
down doing the month of Jan. The cell in b5 is =c5. I am now doing
Feb so I have to go into B5 and change the field to =d5. As you can
see this can be time consuming if I have a number of categories. I was
thinking it has to be possible to just change b1 and it would relate to
that column. I am not sure how to do it though. So every month I
would just change b1 and it would relate to the correct month to use
for the Current column.

Any help would be great..

Thanks

Tony

 

[bj]

one of the ways to do it would be to use the indirect function
somewhere AA1?
enter
=choose(Month(today(),"C","D","E" and so on)

in your current month column enter your equations as
=indirect($AA$1&row())

Needless to say there are multiple other ways to do it.
Vlookups, Index and match combinations etc.

 

[tonydepo]

Thanks BJ.. the second part worked fine. But the first part is giving
me an error with the formula. I can select the column by using the
appropriate letter and it changes my current column, but I cant make
the letter into a month name like you suggested during your first part
of the solution. I placed this formula in my cell

=choose(Month(today(),"C","D","E" and so on)


without the and so on part.

Thanks for your help

 

[bj]

Thats because I left off a parenthesis
=choose(Month(today()),"C","D","E" and so on)

 

[sanskar_d]

Ok. I am going to try to explain what I need.


A B C D
1 Month D
2
3
4 Current JAN FEB
5 Sales 300 200 300
6 Expenses 50 100 50


Ok, every month I need to have the current month column equal th
current month. Lets say Feb is my current month, so I need b5 and b
to relate to the Feb column which is d5 and d6. The current formula i
b5 is =c5. Every month I need to go thru every cell in the B column an
change it to the next current month, for example, Lets say I just go
down doing the month of Jan. The cell in b5 is =c5. I am now doin
Feb so I have to go into B5 and change the field to =d5. As you ca
see this can be time consuming if I have a number of categories. I wa
thinking it has to be possible to just change b1 and it would relate t
that column. I am not sure how to do it though. So every month
would just change b1 and it would relate to the correct month to us
for the Current column.

Any help would be great..

Thanks

Tony

Hi Tony,

I tried your example...
Unfortunately I am unable to understand the problem correctly...
But, I think that you could use "max" function & typing the first da
of the month to retrieve the data. (try using 1-feb-2005 & in forma
cell type "mmm" in Category>Number>Custom>Type).
Then use the formula.

Incase the problem is not solved you can mail me o
(e-mail address removed)

I would be glad to solve the problem.

regards,

sa

 

[Aladin Akyurek]

Ok. I am going to try to explain what I need.


A B C D
1 Month D
2
3
4 Current JAN FEB
5 Sales 300 200 300
6 Expenses 50 100 50


Ok, every month I need to have the current month column equal the
current month. Lets say Feb is my current month, so I need b5 and b6
to relate to the Feb column which is d5 and d6. The current formula in
b5 is =c5. Every month I need to go thru every cell in the B column and
change it to the next current month, for example, Lets say I just got
down doing the month of Jan. The cell in b5 is =c5. I am now doing
Feb so I have to go into B5 and change the field to =d5. As you can
see this can be time consuming if I have a number of categories. I was
thinking it has to be possible to just change b1 and it would relate to
that column. I am not sure how to do it though. So every month I
would just change b1 and it would relate to the correct month to use
for the Current column.

Any help would be great..

Thanks

Tony

B5, copied down:

=LOOKUP(9.99999999999999E+307,C5:IV5)