Dynamic Data

[last90]

I am trying to find a way of gathering data for the last 90 days that will update itself.

I am running one sheet (DUTIES) which lists data from January 1st 2013 to the present day. Column A lists dates, columns B to M represent 12 individuals. Three types of data are listed under each individual; LV, MM or JJ (or blank) on each day they work.

I want to display how many MM and JJ events each individual has conducted over the last 90 days, in a cell on a separate sheet (SUMMARY).

It would also be nice if I could enter a given day in a cell and have the outputs on the SUMMARY sheet show how many MM and JJ duties were conducted in the preceding 90 day period.

I'm an Excel newbie so please type slowly!

Thanks

 

[Aladin Akyurek]

Hi guys,
Yes, as I said I'm new at this

I can get the formula to give me a range over the last 90 days by selecting it with the mouse, but I don't seem to be able to write the formula so that it automatically updates from the O23 cell that refers to the current date. I can see that the "O23" and the "O23-90" will give me the right range, but can I ask what the function of the $ and & characters are?

What is the current range from which you want last 90 records or records of last 90 days?

 

[sijpie]

Two totally different things.
Take a clean spreadsheet. In cell A1 write Jan and use the cell handle to copy it down ( you should see Feb, Mar, etc till Dec)

Now in cell C1 write =A1
It will show Jan
Take the cell handle and drag down C1 to C12 you will see all the months again. Select C6 and it will read in the formula bar: =A6
So when you copied the formula down, excel automatically increased the row numbers.

Ok in E1 to E6 put some numbers. In G1 put 1.37 which is the exchange rate for euro to dollars.
Now in F1 to F6 we want the dollar amount for the euro figures in column E.
So put in F1 =E1*g1
Now copy the formula down. What happened? All the others turned to 0! Look at the formula in F5, it reads =E5*G5. Not what we wanted.
Go back to F1 and change the formula to: =e1*g$1
Now copy it down again, and there you are with the correct outcome. Look at F5, it now reads =E5*G$1
So by putting in the $ we fixed the row number of the formula. (Made it absolute)
If you put the $in front of the letter, then the column is made absolute, for when you want to copy sideways.
A handy tip: in G4 enter =a4, but before you hit enter, press the F4 key a few times. See how excel cycles the address through the various absolute/relative options.

Ok, now the &
In cell h3 or so enter =a1&a2
It will now read: JanFeb
You have added two words to make one new string. Concatenate is what it is called. Try: =a1&" "&e5&"Euros"
You can also use the concatenate function: =concatenate(a2," ",f6,"dollars")

 

[last90]

Thanks guys!

I'll have a play with my current spreadsheet again and get back to you….there must be a simple answer as to why the formula doesn't run.

Aladin, the current range is B326:B416, C326:416, D326:416, etc, (on the DUTIES sheet) all the way through to M326:M416. The 12 columns (B to M) represent 12 individuals. Each column lists each individuals activity in that range, but I need to send a summary of the last 90 days to separate rows (one for each individual) on the SUMMARY sheet. 326 to 426 represent the last 90 days as of todays date. Tomorrow the row references will be 327 to 427, and so on.

 

[Aladin Akyurek]

Thanks guys!

I'll have a play with my current spreadsheet again and get back to you….there must be a simple answer as to why the formula doesn't run.

Aladin, the current range is B326:B416, C326:416, D326:416, etc, (on the DUTIES sheet) all the way through to M326:M416. The 12 columns (B to M) represent 12 individuals. Each column lists each individuals activity in that range, but I need to send a summary of the last 90 days to separate rows (one for each individual) on the SUMMARY sheet. 326 to 426 represent the last 90 days as of todays date. Tomorrow the row references will be 327 to 427, and so on.

Where do you have the dates? And are this dates sequential and ascending?

 

[sijpie]

Ah, found the mistake: Apparently Countif() does not like the string we create inside the formula

So in Z4 put : ="DUTIES!B"&Z3-90&":B"&Z3

the formula in the table should then be: =COUNTIF(INDIRECT(Z4),"MM")

For my summary sheet I have the following

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
2		John	Carol	George					John	Carol	George
3	MM	17	12	15				143	B	C	D
4	JJ	15	12	14					DUTIES!B53:B143	DUTIES!C53:C143	DUTIES!D53:D143
SUMMARY

Cell Formulas
Range	Formula
B3	=COUNTIF(INDIRECT(I$4),$A3)
B4	=COUNTIF(INDIRECT(I$4),$A4)
C3	=COUNTIF(INDIRECT(J$4),$A3)
C4	=COUNTIF(INDIRECT(J$4),$A4)
D3	=COUNTIF(INDIRECT(K$4),$A3)
D4	=COUNTIF(INDIRECT(K$4),$A4)
H3	=MATCH(MAX(DUTIES!A:A),DUTIES!A:A)
I4	="DUTIES!"&I3&$H$3-90&":"&I3&$H$3
J4	="DUTIES!"&J3&$H$3-90&":"&J3&$H$3
K4	="DUTIES!"&K3&$H$3-90&":"&K3&$H$3

 

[last90]

Success!

Thanks guys for your help. All works perfectly. What an awesome forum.

Last question…..

If each individual has to complete 3 activities in 90 days to maintain their recency, how would I go about finding the date that their recency expires?

 

[sijpie]

I don't think you can find that easily with formulas. But with a little user defined function it is easy.

Press Alt-F11 to open the VBA editor. In the top left panel you see your workbook. Right click on it and select Insert... Module
In the right pane that now opened paste the following function:


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> GetRecLostDate(rUser <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> Date<br><SPAN style="color:#007F00">' Get the date 90 days from 3rd last entry for the user</SPAN><br><SPAN style="color:#007F00">' Assumes Date column in column A:A</SPAN><br><SPAN style="color:#007F00">' Use: GetRecLostDate(Duties!C2)</SPAN><br><SPAN style="color:#007F00">' to get the last date user in clumn C has to complete</SPAN><br><SPAN style="color:#007F00">' another activity</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> wsWS <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> lCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lLast <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, l3d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> dtDate <SPAN style="color:#00007F">As</SPAN> Date<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsWS = rUser.Parent<br>    <br>    <SPAN style="color:#00007F">With</SPAN> wsWS<br>        <SPAN style="color:#007F00">' get last entry for the user</SPAN><br>        lLast = .Cells(Rows.Count, rUser.Column).End(xlUp).Row<br>        <SPAN style="color:#007F00">' from here find the 3rd activity</SPAN><br>        lR = lLast<br>        <SPAN style="color:#00007F">Do</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> .Cells(lR, rUser.Column) <> vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                lCnt = lCnt + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            lR = lR - 1<br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> lCnt < 3 And lR > 1<br>        <br>        <SPAN style="color:#007F00">'get the date from the 3rd last activity</SPAN><br>        dtDate = .Cells(lR + 1, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    GetRecLostDate = dtDate + 90<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>


OK now go back to the spreadsheet, to your Summary sheet.
In a cell below the first user type
=getreclostdate( (you should already see that Excel offers you the function)
now go to the duties sheet and point to a cell in the column for that user. Then close the bracket )
and you will see a large number appear. Format it as a date. Then copy across for the other users.

You will need to save your sheet as a .xlsm file (Excel 2007+)

 

[last90]

Ahh…OK, I'm running on a Mac, using Excel for Mac 2011 version 14.3.9
Is there somewhere else to find the VBA editor? I had a look around but can't find it.

 

[sijpie]

No, at some point Apple told Microsoft to stop with the vba editor, as the Aplle script language is powerful enough. Really thought through that argument...

But I believe the latest version of Excel on the Mac at least runs the macros again, but you will need to borrow someone's PC (shock!) and put the macro in it.

good luck

 

[last90]

No problem. I'm happy with what I've achieved with your help so far.

Thanks everyone one for your help!