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]

The formulas on the Data sheet seem to work just fine. However, I can't seem to get any information to populate on my May sheet.

Would you mind explaining the following formulas so I can trouble shoot?...

<table border="1" cellpadding="2" cellspacing="0"><tbody><tr><td>C1</td><td>=COUNTIF(Data!E2:E1000,B1)</td></tr><tr><td>A2</td><td>=IF(D2="","",INDEX(Data!B$2:B$1000,MATCH(B$1&"|"&D2,Data!F$2:F$1000,0)))</td></tr><tr><td>D2</td><td>=IF(ROWS(D$2:D2)>C$1,"",D1+1)</td></tr></tbody></table>

I really appreciate your help. I feel I am so close to solving this.

 

[Peter_SSs]

Before I go into explaining the formulas, I want to just check why, in the post above, you posted just 3 out of the 4 formulas from the May sheet?

I ask in case you just entered a date directly in cell B1 and not the formula I suggested in post #4. B1 must contain text, not a date.

Also, are you getting absolutely nothing on the May sheet or are you getting errors or incorrect data etc?

Another thing to try to help get going is to set up a new workbook with just 2 sheets and data exactly the same as I had in post #4 and see if you can reproduce those results. If you can, then try adding a June 2011 sheet and populate it with similar formulas to the May sheet and see if you can get it to pull the one row af data that it should.

 

[daniellepp2]

Hi Peter,

On my spreadsheet I did not receive an error the fields were blank.

I copied the data exactly as you had it and it works. But somehow on my sheet nothing appears.

I also tested your data with data in subsequent columns and they did not show up on the May sheet. I am probably missing a code to enter the data in column C and D as well.

 

[Peter_SSs]

On my spreadsheet I did not receive an error the fields were blank.

If you have entered the formulas that I suggested that cannot be so for all fields (unless maybe you have the text formatted the same colour as the cell background ).

For example (in the May sheet of your workbook that is apparently not working):

B1 formula: ="May-2011"
That cannot produce a blank, it must return exactly what is in the "".
1. Did you use this formula?
2. Is it in cell B1? (If not what cell is it in?)

C1 formula: =COUNTIF(Data!E2:E1000,B1)
This cannot produce a blank. It could produce 0, but it must return a number.
3. Which cell do you have this formula in?
4. What number is it returning?

But somehow on my sheet nothing appears.

So something is different between your sheets and mine - we just have to track down what that is and make adjustments for the differences.

I also tested your data with data in subsequent columns and they did not show up on the May sheet. I am probably missing a code to enter the data in column C and D as well.

We should be able to fix that once we get something working. Might need a bit more detail about which columns of data have to come from where to where.

5. You posted a shot of the Data sheet in post #7. Is that data in columns A:F?


I hadn't previously looked at the other image you linked to in post #7. It is very small but if you want your calendar in Excel in a grid like the left hand side of that image then that would be very difficult using only formulas.

 

[daniellepp2]

<style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-bidi-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>

Would you mind terribly if I send you my document so you can look at my formula placement? This is really keeping me up at night.


The document is in WikiSend



http://wikisend.com/download/


ID code #491304​

 

[Peter_SSs]

I'm sorry but many helpers here, including me, choose not to receive files from other members or download from other sites. There are a number of reasons including ..

- Having done so in the past it often turns out that the workbook/worksheet is quite complex and takes a lot of time to become familiar with. (If so, it may mean the problem is really too complex for a free public forum like this.)

- It tends to take the onus off the 'asker' to distil their problem to something clear and concise and becomes more "Here's my workbook, can you figure out what I'm trying to do and fix it?"

- It defeats the purpose of the forum being a public forum.

- It probably breaches rule #7 of the Forum Rules.

Unless your data or requirements have changed dramatically since the early part of the thread, we should be able to make some progress if you answered the 5 fairly simple questions I asked in my last post.

 

[daniellepp2]

I do sincerely apologize. I just thought it was a quick way for you to see where I placed the formulas and tell me what I did wrong. My bad

If you would not mind explaining the last set of formulas that need to be on the May sheet I can understand what it is trying to do and figre it out on my own. If I can't figure this out then I will either seek out a site where I pay someone or scrap this whole project and try to use a custom calendar template program which works with Outlook.

Thank you very much for all of your help so far.

 

[Peter_SSs]

If you would not mind explaining ...

OK, but I'm going to change layout and formulas a bit. I think this may be closer to what you want.

