Is it possible to have 1 cell display a result from a choice of other cells?

[Conundrum02]

What I'm visualizing is on our quote sheet, I want to enter in our cost (in a choice with a 5 tier markup cells-from 1.45 down to 1.25-going down by fives) and have that chosen markup % display the result in cell B2. Say K2, L2, M2, N2, and O2 are the choices with the tiered markup %s. I want to simply key in our cost for an item into one of those cells, have it multiply by that markup % automatically, and have the result display in B2. With the added ROUNDUP code to have the dollar figure always end at $**.99
What I started with was: =PRODUCT(K2>1,$K$2*1.45) but I cant figure out how to add the rest of the formula(s) to it without the dreaded FALSE appearing. Thanks in advance, ppl.

[mc84excel]

Hello Conumdrum02,

Just so I have this clear: You want to input currencies in cells K2:O2. Which cell you choose in this range will depend on what markup you wish to use. You want the output in cell B2 - however the result must be rounded up to the next 99 cents.

Please correct me if I have misunderstood what you are trying to achieve.

What do you want cell B2 to do if you have entered multiple figures in K2:O2? Should it refuse to calculate (because more than one entry was made) or should it multiply each cell by its specified markup and then round up to the next 99 cents?

[mc84excel]

Try this formula in cell B2:

Please Login or Register  to view this content.

Not the most elegant solution but it is working for me. I recommend that you use workbook names for the markup tiers in case you wish to change any of these in future.

[Conundrum02]

Ah, good, thank you for the speedy reply. I tried it, and K2 looks good, but when I enter in a dollar amount in the other markup tiers, nothing happens. So, we're gettin there. And, yes, to your question... only one cell's result should be representable in B2.


Thats what I get for speed reading. lol... just only noticed you went down the K column and not across the row I was using. Haha. Yeah, it seems to work fine. Awesome! Thanks!

[mc84excel]

Ah, good, thank you for the speedy reply. I tried it, and K2 looks good, but when I enter in a dollar amount in the other markup tiers, nothing happens. So, we're gettin there. And, yes, to your question... only one cell's result should be representable in B2.


Thats what I get for speed reading. lol... just only noticed you went down the K column and not across the row I was using. Haha. Yeah, it seems to work fine. Awesome! Thanks!

That's my fault for speed reading. Sorry.

I have an idea so that it will only calculate if there is one figure entered. Will try out and let you know in my next post.

[mc84excel]

Try this formula in cell B2:

Please Login or Register  to view this content.

You can alter the 'too many inputs' message to whatever you want. Just change the text inside the quotes near the end of the formula.

[Conundrum02]

Right on. Works very nicely. I was just applying it to the actual sheet, and only realized that my original thought process, once created, wouldnt really work here. I'll have to create a data table (more or less) using the same formula 22 times for the 22 available lines in the "Items" fields. Hmmm. It still got me alot farther than I was though, and I may still use it. But, upon practical application (with a working formula now), is there not a way I can have that same formula reside over in the data entry cell and have it's result copy into the destination cell instead? Just in case the User wants to manually enter in an amount, and not be held prisoner to just the result.

But, as I said, this formula would still be very usable.

[mc84excel]

is there not a way I can have that same formula reside over in the data entry cell and have it's result copy into the destination cell instead? Just in case the User wants to manually enter in an amount, and not be held prisoner to just the result.

But if you have a formula reside in the data entry cell, it will be lost (overwritten) when the User manually enters in an amount. Or have I misunderstood your request?

[mc84excel]

I'll have to create a data table (more or less) using the same formula 22 times for the 22 available lines in the "Items" fields.

I'm having trouble visualising what you are trying to achieve here. However if you could create a quick mockup of this data table (with some dummy data/items/amounts entered in it) & upload it to this thread, I am willing to have a look at it and see if I can assist further.

[Conundrum02]

Was told today that my efforts regarding the Quote sheet are unnecessary now, as we're apparently going to utilize the POS's rather bland Estimate functions. Which, is disappointing. I rather very much preferred the Quote sheet I built. Oh, well... thank you, again, for your efforts. Peace!

[vlady]

Well that's really disappointing to hear.

[mc84excel]

Hello Conundrom02,

Sorry to hear that. I know how you feel - it's always disappointing when you work on a project and it ends up scrapped or features removed.

Since I solved your initial question (1 cell display result from a choice of other cells) - would you mind marking this thread as solved? Thank you. (Mark as solved by signing in and select 'Thread Tools' near the top of this page).

If you have any other Excel queries in future, come back to www.excelforum.com.