Vlookup on Dynamic Range that expands vertically and horizontally

**huy_le** · 08-26-2013, 06:13 PM

Hi guys,

I have a problem. I am working on the number of applications coming from different countries on each day. What I am trying to create is a summary of the number of applications from each day, from each country, and from each country in the last 7 days.

I have created a pivot table but since it is going to be cumulative, the date and the number of countries will expand. Now I am looking at a pivot table that will expand both horizontally and vertically. I don't know how to do vlookup on it. Unless you have a better idea.

I'm attaching the workbook here.

Thanks

**JBeaucaire** · 08-26-2013, 09:48 PM

Only thing that jumps to my eye is this:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Range("DynamicRange_Reject"), Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Rejected!R1C9", TableName:="PivotTable18", _
        DefaultVersion:=xlPivotTableVersion14

After fixing that, the PIVOT TABLE button seems to update properly each time it is pushed.

**huy_le** · 08-26-2013, 10:29 PM

Hi JBeaucaire,

Thank you for your reply. However, let's not worry about the macro because I was testing formulas and it messed up the macro. What I wanted to solve is different.

Let's look at the tab "Approve", you will see a pivot table with date, countries, and the count. Now go to tab "Total Applications", I want to be able to count the number of applications by specific countries and also by date. If I set a specific range, that would be easy. However, as time goes on there will be new data added to the source which would expand the pivot table (new dates, new countries). The question is, how can I do a vlookup (or anything to look up) based on a dynamic pivot table like that?

Do you understand my question? Please help because I really need it solved quickly.

Thanks.

**JBeaucaire** · 08-27-2013, 02:38 AM

Why macro at all? Put this formula in B19, then copy down/across the table:

=COUNTIFS(Approved!$B:$B, B$18, Approved!$D:$D, $A19)

**huy_le** · 08-27-2013, 03:47 PM

Hi JB,

Sorry work has caught up to me the whole morning. Anyway, your formula worked and I am so appreciative that your help came on time.

I will look forward to receiving more great tips from you.

Vlookup on Dynamic Range that expands vertically and horizontally

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Vlookup on Dynamic Range that expands vertically and horizontally

Re: Vlookup on Dynamic Range that expands vertically and horizontally

Re: Vlookup on Dynamic Range that expands vertically and horizontally

Re: Vlookup on Dynamic Range that expands vertically and horizontally

Re: Vlookup on Dynamic Range that expands vertically and horizontally

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Look into a range of cells (horizontally and vertically) and return a number

[SOLVED] Sumproduct vertically and horizontally

The text keeps writing vertically instead of horizontally?

Excel 2007 : Merging data horizontally vs. vertically

[SOLVED] Move cells vertically to horizontally

Bookmarks

Bookmarks

Posting Permissions