lookup and if function for complex question

quote template.xls

Almost have it but not quite. there is still an error..... trying not to bother you! can you please have a look and see if you can work out what i did wrong! please!

sumcust2.xlsx


index is the way to go with this one take a look and if you need help let me know

Ok, My goodness. Thankyou so much! Its funny, when you see it, makes perfect sense, but I definately would not have come up with that.

Thanks again, very much appreciated.

I cannot put in to words my thanks. This is what i love about forums. I had no clue how to do this, then you lay it out for me on one table, and now i have something to follow and see where you got your info and how you did it. I just (and you should be proud as you taught me!) put in a new table and lookup and all looks lovely.

Was going to ask one thing, although not important, but would be nice. on my sheet when i print i have all these ugly #N/A on the cells that done get used.... (not on your formulas but on my two.) how did you do that?

You know the other things that bothered me. When I made a new sheet i brought all the codes over to the next sheet, and when i went down to copy the formula, it not only changed the reference cell from c6 to c7 but also the lookup cells which will remain the same. (ie:

=LOOKUP(C6,brickcourse!D5:AY5,brickcourse!D6:AY6)
next row down =LOOKUP(C7,brickcourse!D5:AY5,brickcourse!D6:AY6)
next row down =LOOKUP(C8,brickcourse!D5:AY5,brickcourse!D6:AY6)

but mine was doing this when i copied the code:
=LOOKUP(C6,brickcourse!D5:AY5,brickcourse!D6:AY6)
next row down =LOOKUP(C7,brickcourse!D6:AY6,brickcourse!D7:AY7)
next row down =LOOKUP(C8,brickcourse!D7:AY7,brickcourse!D8:AY8)

and so on.

I changed them all manually, but is there a way to not have to. just out of interest.

Also can i lock these formulas so they dont get accidently changed by the users and they muck it all up? lol

Thanks again. I have a thirst for it now.

Ok.....IFERROR( before and ,"")after your formula will get rid of the nasty N/A or #value or #name ect.

and to stop the numbers from changing when you drag it down put a $ before the number.

If you want the column letter to not change when you drag the formula left or right you put the dollar sign before letter


for example when doing Vlookups your formula should always have $ in them like this =vlookup(a2,sheet1!$a$2:$a$5000,2,False)

if you want to see how it works put a word in A1 then in C3 put =A1, then drag it around, and then put in another cell =$a1 and drag that around....and finally =$a$1


and yes you can lock your formulas( or rather lock the whole sheet and leave the data entry cells unlocked). highlight the data entry cells then right click on them go down to format cells then protection and uncheck the box that says locked, do this for all the cells you want people to enter data into. When you are done you can go to the review tab and protect the sheet. if you have random people using the sheet then i would put a password on it. if it is just the workers you know then i would leave the password blank.
if you need more help send me a message on my profile page i would be glad to help

i typed a email to you through the friends thing, but unsure if you will get it, didnt have any confirmation???? anyway, if you did get the mail, here is the attachment,quote template two.xls, if you didnt get my query can you pm me and i will retype the thing! lol

Thanks Dave.
Meredith