Pulling data that matches a specific date

[Krix]

Hi,

I have this spreadsheet where in there's a Raw Data. Along with is there's a different tab for different user, is there a way that if a certain user choose a date it will pull up the data for that date in the Raw Data.

What formula should I use? and how should I do it.

Sorry if I can't explain it well but please do see the sample spreadsheet and you will notice what I really want.


PS :
Is it possible to do this without using VBA because I want to upload it in Google Spreadsheet.

Many thanks!

[FDibbins]

1st, put this in E1 in each workbook...
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Then use this ARRAY formula in A6, copied down and across for each worksheet...
=IFERROR(INDEX('Raw Data'!A$5:A$16,SMALL(IF('Raw Data'!$A$5:$A$16=$D$1,IF('Raw Data'!$C$5:$C$16=$E$1,ROW('Raw Data'!$A$5:$A$16)-ROW('Raw Data'!$A$5)+1)),ROW('Raw Data'!$A1)),1),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Krix]

Thank you for changing the subject.

I tried what you have said but it pulls up a different data. I'm wondering on this part of the code :
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Should I change something there? I have reattached the file again and added the codes and steps that you have said.

Can you please tell me where did I go wrong?


Many thanks!

[FDibbins]

Looks like you missed the ARRAY part that I mentioned (dont worry, even old hands make that mistake now and then). You need to enter those with CTRL SHIFT ENTER, not just enter. If you did it properly, your formula should be now be inside {}

in A6 on each sheet, press F2 (edit), then press CTRL SHIFT ENTER. Then copy down and across

[Krix]

Yeah! I thought it worked already when I pressed CTRL SHIFT ENTER but it didn't. Haha! Anyway, it's working now.

But one last request and I just forgot to add it, is it possible that I can pull up the data if its a week range (or most specifically a range of date)

For example, I want to pull out the data from May 5, 2014 until May 11, 2014.

Is that possible? Thank you very much for your help! A big thanks really this will help me alot.

[FDibbins]

Try adding to that formula (it's not as hard as it looks), you just need to add an extra date

=IFERROR(INDEX('Raw Data'!A$5:A$16,SMALL(IF('Raw Data'!$A$5:$A$16=$D$1,IF('Raw Data'!$C$5:$C$16=$E$1,ROW('Raw Data'!$A$5:$A$16)-ROW('Raw Data'!$A$5)+1)),ROW('Raw Data'!$A1)),1),"")

=IFERROR(INDEX('Raw Data'!A$5:A$16,SMALL(IF('Raw Data'!$A$5:$A$16>=$D$1,IF('Raw Data'!$A$5:$A$16<=$D$2,IF('Raw Data'!$C$5:$C$16=$E$1,ROW('Raw Data'!$A$5:$A$16)-ROW('Raw Data'!$A$5)+1))),ROW('Raw Data'!$A1)),1),"")

[Krix]

A BIG THANK YOU!!! How can I put SOLVED on this thread?

[FDibbins]

In the menu bar just above your 1st post, Thread Tools