Formula Help!!!!

[daniellepp2]

Hello,

I hope someone can help me with this issue.

1 sheet has all of my data. The other sheets have a custom template. I am trying to get Excel to populate a cell based on certain criteria. I have the formula....

=IF(AND( Data!B40>=DATE(2011,3,1), Data!B40<=DATE(2011,3,31)),Data!B40,"")

The above formula only works if the data sheet remains the same. If I add rows to the data sheet then the formula is not correct.

I am looking for a formula that looks at an entire column or worksheet and if it meets a certain date range then enter the row.

Does that make sense? Can anyone help?

Thank you very much,
D

 

[daniellepp2]

It seems I may have to use VLOOKUP. But I am struggling with the correct formula.

my data sheets change on a regular basis. I would need a formula that states if a date in Column B is in January then place the date in the cell. If the date in Column B is not in January then do nothing.

 

[wsautrey]

Can you place a snapshot of your sheet with the formulas on your post so I can get a better idea of what you might be looking for?

 

[Peter_SSs]

See if this is any use. It uses some helper columns (which can be hidden once they are populated).

Data sheet. Formulas in E2:F2 copied down.

Excel Workbook
	B	C	D	E	F
1	Dates
2	1/05/2011			May-2011	May-2011|1
3	23/04/2010			Apr-2010	Apr-2010|1
4	15/05/2009			May-2009	May-2009|1
5	28/05/2011			May-2011	May-2011|2
6	1/05/2011			May-2011	May-2011|3
7	2/06/2011			Jun-2011	Jun-2011|1
8
Data

Then for, say, May 2011 sheet. Formulas in A2 and D2 copied down. Any of columns B:D can be hidden if you want.

Excel Workbook
	A	B	C	D
1	Dates	May-2011	3	0
2	1/05/2011			1
3	28/05/2011			2
4	1/05/2011			3
5
May 2011

 

[daniellepp2]

Thanks for your help Peter.

I can't add anything to my spreadsheet because then it would be labor intensive.

I am exporting a yearly Outlook calendar to the spreadsheet. The appointments change all the time. If I begin to add columns and such it will be a lot of work.

Do you know if there is any way to do this without the helper columns?

Sheet1 is the data imported from Outlook
Sheet2 through Sheet13 are each months calendar in the year

So I would need a formula that states something like "look at Column A if the date is less than 2011,1,31 then add the data in that cell, if not then leave blank"

The formula I had written was working but not if my data changes because it may add rows and my formula is row specific.

=IF(AND( Data!B2>=DATE(2011,1,1), Data!B2<=DATE(2011,1,31)),Data!B2,""

 

[violet_universe]

Hello,

I hope someone can help me with this issue.

1 sheet has all of my data. The other sheets have a custom template. I am trying to get Excel to populate a cell based on certain criteria. I have the formula....

=IF(AND( Data!B40>=DATE(2011,3,1), Data!B40<=DATE(2011,3,31)),Data!B40,"")

The above formula only works if the data sheet remains the same. If I add rows to the data sheet then the formula is not correct.

I am looking for a formula that looks at an entire column or worksheet and if it meets a certain date range then enter the row.

Does that make sense? Can anyone help?

Thank you very much,
D

If you can place a snapshot of your sheet with the formulas on your post I think I can get a better idea of what you might be looking for? <!-- / message -->

 

[daniellepp2]

<style>table { }.font5 { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: "MS Sans Serif"; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-weight: 700; border: 0.5pt solid silver; }.xl66 { border: 0.5pt solid silver; }.xl67 { border: 0.5pt solid silver; }.xl68 { font-size: 11pt; font-family: Arial; text-align: center; border: 0.5pt solid silver; }.xl69 { font-size: 11pt; font-family: Arial; text-align: left; border: 0.5pt solid silver; }.xl70 { font-size: 11pt; font-family: Arial; border: 0.5pt solid silver; }ruby { }rt { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; display: none; }</style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="474"> <col span="4" width="61"> <col style="" width="169"> <col width="61"> <tbody><tr height="13"> <td colspan="4" height="13" width="244">SHEET 1 - IMPORTED OUTLOOK CALENDAR DATA</td> <td width="169"></td> <td width="61"></td> </tr> <tr height="13"> <td height="13"></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td class="xl65" height="13">Subject</td> <td class="xl65">StartDate</td> <td class="xl65">StartTime</td> <td class="xl65">EndTime</td> <td class="xl65">Description</td> <td class="xl65">Location</td> </tr> <tr height="13"> <td class="xl66" height="13">Meeting</td> <td class="xl67" align="right">1/4/11</td> <td class="xl68">9:00 AM</td> <td class="xl68">3:00 PM</td> <td class="xl69">Tinkerbell Pre-School Fundraiser</td> <td class="xl66">Office</td> </tr> <tr height="13"> <td class="xl66" height="13">Tour</td> <td class="xl67" align="right">2/5/11</td> <td class="xl68">9:00 AM</td> <td class="xl68">3:00 PM</td> <td class="xl70">GP Little League Assoc Sign-ups</td> <td class="xl66">Office</td> </tr> <tr height="13"> <td class="xl66" height="13">Meeting</td> <td class="xl67" align="right">3/9/11</td> <td class="xl68">9:00 AM</td> <td class="xl68">3:00 PM</td> <td class="xl69">Tinkerbell Pre-School Fundraiser</td> <td class="xl66">Office</td> </tr> <tr height="13"> <td class="xl66" height="13">Tour</td> <td class="xl67" align="right">4/10/11</td> <td class="xl68">9:00 AM</td> <td class="xl68">3:00 PM</td> <td class="xl70">GP Little League Assoc Sign-ups</td> <td class="xl66">Office</td> </tr> <tr height="13"> <td class="xl66" height="13">Meeting</td> <td class="xl67" align="right">5/12/11</td> <td class="xl68">9:00 AM</td> <td class="xl68">3:00 PM</td> <td class="xl69">Tinkerbell Pre-School Fundraiser</td> <td class="xl66">Office</td> </tr> </tbody></table>

<style>table { }.font5 { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: "MS Sans Serif"; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-weight: 700; border: 0.5pt solid silver; }.xl66 { border: 0.5pt solid silver; }.xl67 { border: 0.5pt solid silver; }.xl68 { font-size: 11pt; font-family: Arial; text-align: center; border: 0.5pt solid silver; }.xl69 { font-size: 11pt; font-family: Arial; text-align: left; border: 0.5pt solid silver; }.xl70 { font-size: 11pt; font-family: Arial; border: 0.5pt solid silver; }ruby { }rt { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; display: none; }</style>SHEET 2 - MONTHLY CALENDAR

 

[daniellepp2]

so the data on sheet 1 should be copied to sheet 2 if the date is January and so on for the other sheets with the corresponding months.

thanks in advance for your help.

 

[Peter_SSs]

I am exporting a yearly Outlook calendar to the spreadsheet.

1. How are you doing that?

2. What versions of Outlook/Excel are you using?

 

[daniellepp2]

in Outlook you can export your calendar to other programs. I'm using 2007 but other previous versions had this capability as well. File, Export, then choose excel. All data is exported to an Excel sheet.

I hope someone out there can still help me with a solution to this issue. You think it may be a VLOOKUP feature?