Conditional Lookup

A

AlanM

I am trying to develop a file for budgets/costs in a publishing environment.
The main variables are: title (any one of 7), print run (a range from 3,000
to 20,000) and pagination (ranges from 24 + cover to 120 + cover).

Currently the summary worksheet allows the user to select ‘title’, ‘print
run’ and ‘pagination’ data from 3 drop-down lists.

I need to now use the data selected from the lists to extract various costs
from ranges on other worksheets and put the figures into the appropriate
budget item cells.

For example, the ‘Printing & Binding’ cost item for a specific title would
be stored in a range as the relevant print price for a combination of print
run and page count, eg, printrun = 6000; page = 68; cost = $12,000. The
‘cost’ figure is what the user needs to see on the summary sheet.

I’m assuming each title would have its own Lookup range on a separate
worksheet.

Would I be best using a conditional Lookup for this kind of thing? And if
so, how would I create the formula? Or is there a smarter way to do it – I’ve
only ever used Excel for relatively simple things before this.
 
A

alanm

Hi Anne,

Thank you for the tutorial. I’m probably being dumb, but I don’t see how
that example could do what I’m trying to achieve. In fact, I now wonder if
the Lookup functions are really what I want or if I started down the wrong
road altogether.

If I understand Lookups correctly, they need a cell reference for each
‘item’ returned. I can’t provide that specific a reference. There would be
too many permutations.

I’m not precisely trying to do a similar function to a price list or an
order form. The file is for producing budgets for various magazines, each of
which has dozens of permutations of combinations of print runs, number of
pages etc, etc.

I started by creating 3 validated drop-down lists. One allows the user to
select the publication title (from a total of 7), the second uses a
conditional list to let the user choose the print run (which can range from
3,000 to 20,000 in 1,000 or 500 increments and varies depending on which
title they selected). The third (also conditional on title) allows them to
choose the pagination (number of pages, e.g., 64 + cover, 88 + cover etc,
etc).

The three results are presented at the top of the page. (e.g. ATM [title],
10,000 [Print Run], 88+ cover [Pagination]). With those results in place,
most of the calculated costs involved in budgeting (editorial, contributors,
design, production etc) are easy enough to get by summing various ranges on
other worksheets, and for the cost item total to appear automatically on the
summary sheet (the only one I want the end user to see).

I’m really stuck on returning two important costs though — printing and
mailing.

Both those cost elements have a lot of permutations (e.g., a print run of
8,000 for a magazine of 88 pages plus cover, or 6,000 for 120 + cover etc).
For each title there will be maybe 15 possible print runs and 15 possible
paginations, each combination of which has a different printing and binding
price. Mailing is similar, with variances in magazine weight, local, overseas
etc altering postage rates. I figure if I can get the ‘print cost’ function
to work though, the mailing will use a similar function.

I thought that if I created a series of ‘tables’ for each title on a ‘Print
Cost’ worksheet that I would be able to utilise the same nested IF formula I
used for the other costs, but combine it with a Lookup to get the correct
print price for each title/print run/size, based on the data in the cells
from the user-selected drop down lists.

Those tables would look like:
Print Run 64+ cover 72+ cover 80 + cover etc, etc, etc
6,000 $12,000 $14,000 $16,000 etc, etc, etc
7000 $18,000 $20,000 $22,000 etc, etc, etc

What I hoped to get Excel to do was to look at the summary page for the
‘Title’ ‘Print Run’ and ‘Pagination’ data and then look both down and across
the various ‘Print Cost’ tables (one for each title) and return the one
correct print price for that particular title/print/pagination.

If the Vlookup function will do that, I’m too stupid to see it. I’ve tried a
database DGET function but I can’t make that work either. I’m certain it’s
not as hard as it seems to me (somebody must have done something similar),
but maybe I should have approached the whole thing in a different way. I hope
not. Everything else works perfectly. I’d hate to start over again!

If anyone has any ideas I would be very grateful. My apologies for the
length of this post.

Alan M
 
A

Anne Troy

So you're not really looking up a distinct record, then? The tutorial
demonstrates a lookup for when you have one row for every possible scenario.
Your situation sounds more like you need a database, tho there are probably
many who could accomplish it in Excel. Deb Dalgleish has many examples here
that may help you:
http://www.contextures.com/tiptech.html
However, if it were ME, I would make a row for each the 64+, the 72+, etc...
Then you don't need a macro or fancy (heavy) formulas.
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

alanm said:
Hi Anne,

Thank you for the tutorial. I'm probably being dumb, but I don't see how
that example could do what I'm trying to achieve. In fact, I now wonder if
the Lookup functions are really what I want or if I started down the wrong
road altogether.

If I understand Lookups correctly, they need a cell reference for each
'item' returned. I can't provide that specific a reference. There would be
too many permutations.

I'm not precisely trying to do a similar function to a price list or an
order form. The file is for producing budgets for various magazines, each
of
which has dozens of permutations of combinations of print runs, number of
pages etc, etc.

I started by creating 3 validated drop-down lists. One allows the user to
select the publication title (from a total of 7), the second uses a
conditional list to let the user choose the print run (which can range
from
3,000 to 20,000 in 1,000 or 500 increments and varies depending on which
title they selected). The third (also conditional on title) allows them to
choose the pagination (number of pages, e.g., 64 + cover, 88 + cover etc,
etc).

The three results are presented at the top of the page. (e.g. ATM [title],
10,000 [Print Run], 88+ cover [Pagination]). With those results in place,
most of the calculated costs involved in budgeting (editorial,
contributors,
design, production etc) are easy enough to get by summing various ranges
on
other worksheets, and for the cost item total to appear automatically on
the
summary sheet (the only one I want the end user to see).

I'm really stuck on returning two important costs though - printing and
mailing.

Both those cost elements have a lot of permutations (e.g., a print run of
8,000 for a magazine of 88 pages plus cover, or 6,000 for 120 + cover
etc).
For each title there will be maybe 15 possible print runs and 15 possible
paginations, each combination of which has a different printing and
binding
price. Mailing is similar, with variances in magazine weight, local,
overseas
etc altering postage rates. I figure if I can get the 'print cost'
function
to work though, the mailing will use a similar function.

I thought that if I created a series of 'tables' for each title on a
'Print
Cost' worksheet that I would be able to utilise the same nested IF formula
I
used for the other costs, but combine it with a Lookup to get the correct
print price for each title/print run/size, based on the data in the cells
from the user-selected drop down lists.

Those tables would look like:
Print Run 64+ cover 72+ cover 80 + cover etc, etc, etc
6,000 $12,000 $14,000 $16,000 etc, etc, etc
7000 $18,000 $20,000 $22,000 etc, etc, etc

What I hoped to get Excel to do was to look at the summary page for the
'Title' 'Print Run' and 'Pagination' data and then look both down and
across
the various 'Print Cost' tables (one for each title) and return the one
correct print price for that particular title/print/pagination.

If the Vlookup function will do that, I'm too stupid to see it. I've tried
a
database DGET function but I can't make that work either. I'm certain it's
not as hard as it seems to me (somebody must have done something similar),
but maybe I should have approached the whole thing in a different way. I
hope
not. Everything else works perfectly. I'd hate to start over again!

If anyone has any ideas I would be very grateful. My apologies for the
length of this post.

Alan M


Anne Troy said:
Hi, Alan. I have a tutorial for just this type of thing. See:
http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com
Click to expand...
Click to expand...
 
Top