Nesting VLOOKUP in IF/vice versa & Pivot Table

[jbritt609]

I've been wrestling with this (& ransacking these boards) for way too long now ... I'm "taking the first step" & admitting I need help.

I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).
>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.
>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).

Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).

My problem is twofold:
1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.
I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).

2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.

I am at a total loss, but I know this can be done somehow & I'm probably just missing it. Thanks!

[mubashir aziz]

Try this formula which will work perfectly .... you can use Vlook instead of Index/Match as well .............

For Cat 1:

B3=IF(ISERROR(INDEX('Feb Cat1'!B:B,MATCH(A3,'Feb Cat1'!A:A,0))),0,INDEX('Feb Cat1'!B:B,MATCH(A3,'Feb Cat1'!A:A,0)))+IF(ISERROR(INDEX('Jan Cat1'!B:B,MATCH(A3,'Jan Cat1'!A:A,0))),0,INDEX('Jan Cat1'!B:B,MATCH(A3,'Jan Cat1'!A:A,0)))

For Cat2:

C3=IF(ISERROR(INDEX('Feb Cat2'!B:B,MATCH(A3,'Feb Cat2'!A:A,0))),0,INDEX('Feb Cat2'!B:B,MATCH(A3,'Feb Cat2'!A:A,0)))+IF(ISERROR(INDEX('Jan Cat2'!B:B,MATCH(A3,'Jan Cat2'!A:A,0))),0,INDEX('Jan Cat2'!B:B,MATCH(A3,'Jan Cat2'!A:A,0)))

[Justin Young]

Mubashir's solution will work but it will not be correct if there is more than a single occurence of each code on a worksheet. I have a solution that is shorter, calculates more quickly and requires less maintenance. First, you set up your workbook so that you already have the month/category sheets in place. My solution requires a number of named ranges and I have set up the ranges and worksheets as far as April - to demonstrate the solution. The formula used is:

Please Login or Register  to view this content.

See the attached for the implementation.

[mubashir aziz]

Nice work Justine ......

[DonkeyOte]

Another alternative is to group the monthly sheets such that each replicates the master ID list in an identical range... each using SUMIF to populate totals, then you can use a standard SUM(sheet:sheet!cellreference).

This is less elegant than the INDIRECT approach but it's big advantage is that it is not Volatile (see link in Sig re: Volatility).

It's not really clear as to whether or not you intend to have multiple summary tabs (1 for each month), if you do this approach may not be viable and you will need to adopt the INDIRECT approach regardless.

[jbritt609]

WOW. This is amazing--thank all three of you very much!! I do need to have a monthly YTD summary sheet for each month in the workbook. For example (just to be totally clear):
Workbook by January has:
Jan-Cat1, Jan-Cat2, Jan-YTD Summary: Cat1, Cat2 & Total (just Jan data)

Workbook by February has:
Jan-Cat1, Jan-Cat2, Jan-YTD Summary (from above), Feb-Cat1, Feb-Cat2, Feb-YTD Summary: Cat1, Cat2 & Total (sums of Jan & Feb data)

Workbook by March has:
Jan-Cat1, Jan-Cat2, Jan-YTD Summary (from above), Feb-Cat1, Feb-Cat2, Feb-YTD Summary: Cat1, Cat2 & Total (sums of Jan & Feb data), Mar-Cat1, Mar-Cat2, Mar-YTD Summary: Cat1, Cat2 & Total (sums of Jan, Feb & Mar data)
(and so on through the year...)

Thus far I think Justin's solution best meets my needs for the monthly structure and updating... but after reading the volatility link you provided, Donkey, that does make me nervous. So I have some additional questions(thank you all, again, SO much):
1. Is it correct that the volatility risk only applies if changes are made to the sheets/cells that are linked or related to the formulas? ie, if no one changes the prior month's data, is there still some type of volatility risk? If not, (because I trust myself with the workbook & editing it), if I were to just lock/password protect the sheets & workbook, would that remove the volatility risk? (aside from my own human error )

2.If there is still volatility risk, or even regardless, would it be possible/feasible to accomplish what I'm trying to do with pivot tables? I'm much less familiar with them, but what I'm wondering is if I could create an additional master (raw) data tab with 3 columns (Model, Cat1, Cat2), dump the data into it each month, & then create a pivot table to sum by model (rows) for Cat1, Cat2 & Total, which I could then refresh each month (a plus being any new models would automatically be entered/updated in the table by refreshing)... BUT my caveat here is how would I preserve the summary data for prior months. Can pivot tables/tabs easily be copied & pasted, or changed to include additional months? & would this even be a reasonable option compared to what I've already have here?

