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
 

daniellepp2

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

New Member
Joined
Apr 2, 2011
Messages
21
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,169
Office Version
365
Platform
Windows
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/2011
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
1Dates 0
2
328/05/20112
41/05/20113
5
May 2011
 

daniellepp2

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

violet_universe

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

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

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

daniellepp2

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

Forum statistics

Threads
1,081,518
Messages
5,359,237
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top