Help needed, outputting 1 of 2 different results based on cell value

[Stigdu]

Hi all,

New to the forum and need some help.

I have a workbook which has 2 sheets.

The first contains part numbers in column A, part description in column B, quantity in column F and cost in column G.

However, the cost can be either a trade cost or an end user cost.

Sheet 2 looks like this:

Column A - part description
Column B - part number
Column C - trade cost
Column D - end user cost

So if I put in, say, Cell E50 on sheet 1 the word 'Trade', I'd like all the costs which are returned to me on that sheet to be the costs in column C on sheet 2.

This is the formula I'm trying to use, but isn't working:

=IF(E50="Trade",F19*VLOOKUP(B19,PriceList!B5:B102,2,FALSE),PriceList!D6)

Any help appreciated!
Thank you

[Richard Buttrey]

Hi, and welcome to the forum.

It's not clear, at least to me, what you mean by 'isn't working'. Would you upload the workbook and manually add the results you expect clearly identifying which cells are the results so that we may see in context.

[Stigdu]

Thanks Richard, I've uploaded the sheet and have picked a product from the drop-down list in cell B19, and the quantity is next to it.

As you can see, I'm just getting #N/A in the unit price column on the quote rather than the actual cost which is on the PriceList sheet.

dummyquote.xlsx

[Sam Capricci]

one thing that I see is that this...
=IF(E50="Trade",F19*VLOOKUP(B19,PriceList!B5:B102,2,FALSE),PriceList!D6), The VLOOKUP is saying you look in column B of the price list, find the corresponding value in B5:B102 that matches B19, then go to the next column which would be C and return that value. If you don't change that part from B5:B102 to B5:C102 it won't work.

EDIT: BTW, in your attached sheet it would have been helpful to show where you are putting that formula so we could see the results.

[Stigdu]

Apologies, the formula haven't come across in the sheet for some reason. It is in cell G19 on the first sheet.

[Richard Buttrey]

Hi,

There was no drop down in B19 and no formulae on the Quote sheet only #N/A. However I assume you're looking for a formula for G19, in which case

Please Login or Register  to view this content.

With the word 'trade' in A1 it will return the trade cost, blank or anything else will be the end user cost.

Incidentally be careful when using merged cells like you have in B:E. Rarely are they a good idea and most of us avoid them like the plague, they just cause too many problems with other aspects of Excel. You can achieve the same thing by using the 'Center Across Selection' in the Format Cells Horizontal Alignment box.

[Stigdu]

Hi Richard, yes, that's exactly right - and your formula works perfectly.

Many thanks!

[Sam Capricci]

This is a different take using the vlookup you began with... =IF(E50="Trade",F19*VLOOKUP(B19,PriceList!A5:C102,3,FALSE),PriceList!D6) if you're interested.

[Stigdu]

Thanks Sambo kid.

Got another issue, probably very easy to fix, but I'll start a new discussion topic as it's unrelated to this question.

[Stigdu]

Help! This sheet has suddenly got quite a bit more complicated, and I'm really stuck as to how to proceed.
There are basically some more conditions required now as to what price list to select from, as there have been a couple more drop-down
lists added on sheet 1, and some more prices on sheet 2.

I've attached a sample.

So now, I have a drop-down for trade/end user, a drop-down for the company name (3 companies listed), and a drop-down for which tier (or column) it should be getting the prices from,
IF the company selected in the 2nd drop-down happens to be company B.

Very complicated!!

I've attached a dummy version of the sheet.
Thanks for any advice/help!!

[Sam Capricci]

the sheet you attached is password protected. I can't open it.

[Stigdu]

Apologies. Password removed.

[JohnTopley]

Where are the drop downs positioned? You have lists of drop down entries but no actual drop downs shown?

If required could the headings in the Price list be limited to "Trade", "End User" ,"Tier 5 " etc as this makes matching headings much easier?

[JohnTopley]

See attached: drop downs in L1, M1 and N1. Changed headings in PriceList.