3. Just so my understanding is clear (because I feel like the more I learn about excel, the more clueless I also become), none of these solutions update the models in the master summary, right? that would still need to be done by hand? (if so, it's no big deal for me)

Thanks again!

[DonkeyOte]

Re: Volatility

This isn't a "risk" per se it simply means that a Volatile function will always recalculate whenever XL calculates ... XL has a clever calculation engine such that it can determine which cells need to be calculated at any given point in time, those not requiring calculation are simply ignored... the exceptions are Volatiles - these are constantly recalculated regardless of whether or not they need to be -- some functions do obviously need to be updated constantly by their very nature: NOW(), TODAY(), RAND() for ex. ... others seemingly less so: OFFSET, INDIRECT

To illustrate

Please Login or Register  to view this content.

If you alter A1 to say 15 both B1 & B2 will be flagged as requiring calculation ... B2 though not impacted by the change to A1 will recalculate given INDIRECT is a Volatile function.

Altering A2 will also enforce a recalculation of B2 but B1 will not recalculate given it is not a Volatile function and does not reference A2.

Obviously this is not a big deal if you have a handful of Volatile functions but as you can see by this example Volatile functions mean potentially unnecessary calculation overheads in your workbook as you're giving XL more work to do every time a calculation is invoked - some of which could potentially be avoided.

The issue of Volatile functions is amplified if the Volatile formula is an Array (includes Sumproduct) - Array's have a lot more work to do than a standard function - if then they are used in a Volatile context (ie SUMPRODUCT with INDIRECT / OFFSET etc...) XL is having to do all the Array work over and over and subsequently calculation performance is most likely going to be affected (adversely & potentially significantly).

A workaround is obviously to switch such files to Manual Calculation such that you control as and when XL recalculates but this can leave files open to error (IMO) and it is often best to avoid the initial problem that necessitate manual calc... that's not to say it's always possible to run on Auto - I wouldn't claim it is but I'm making the point that Manual mode shouldn't be first port of call for slow files.

Why don't you post up a version of your file with say a few months of dummy data and all the relevant sheets so we can see exactly what you're trying to produce ?

Your idea of a PT may well be viable if the monthly data is stored akin to a database in one contiguous range on one sheet with a column denoting month on each transaction.

Pivot Tables are ideal for aggregation work and offer ridiculous amounts of reporting power & flexibility for very little effort... I personally believe they should be the first thing any XL user is taught / learns -- if only to illustrate the importance and gains to be had from organising data in a succinct logical manner.

I will be offline for the next few days but I'm sure Justin Young and mubashir aziz (and others) will look to assist you further ... I will look back in next week and try to help further if required (and if I can!)

[mubashir aziz]

Re: Volatility
I will be offline for the next few days but I'm sure Justin Young and mubashir aziz (and others) will look to assist you further ... I will look back in next week and try to help further if required (and if I can!)

@DK who ?? me to assist okay okay i'll try my level best ....

anyway have a nice long long weekend / offdays

[jbritt609]

To clarify/possibly figure out the very last piece of the puzzle, I've attached an updated sample spreadsheet w/ what I've got thus far. My last remaining question is what can I do so that the (new) models would automatically update monthly? Could create/convert this into a pivot table? Or if there's any other way to do that? Or is it not worth the effort?

Thanks!!

[Justin Young]

I have cheated! For my own selfish reasons, I have re-formatted your approach to the problem. I realise that you are probably seeking a solution to an already established model, but its often the case that the spreadsheet design does not lend itself readily to simple and direct solutions.

If its possible I think you would benefit from a re-design. Take a look at the attached. Depending upon your level of familiarity with DSUM, Query Tables, SQL and Data Tables....you may wish to carefully follow the instructions.

This design would allow you to easily generate YTD reports and update model numbers in those reports without needing macros or tricky formulae. I intend to use my solution as they basis for an article so, if its possible, I would love to know some of the background as to what you are doing (what is the context?) and why your spreadsheet is set up as it is.

[Justin Young]

There is a solution that does not require a single formula or macro and that does not require you to rearrange data!!! All you need to do is give a range name to each of the data sets - i.e. JanCat1, JanCat2 and so on.... and then when you wish to create a month summary sheet use Data | Consolidation.

The nice thing about Data | Consolidation is that it extracts the model numbers from the ranges you wish to consolidate - and pastes only one instance of each number in the consolidation range. Effectively, it updates the model numbers 'automatically' as you require.

The steps to create a summary sheet would then be as follows:

1. Name the JanCat1,JanCat2,FebCat1,FebCat...etc ranges (include the headings but not the totals)
2. For a February summary (a) Select a blank cell in the middle of nowhere (b) choose Data | Consolidate...
3. In the Reference: box press F3 to paste the JanCat1 name into it.
4. Choose Add to add the name to the All references: box
5. Repeat above step for JanCat2, FebCat1, FebCat2
6. Select the Use labels in Top row, Left column check boxes
7. Choose OK.

See the attached to get a feel for how to do this. Of course, the major drawback is that if you later change the data - you have to repeat Data | Consolidation to refresh the summary report. Fortunately, Excel remembers the consolidation ranges.