Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Dynamic "table" lookup in Excel 2007/10

Hi guys -

I'm not sure if Excel can do this - nor what it's 'technically' called, so maybe you can help.

I have a table in excel of data and dates. Well what I want to do is take a dynamic range of that data, like say between 1/1/2010 and 7/3/2010 and then evaluate that on another worksheet. I've been using COUNTIFS(...) to get what I want - but I haven't figured out how to make that be a filtered range on a dataset so I've been manually deleting the dataset down to what I want and going from there.

Is there a way possible in Excel to do this, or do I manually have to modify my table size every time?

Thanks!
0
rmm2001
Asked:
rmm2001
  • 5
  • 5
  • 2
1 Solution
 
gowflowCommented:
yes its possible but you need to post a workbook first to know exactly what you want.
gowflow
0
 
rmm2001Author Commented:
So get column A B C where C is between 2/1/2010 and 5/1/2010 ... but where that range can be dynamic (where I can modify it on the formula bar...something like that)
Book1.xlsx
0
 
neothwinCommented:
pls try with filtering.
filter.png
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rmm2001Author Commented:
No. That was not what I asked. I know how to filter - but that doesn't help when I'm trying to reference it within another function - like COUNTIFS

So I need like COUNTIFS(Sheet1!$A:A$, $A1, Sheet1$C:$C, here's where I don't know... BETWEEN 2/1/2010 and 5/1/2010????) and so on.

I know how to filter - I just need to know how to do it via a formula - if that can be done. Can it?
0
 
gowflowCommented:
Is this what your looking for ?
Pls make sure your macros are enabeled (office icon, excel options, Trust Center, Trust Center Settings, Macro Settings and make sure the last option is ticked Enable all macros (you can change this at any time later just for testing this macro)
Load the file attached and run the command button 'Produce Results in Given Time Interval' and see the result and advise if this is what you want.

I can modify / cater to your needs.
gowflow
Book1.xlsm
0
 
gowflowCommented:
just saw your reply
So I need like COUNTIFS(Sheet1!$A:A$, $A1, Sheet1$C:$C, here's where I don't know... BETWEEN 2/1/2010 and 5/1/2010????) and so on.

can you ellaborate its not clear.
gowlfow
0
 
rmm2001Author Commented:
Hmm... not a macro.

A formula.

Is that possible or is a macro the only way to go? Is this the extent of Excel? (I'm newish to it)
0
 
gowflowCommented:
pls read my last post if your reply was for me
gowflow
0
 
rmm2001Author Commented:
Say I want to count the number of people that appeared in a table between a given date range... that's what I'm trying to do. (If that makes sense)
0
 
neothwinCommented:
how about using two CountIF
COUNTIF(C2:C18,">2/1/2010")-COUNTIF(C2:C18,">10/1/2010")
0
 
gowflowCommented:
I read your post over and over and seems you need to flag only the items that are in between a certain date range ? Try this file and play with the dates in green and blue
gowflow
Book1.xlsx
0
 
rmm2001Author Commented:
That's awesome! I didn't know I could do that.

Thanks!!!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now