I would also suggest you reverse the order of item code/Item description which enable a VLOOKUP of Description based on Item Code.

[Stigdu]

John, thank you - sorry, I had a mammoth conference call and then lunch.

Much appreciated what you've done there - it looks like that will work. I'll have more of a play with it in a while; the dropdowns were in
cells E50-E52, but I can easily keep those and still use your (admittedly very complicated-looking) formula.

I can reverse the order of those columns, as you say. It doesn't look like you've done that in your version of the sheet. What benefits do I get from doing that?
Sorry, still very much a basic-intermediate Excel user and learning new things every day.

Many thanks for your time on this.

[Stigdu]

Fantastic! I've had a play around with the cell locations and putting the proper company names in, and it's looking a lot better now. Thanks
for all your help.

Another question, if I may:

At the moment, the 'DESCRIPTION' dropdown is only giving me company A's items. I believe I can select items across multiple columns
to create a very long dropdown (in the example above, it would be columns B,F and O on sheet 2.

The question is, is it possible in Excel to have it so that once I have selected the company name initially, it will ONLY show me products
from a specific column (ie the company in question) in the list of selectable options in the dropdown?

[JohnTopley]

Posted this BEFORE I saw your last post re company-dependent lists: below is the principle of what you want.


Please see the attached where I have added some additions for you to consider.

For each Company , I have created two Named ranges ("A_Desc" and "A_Desc_Items"). etc.

In the "Quota" form the "Description" entries are generated specific to a company: in this case "Company A": it uses the INDIRECT function to reference the Named Range. Look at the Data Validation for "Descriptions".L1

=INDIRECT(RIGHT($L$1,1)&"_Desc") creates a reference of "A_Desc" : RIGHT(L1,1) gets the "A" from "Company A" in L1

In the Items column I use VLOOKUP with "Description" as the search and the named range "A_Desc_Items" as the lookup range.

This may seem a bit complicated but does make the whole system more dynamic.

These changes mean the need to reverse Description/item is NOT required.

Have a "play" with this and if you find it acceptable, I am happy to help you take it further.

If you need an explanation of the price formula I can provide it.

[Stigdu]

Hi John,

Thanks very much for that - I've tried your spreadsheet and it appears to be working exactly as required. I've tried to amend the formula slightly so that it fits in with the proper company names, prices, etc but I'm getting blank results output so I must be doing something wrong.

Would it be possible for me to send you a copy of a small part of the sheet, and you can hopefully tell me where I'm going wrong?

Many thanks

[JohnTopley]

Either PM me or simply post on thread.

[Stigdu]

File attached.

[JohnTopley]

See attached:

Several points to make:

1. when you compare E50 in the price formula, ensure the check is correct: you were checking "VUE" vs. "abc"

2. The changes in "Pricelist" resulted in the wrong ranges being checked when matching "Description".

3. The named ranges I created needed to be changed e.g "A_Desc_items" to "abc_Desc_items"

4, The data validation to retrieve the Description needs to be =INDIRECT($E$50 & "_Desc")

5. Item Lookup needs to be =IFERROR(VLOOKUP(B19,INDIRECT($E$50&"_Desc_Items"),2,0),"")

If you change the Company names make sure you change the named ranges accordingly and that the drop-down names are consistent with the company names.

Let me know if you have any queries re the above.

[Stigdu]

This is infuriating now! The drop-down in the Description field (B19 downwards) only shows one item, no matter what I change the company name dropdown to.

I've copied your formula across and changed the group names in the PriceList.

My Unit Price formula is now:

=IFERROR(IF($E$50="VUE",INDEX(PriceList!$A$1:$D$49,MATCH(B19,PriceList!$A$1:$A$49,FALSE),
IF($E$49="Trade",3,4)),IF($E$50="Brigade",INDEX(PriceList!$E$1:$M$30,MATCH(B19,PriceList!$E$1:$E$30,FALSE),
MATCH($E$51,PriceList!$E$1:$M$1,0)),IF($E$50="SmartWitness",
INDEX(PriceList!$N$1:$Q$137,MATCH(B19,PriceList!$N$1:$N$137,FALSE),IF($E$49="Trade",3,4)),""))),"")

Is it that that I've done incorrectly, or somewhere else on the sheet?

The data validation for the company name is:

=$J$4:$J$6

Should I be changing that?

[JohnTopley]

Data Validation for Company names must be "VUE", "Brigade", "SmartWitness"and the named ranges n must start with "VUE_ ..... etc. as I described in my previous post.

If you change the Company names make sure you change the named ranges accordingly and that the drop-down names are consistent with the company names

[JohnTopley]

See attached.

[Stigdu]

Yep, I've done both of those things. I'm flicking back and forth between your sheet and mine, and can't see any differences
except for the fact yours works and mine doesn't!

[Stigdu]

No worries, I've just copied across everything into your (working) sheet and it's all good!

Thank you once more John, you're a life saver.

All the best
S

[JohnTopley]

If you are OK with the results, can you please mark the thread as SOLVED ("Thread Tools" at top of first post).

If you need further assistance just start another post.

[Stigdu]

It's all working fine except for one thing. If I drag down on cell G19 (the one with the very long formula) to copy the formula down the column,
then it will only show me products from company A (VUE).

i.e. The 2nd line down (G20) looks like this:

=IFERROR(IF($E$50="VUE",INDEX(PriceList!$A$1:$D$49,
MATCH(B20,PriceList!$A$1:$A$49,FALSE),IF($E$49="Trade",3,4)),
IF($E$50="Brigade",INDEX(PriceList!$E$1:$M$30,MATCH(B20,PriceList!$E$1:$E$30,FALSE),
MATCH($E$51,PriceList!$E$1:$M$1,0)),IF($E$50="SmartWitness",INDEX(PriceList!$N$1:$Q$137,
MATCH(B20,PriceList!$N$1:$N$137,FALSE),IF($E$49="Trade",3,4)),""))),"")

What have I done wrong?

Thanks John!

[JohnTopley]

Are you trying to enter data for more than one company? The current system does not allow that as it is determined by the company set in E50 which in turn determines which drop-down lists and pricing is used.

If you need to enter data from multiple companies the attached is one way of doing it: I added extra columns A/B to allow input of company and pricing tier: in the latter I combined the "Trade", "End User" and "Tiers 1 to 5" into a single drop-down.

[Stigdu]

Hi John,

That's wonderful - such a clever workaround. I'm unsure as to the purpose of the extra sheet you've added to the workbook though - Sheet 1.

Isn't the main quote page still pulling all the description and pricing data from the PriceList sheet, making Sheet 1 superfluous? I'd like to
add the rest of the (proper) data to the workbook but am not sure if I should be using the PriceList sheet, or Sheet1.

Thanks

[Stigdu]

It wouldn't let me edit my post above)

I've plugged all the data into PriceList and it's working wonderfully. Still not sure what Sheet1 is for, though. Am I safe to delete it?



Will wait for your reply and then mark the topic as SOLVED. Thanks so much for all your help.

[JohnTopley]

You can ignore Sheet1: I set it up as I was thinking we might need a different solution so continue adding data to the existing Pricelist.

Apologies for the confusion but glad you are able to use the proposed solution.

As before, please ask if you have any more queries.

[Stigdu]

Hey John,

I've added another set of prices to the PriceList sheet in the workbook, and have amended the unit price formula accordingly,
having set up a new named range for the new company. Can you have a look at this and tell me where I've gone wrong?
Everything works exactly as expected, except the prices for the new company don't show up - instead, it always says
"FALSE" instead of the proper unit price.

Many thanks

[JohnTopley]

File is password protected.

[Stigdu]

Apologies - password removed.

[JohnTopley]

=IFERROR(IF($A20="VUE",INDEX(PriceList!$A$1:$D$49,MATCH(D20,PriceList!$A$1:$A$49,FALSE),IF($B20="Trade",3,4)),IF($A20="Brigade",INDEX(PriceList!$E$1:$M$30,MATCH(D20,PriceList!$E$1:$E$30,FALSE),MATCH(B20,PriceList!$E$1:$M$1,0)),IF($A20="SmartWitness",INDEX(PriceList!$N$1:$Q$215,MATCH(D20,PriceList!$N$1:$N$215,FALSE)),IF($A20="Thinkware",INDEX(PriceList!$R$1:$U$14,MATCH(D20,PriceList!$R$1:$R$14,FALSE),IF($B20="Trade",3,4)),"")))),"")

Missing bracket .... so easy to do!

[Stigdu]

Thank you!!

[Stigdu]

Hmm.. now that the formula is working for the new company, something has happened and it's not picking up the prices from the PriceList tab for SmartWitness. The other 3 companies are all working as expected. Another bracket issue?

[Richard Buttrey]

...maybe, or maybe a price list range that isn't big enough. You'll need to upload the workbook so that we can see in context.

Have you also considered using a helper column in the price list and concatenating A&B price list cells into one so that you could use a simple VLOOKUP and avoid IF statements.

[JohnTopley]

try
=IFERROR(IF($A19="VUE",INDEX(PriceList!$A$1:$D$49,MATCH(D19,PriceList!$A$1:$A$49,FALSE),IF($B19="Trade",3,4)),IF($A19="Brigade",INDEX(PriceList!$E$1:$M$30,MATCH(D19,PriceList!$E$1:$E$30,FALSE),MATCH(B19,PriceList!$E$1:$M$1,0)),IF($A19="SmartWitness",INDEX(PriceList!$N$1:$Q$215,MATCH(D19,PriceList!$N$1:$N$215,FALSE),IF($B19="Trade",3,4)),IF($A19="Thinkware",INDEX(PriceList!$R$1:$U$14,MATCH(D19,PriceList!$R$1:$R$14,FALSE),IF($B19="Trade",3,4)),"")))),"")

[Stigdu]

Thanks again John - I don't know what I'd do without you! I'm trying really
hard to do this myself but the formula is getting pretty complicated now...

All the best
Stacy

[JohnTopley]

Look at the attached where I have "redesigned" the price list and added a sheet (" Parts_list") which has the descriptions for parts for each company: these are named ranges ("VUE","Brigade" etc). These lists are updated automatically as items are added/deleted from the "PriceList" using the formula below.

=IFERROR(INDEX(PriceList!$B$2:$B$1000,SMALL(IF(PriceList!$A$2:$A$1000=Parts_List!A$1,ROW($A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Entered with Ctrl+Shift+Enter

As a result the price formula is much simpler but you still get the parts list associated with a company.

=IFERROR(INDEX(PriceList!$A$1:$L$100,MATCH(D19,PriceList!$B$1:$B$100,FALSE),MATCH(B19,PriceList!$A$1:$L$1,0)),"")

First MATCH returns ROW based on "Description": second MATCH gets COLUMN based on "Tier"

I have added a further refinement to select the "Tier" associated with a company (see table in L3:M9): "Tier" data validation in "Quota" uses a VLOOKUP to select the correct tier (one of 2 named lists: TierA and TierB)

To add a new company , add the data to the "PriceList" then change "Company A" (etc) in "Parts_List" to that of the new company.

Add the company to the table in L3:M9 with the corresponding tier.

As you have realised the current price formula approach is not sustainable for an increasing number of companies

Hope this helps.

[Richard Buttrey]

As I suggested in #39 change the layout of your price list
e.g.
Column 1
azTrade
azenduser
axtrade
.
.
etc

Column 2
5
8
7
.
.
etc

It will be a longer list but not as wide. Then just use this as the lookup table

[Stigdu]

Thank you John, that works a treat!

[JohnTopley]

Can we put this one to bed?

If you need additional help I suggest you start a new thread as this one is already marked SOLVED.