Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function/sum function | Excel Discussion (Misc queries) | |||
date formula | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
insert date | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |