Referencing a cell for a row
 

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


--
tonydepo
------------------------------------------------------------------------
tonydepo's Profile: http://www.excelforum.com/member.php...o&userid=15306
View this thread: http://www.excelforum.com/showthread...hreadid=381912

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" wrote:


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


--
tonydepo
------------------------------------------------------------------------
tonydepo's Profile: http://www.excelforum.com/member.php...o&userid=15306
View this thread: http://www.excelforum.com/showthread...hreadid=381912

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


--
tonydepo
------------------------------------------------------------------------
tonydepo's Profile: http://www.excelforum.com/member.php...o&userid=15306
View this thread: http://www.excelforum.com/showthread...hreadid=381912

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

"tonydepo" wrote:


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


--
tonydepo
------------------------------------------------------------------------
tonydepo's Profile: http://www.excelforum.com/member.php...o&userid=15306
View this thread: http://www.excelforum.com/showthread...hreadid=381912

tonydepo Wrote:
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





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 day
of the month to retrieve the data. (try using 1-feb-2005 & in format
cell type "mmm" in CategoryNumberCustomType).
Then use the formula.

Incase the problem is not solved you can mail me on


I would be glad to solve the problem.

regards,

sam


--
sanskar_d
------------------------------------------------------------------------
sanskar_d's Profile: http://www.excelforum.com/member.php...o&userid=24217
View this thread: http://www.excelforum.com/showthread...hreadid=381912

tonydepo wrote:
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)