Using a date range in a formula to pull info to the correct column
 

I have a spreadsheet set up with a column (a) with cost; (b) bill date and 12
columns for each month. I need a formula that will pull (a)the cost into the
correct month column based upon (b)the bill date which would be within the
range 1/1/07-1/31/07, etc for each month.
I tried so many different formulas but non seem to work...
=if (b) =1/1/07&1/31/07, (a),0

Using a date range in a formula to pull info to the correct column
 

Hi Tricia

If you use date in row 1 as column headings from B1 onward like
C1= 1/1/07, D1 =2/1/07 etc. and then format theses cells with
FormatCellsNumberCustom mmm
they will show as Jan, Feb, Mar etc.

Then in C2
=IF(MONTH($B2)=MONTH(C$1),$A2,"")
Copy across and down as necessary

--
Regards

Roger Govier


"Tricia" wrote in message
...
I have a spreadsheet set up with a column (a) with cost; (b) bill date
and 12
columns for each month. I need a formula that will pull (a)the cost
into the
correct month column based upon (b)the bill date which would be within
the
range 1/1/07-1/31/07, etc for each month.
I tried so many different formulas but non seem to work...
=if (b) =1/1/07&1/31/07, (a),0

Using a date range in a formula to pull info to the correct co
 

Hi Roger,
There is other info involved in the spreadsheet. What I want the formula to
do is when I key in the bill date, it will move the cost to the correct month
column where the costs will be calculated.

"Roger Govier" wrote:

Hi Tricia

If you use date in row 1 as column headings from B1 onward like
C1= 1/1/07, D1 =2/1/07 etc. and then format theses cells with
FormatCellsNumberCustom mmm
they will show as Jan, Feb, Mar etc.

Then in C2
=IF(MONTH($B2)=MONTH(C$1),$A2,"")
Copy across and down as necessary

--
Regards

Roger Govier


"Tricia" wrote in message
...
I have a spreadsheet set up with a column (a) with cost; (b) bill date
and 12
columns for each month. I need a formula that will pull (a)the cost
into the
correct month column based upon (b)the bill date which would be within
the
range 1/1/07-1/31/07, etc for each month.
I tried so many different formulas but non seem to work...
=if (b) =1/1/07&1/31/07, (a),0

Using a date range in a formula to pull info to the correct column
 

How do you have your 12 columns for the months identified? A date like
1/1/2007, 2/1/2007, 3/1/2007? A *TEXT* heading like Jan, Feb, Mar?

Biff

"Tricia" wrote in message
...
I have a spreadsheet set up with a column (a) with cost; (b) bill date and
12
columns for each month. I need a formula that will pull (a)the cost into
the
correct month column based upon (b)the bill date which would be within the
range 1/1/07-1/31/07, etc for each month.
I tried so many different formulas but non seem to work...
=if (b) =1/1/07&1/31/07, (a),0

Using a date range in a formula to pull info to the correct co
 

Biff,
I have them labeled as January, February, etc... the other columns are Cost
and Date Billed.
Thanks

"T. Valko" wrote:

How do you have your 12 columns for the months identified? A date like
1/1/2007, 2/1/2007, 3/1/2007? A *TEXT* heading like Jan, Feb, Mar?

Biff

"Tricia" wrote in message
...
I have a spreadsheet set up with a column (a) with cost; (b) bill date and
12
columns for each month. I need a formula that will pull (a)the cost into
the
correct month column based upon (b)the bill date which would be within the
range 1/1/07-1/31/07, etc for each month.
I tried so many different formulas but non seem to work...
=if (b) =1/1/07&1/31/07, (a),0

Using a date range in a formula to pull info to the correct co
 

I just read your reply to Roger. What you want to do can't be done with a
formula. You want to "move" the data. A formula can only "copy" the data.
You'll need some VBA code to "move" the data. I can't help you with that.

Biff

"Tricia" wrote in message
...
Biff,
I have them labeled as January, February, etc... the other columns are
Cost
and Date Billed.
Thanks

"T. Valko" wrote:

How do you have your 12 columns for the months identified? A date like
1/1/2007, 2/1/2007, 3/1/2007? A *TEXT* heading like Jan, Feb, Mar?

Biff

"Tricia" wrote in message
...
I have a spreadsheet set up with a column (a) with cost; (b) bill date
and
12
columns for each month. I need a formula that will pull (a)the cost
into
the
correct month column based upon (b)the bill date which would be within
the
range 1/1/07-1/31/07, etc for each month.
I tried so many different formulas but non seem to work...
=if (b) =1/1/07&1/31/07, (a),0

Using a date range in a formula to pull info to the correct co
 

Hi Tricia

The value from column A will be repeated in the column corresponding to
the month in column B.
Other columns will return null.
I thought from your original question, that was what you wanted to do.
If not, describe in more detail what you are trying to achieve.

--
Regards

Roger Govier


"Tricia" wrote in message
...
Hi Roger,
There is other info involved in the spreadsheet. What I want the
formula to
do is when I key in the bill date, it will move the cost to the
correct month
column where the costs will be calculated.

"Roger Govier" wrote:

Hi Tricia

If you use date in row 1 as column headings from B1 onward like
C1= 1/1/07, D1 =2/1/07 etc. and then format theses cells with
FormatCellsNumberCustom mmm
they will show as Jan, Feb, Mar etc.

Then in C2
=IF(MONTH($B2)=MONTH(C$1),$A2,"")
Copy across and down as necessary

--
Regards

Roger Govier


"Tricia" wrote in message
...
I have a spreadsheet set up with a column (a) with cost; (b) bill
date
and 12
columns for each month. I need a formula that will pull (a)the
cost
into the
correct month column based upon (b)the bill date which would be
within
the
range 1/1/07-1/31/07, etc for each month.
I tried so many different formulas but non seem to work...
=if (b) =1/1/07&1/31/07, (a),0