function that will generate a price based on three fields

**cjconnor24** · 07-06-2007, 02:27 PM

Does any one have any ideas:

I'm a graphic designer and i'm trying to build a spreadsheet that will help me in doing my accounts.

Ok, so i'm trying to set up a function that will generate a price based on three fields:

Lamination:YES or NO
Duplex: YES or NO
Stock: Around 14 different stock options.

I had it working using a nested IF but I ran out of the 7.

I then tried using CONCATENATE using "&" to seperate but excel said the formula was too long.

I'm Lost!

**VBA Noob** · 07-06-2007, 02:29 PM

Post a sample with expected results

VBA Noob

**cjconnor24** · 07-06-2007, 02:30 PM

Thanks for such a quick reply. I've just this minute signed up.

Do i just attach my file?

**VBA Noob** · 07-06-2007, 02:38 PM

Yes. Excel files need to be zipped. USe the paperclip symbol on the toolbar when your replying

VBA Noob

**cjconnor24** · 07-06-2007, 02:42 PM

hopefully should have the file attached

**VBA Noob** · 07-06-2007, 03:03 PM

Does this help

VBA Noob

**cjconnor24** · 07-06-2007, 03:10 PM

Originally Posted by VBA Noob

Does this help

VBA Noob

I have no idea what those functions are doing however that is exactly what i've been trying to do. You've made my weekend!

**VBA Noob** · 07-06-2007, 03:14 PM

No problem

It uses the following

concatenate

Vlookup

Data Validation for the dropdowns

Advance Filter to get a Unique Records

VBA Noob

**cjconnor24** · 07-06-2007, 03:37 PM

Does anyone have anytime that they could tell me what is going on there? I'm baffled by how good that is - all i need to do is try to incorporate this into my actual account spreadsheet.

im not too sure what VLOOKUP actually does?

Does ISNA just look for #N/A ?

**VBA Noob** · 07-06-2007, 03:46 PM

Take a look at blue links for more info

If you still need more help then post back

Yes the if statement uses isna to check for #N/A errors

VBA Noob

**cjconnor24** · 07-07-2007, 07:20 AM

Fantastic - totally got the hang of it now! Thanks!

ISNA is a great function! Is there something similar to use so the if the result is #VALUE! you could leave a blank?

**VBA Noob** · 07-07-2007, 07:29 AM

Yes

Iserror captures all errors

http://www.bettersolutions.com/excel...R711922323.htm

VBA Noob

**cjconnor24** · 07-07-2007, 07:39 AM

SPOT ON AS USUAL!!!

worked great! Thanks!!

**cjconnor24** · 07-07-2007, 08:05 AM

Just one last thing - i've manage to create my sheets - is there anyway to to lock down the doc and only let certain parts to be edited. Similar to protecting a word doc for form filling. (if that made any sense)