First the 'Data' sheet, with data copy/pasted here from your calendar export. I've changed you data a bit so we have a few entries for May, including a couple on the same day. Note that my dates in column B are in d/mm/yy format.

Copy the formulas down as far as you might ever need. Once you get this working (or even now) you can hide these columns so the "Jan-1900" values where there is no data on the left won't be a problem.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Subject	StartDate	StartTime	EndTime	Description	Location
2	Meeting	1/04/11	9:00 AM	3:00 PM	Tinkerbell Pre-School Fundraiser	Office				Apr-2011	Apr-2011|1
3	Tour	2/05/11	9:00 AM	3:00 PM	GP Little League Assoc Sign-ups	Office				May-2011	May-2011|1
4	Meeting	3/04/11	9:00 AM	3:00 PM	Tinkerbell Pre-School Fundraiser	Office				Apr-2011	Apr-2011|2
5	Tour	4/05/11	9:00 AM	3:00 PM	GP Little League Assoc Sign-ups	Office				May-2011	May-2011|2
6	Meeting	4/05/11	4:00 PM	4:30 PM	Tinkerbell Pre-School Fundraiser	Office				May-2011	May-2011|3
7										Jan-1900	Jan-1900|1
Data

The formula in J2 takes the date in B2, removes the day from the date and returns a text value for month and year.

The Formula in K2 makes a progressive count of each of the month values in column J.
So the "May-2011|2" in cell K5 is telling us that this row is the second May-2011 row so far.


Now the 'May' sheet.
I've changed the layout here too.
A1 is a stand-alone formula.
A2 contains a 0, manually entered.
A3 formula copied down as far as you might ever need.
Once you get this working (or even now) you can hide column A.

B1 is a stand-alone formula (not a date)

B2:G2 are headings copied/pasted from the 'Data' sheet.

Formula in B3 is copied across to G3. After doing this copy the formatting of the time columns (and possibly the date column) may not be correct. If that is the case, either format the cells manually to the correct format or you could use the format painter to copy the formatting from a row in the 'Data' sheet.

Once you have the formatting correct, select B3:G3 and copy these formulas down to the same row you copied the column A formulas to.

Excel Workbook
	A	B	C	D	E	F	G
1	3	May-2011
2	0	Subject	StartDate	StartTime	EndTime	Description	Location
3	1	Tour	2/05/11	9:00 AM	3:00 PM	GP Little League Assoc Sign-ups	Office
4	2	Tour	4/05/11	9:00 AM	3:00 PM	GP Little League Assoc Sign-ups	Office
5	3	Meeting	4/05/11	4:00 PM	4:30 PM	Tinkerbell Pre-School Fundraiser	Office
6
May 2011

Formula in B1: Simply gets a text entry for the month/year into that cell.

Formula in A1: Looks at column J in the 'Data' sheet and counts how many entries match the value in cell B1 of this sheet. In this case, how many "May-2011" values there are (3).

Formula in A3 (copied down): Numbers the rows until the value in cell A1 is reached.

Formula in B3:
=IF($A3="","", ..
If nothing in A3 then we don't need anything in this column. But there is something in A3 so ..

$B$1&"|"&$A3 = May-2011|1

MATCH($B$1&"|"&$A3,Data!$K$1:$K$1000,0) becomes
MATCH(May-2011|1,Data!$K$1:$K$1000,0)
This means look in column K of 'Data' and find what row "May-2011|1" is on. Result is row '3'.

INDEX(Data!A$1:A$1000,MATCH($B$1&"|"&$A3,Data!$K$1:$K$1000,0)) becomes
INDEX(Data!A$1:A$1000,3)
This means return the value from row 3 of column A in 'Data'
Result is 'Tour'

As this formula is copied across it returns values from different columns of the 'Data' sheet.

Hope all that makes some sense.

 

[Haseeb Avarakkan]

Hello Daniel,

Jan!I2, enter this formula,

=TEXT(DATE(2011,MONTH(1&A2),1),"mmm-yyyy")

J2,

=COUNTIF(Data!F2:F1000,I2)

I3:M3, Enter the headings. Date | Subject | Start | End | People.

I4, copy down & across.

=IF(ROWS(I$4:I4)<=$J$2,INDEX(Data!A:A,MATCH($I$2&"|"&ROWS(I$4:I4),Data!$G:$G,0)),"")

Change the cel format to suit.

Select I2:M33 then copy & paste to other tabs.

 

[daniellepp2]

Thank you very much Peter! This is really helpful.