Error when applying formula to other cells

See attached file. I've set up two new named ranges and used VLOOKUP rather than INDEX/MATCH.

Hope this helps.

Pete

Thank you so much , Pete, I cant tell you how much this helped.

I've made the necessary changes to the formula in cell H27, and then copied this down to the other blue cells in that column.

Hope this helps.

Pete

Thank You So Much Pete, really appreciate the fact that their are people with experience offering their help to assist noobs like me

If I ever come over to the UK I will buy you a case of beer!

Hi there Pete

Thank you for the help with modifying the rules applicable to the modifiers in the excel workbook, really appreciated it.

I have another issue which cropped up , wanted your advice and assistance if it is possible to do the following:

If you open the attached excel workbook and go to sheet 2 you will see in

cell J1 title named -> Dispensaries
cell K1 titles named -> Price

If you look at the attached image, I illustrated what functionality is required.

Your help has really been great, and I am sure you are a busy person to, therefore I would appreciate it if you can maybe direct me in the right direction as to how I can go about educating myself in understanding how you applied the formulas in the workbook so far, so that I can help myself and others like you have kindly done.

Thank you once again, have a great day.

Originally Posted by Excelnoob1234

I would appreciate it if you can maybe direct me in the right direction as to how I can go about educating myself in understanding how you applied the formulas in the workbook so far, ...

That's a bit like asking how I learnt to speak English - I can't remember all the sources I've used in the past to learn about Excel. This forum (and others) is a great source of advice if you take the trouble to examine solutions that are given and to work out and fully understand how they work.

I've replied to your PM - I'll try to take a look at this later on.

Pete

I've set up a drop-down in B47 from which you can choose from the Dispensary list on Sheet2. To achieve this, I first set up a named range called Disp to cover J3:J75 on Sheet2 (I also set up another named range called D_Price to cover cells K3:K75 on Sheet2, which I will need later). Then in cell B47/C47 on Sheet1 (it's not generally a good idea to merge cells), I clicked on Data | Data Validation | Data Validation and in the Allow box I selected List and in the Source box I typed:

=Disp

This tells Excel to only allow values from that named range. I copied cell B47/C47 down to B51/C51, so each of those cells now has the data validation set up in them.

I put this formula in E47/F47:

Formula:

Please Login or Register  to view this content.

which checks to see if either B47 or D47 are blank (and returns a blank if so), and if not it then returns the price from the row on which the dispensary occurs and multiplies the unit price by the number in column D. This is very similar to the VLOOKUP formulae I gave you last time, but uses INDEX/MATCH instead (just because I had defined named ranges as lists rather than tables). This formula is copied down to E51/F51.

The formula in the blue cell (H51) is simply a sum of those cells:

Formula:

Please Login or Register  to view this content.

Note that you should use the left-most cell when dealing with merged cells. You can then copy this block of cells down to your other pages (Do you really need so many? It slows the workbook down considerably).

Anyway, I hope this helps - good luck with your project.

Pete

Hey Pete

Thank you so much for the detailed explanation and showing me how you went about implementing the formulas and their application. I carefully analyzed what you wrote down and I think I have fairly good understanding of the concept. You must have great holiday and I respect you for taking the your time in helping me.

Kind Regards

Okay, well if you want to get into it further, here's a link to an excellent site run by Debra Dalgleish:

http://www.contextures.com/tiptech.html

The link is to the contents page, so from there you can look for Data Validation, INDEX, MATCH, VLOOKUP etc., and there are lots of videos on other topics. Add it to your Favourites.

Another favourite of mine is Chip Pearson's site, here:

http://www.cpearson.com/excel/topic.aspx

Both are excellent sources of information, in addition to the forums that I mentioned earlier.

Hope this helps.

Pete