Formula Help!!!!

daniellepp2

New Member
Joined
May 6, 2011
Messages
16
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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?
 
Upvote 0
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
BCDEF
1Dates
21/05/2011May-2011May-2011|1
323/04/2010Apr-2010Apr-2010|1
415/05/2009May-2009May-2009|1
528/05/2011May-2011May-2011|2
61/05/2011May-2011May-2011|3
72/06/2011Jun-2011Jun-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
ABCD
1DatesMay-201130
21/05/20111
328/05/20112
41/05/20113
5
May 2011
 
Upvote 0
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,""
 
Last edited:
Upvote 0
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 -->
 
Upvote 0
<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

62ccf7eec8bc243c6f3daff90ead2d47f1e782f1deacca5eef0e373ac39628bb6g.jpg

moz-screenshot.png
moz-screenshot-1.png
moz-screenshot-2.png
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top