Copying code down a column without all cell references updating.

Hi Guys, Genius' and Saviours...

Is there a quick and easy way to copy my code down the column, without having to manually edit it so much in each cell it has been copied into.

What I am trying to achieve, is for the cell to return a price from a table, in which the price returned is found in a corresponding column dependant upon a price group selection made in another cell..
So, In my code, the following applies:
B12 is a blank cell, where a product code will be entered by the user, the corresponding cells in that row will return results based on the product code entered, such as its description, price etc.
Cell C3 is a data validation List in which the user will select the price group, this determines the price group column that needs to be searched to return the correct price of that item.

In the below example, the only part of the code that I do want to update as the code is copied down is B12 - which will need to change to B13, B14, B15 etc as it is copied down, the rest needs to remain exactly as it is...
My Code:
=(IF(B12="","",IF(C3="PG00",VLOOKUP(B12,AshleyAnnTable,7,0),IF(C3="PG01",VLOOKUP(B12,AshleyAnnTable,8,0),IF(C3="PG04",VLOOKUP(B12,AshleyAnnTable,9,0),IF(C3="PG07",VLOOKUP(B12,AshleyAnnTable,10,0),IF(C3="PG09",VLOOKUP(B12,AshleyAnnTable,11,0),IF(C3="PG12",VLOOKUP(B12,AshleyAnnTable,12,0),IF(C3="PG15",VLOOKUP(B12,AshleyAnnTable,13,0),IF(C3="PG18",VLOOKUP(B12,AshleyAnnTable,14,0),IF(C3="PG20",VLOOKUP(B12,AshleyAnnTable,15,0),IF(C3="PG22",VLOOKUP(B12,AshleyAnnTable,16,0),IF(C3="PG25",VLOOKUP(B12,AshleyAnnTable,17,0),IF(C3="PG28",VLOOKUP(B12,AshleyAnnTable,18,0),IF(C3="PG31",VLOOKUP(B12,AshleyAnnTable,19,0),IF(C3="PG34",VLOOKUP(B12,AshleyAnnTable,20,0),IF(C3="PG37",VLOOKUP(B12,AshleyAnnTable,21,0),IF(C3="PGC",VLOOKUP(B12,AshleyAnnTable,22,0),IF(C3="PGD",VLOOKUP(B12,AshleyAnnTable,23,0),IF(C3="PGE",VLOOKUP(B12,AshleyAnnTable,24,0),IF(C3="PGF",VLOOKUP(B12,AshleyAnnTable,25,0),0)))))))))))))))))))))

My code looks quite long and messy, maybe there is a far simpler way of doing what I need - open to any suggestions...

Thanks in Advance...

That's a horrendous formula. But the only thing that should change other than B12 is C3 so make that absolute ... $C$3


Regards, TMS

Originally Posted by TMShucks

That's a horrendous formula

I know - I'm a relatively new-ish user, and whilst I kind of know how to get excel to do what I want, I often miss the obvious, and am often told I over complicate things.
On the plus side, Your answer worked a treat, thank you so much.
(Feel free to suggest a non horrendous way to do what I need?...)

Thanks again.

Maybe:

Formula:

Please Login or Register  to view this content.

The "a" to "f" are just fillers to get the MATCH to return the correct column depending on the contents of cell $C$3

See the attached sample workbook


Regards, TMS

Forgot to say ... You're welcome. Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Originally Posted by TMShucks

Formula:

Please Login or Register  to view this content.

The "a" to "f" are just fillers to get the MATCH to return the correct column depending on the contents of cell $C$3

Wow, That does look so much better, I agree completely.
As i said in my initial intro.. "Guys, Genius' and Saviours"

I thank you so much for your help.

So, could I just ask you to cast your eye over my amended example attached to see if you agree this is now as good as/best it can be?
(Formulas, not design or layout)

Thank you once again, I truly appreciate it!

Regards

Thanks. Good to see it in "real life".

Not many formulae to check ... looks to work OK.

I would split the invoice or delivery note, or whatever it is, and the Price Table. That then allows for adding rows to either without it getting silly.

I've also added Data Validation to the Product column on the first sheet. That uses the Product Code: column in the Table.

Have a look at the updated sample.

Regards, TMS

Again, I thank you - what you are doing here, helping so openly and willingly on this site is phenomenal, so again, thank you.

This is one very small part of a very major excel document I have been working on for ages.
We are a kitchen design and supply business and I am trying to make a complete user interface for new client lead (Details) capture, product quoting and order management designed to make quoting much quicker than it currently is.
It also needs to keep products from different suppliers together, so I can link the clients order details to my Supplier orders etc. It's all rather confusing, but I know what I need to achieve.

In My 'Proper' Workbook, the table etc is on a different tab along with my data Validation lists etc.
The product details that I used in my example are just a few of my current 12,500 product lines (On the kitchen cabinetry, excluding other products like appliances etc... ), which makes it a tad mad to add the Data Validation list to... What I really want to achieve with this, is to have a filtering search ability on my product code Search cell (Column a in your version you just attached) where it displays valid items as the user types, which they can click on when the see the correct one... For example, I might not know the correct full code for "BFH12PO1D1CH" but I might know that it starts with "BFH12P", and so as I start to type, I want it to show a list of all the units that contain that string in the product code, which I can then click the one I want, then, the rest of the cells do what we have been doing so far here.

Hope this makes sense.

Does this sound like something that Excel can do?
I'm not asking you to dig me out of this hole as well - Not yet, at least ;0) - just thought I'd fill you in on where I am at the moment - my whole workbook is all over the place at the minute.
I'll get there...

Thanks again for all your help.

If you change the definition of the named range "PriceGroups" to

Formula:

Please Login or Register  to view this content.

you could amend the formula in C5 to

Formula:

Please Login or Register  to view this content.

Then, change Data Validation for B2 to use

Formula:

Please Login or Register  to view this content.

@newdoverman: thanks for picking up. When there are Tables involved, I've started trying to use direct references to the Tables instead of creating a Named Range that simply refers to a column or row in the Table.

Cheers, TMS

@TMShucks: I'm just starting to learn these "table tricks" and they are awesome if you can get your head around some of the convoluted logic in them. Those [[ ]] brackets for absolute references throw me every time.

You can use the table headers as a source for named ranges and if there are spaces in the header names use a vlookup to convert the name with spaces to a name without spaces....works quite well for Data Validation.

Thank you for the rep.

Did you look at the DV I put in?

Originally Posted by newdoverman

If you change the definition of the named range "PriceGroups" to

Formula:

Please Login or Register  to view this content.

you could amend the formula in C5 to

Formula:

Please Login or Register  to view this content.

Then, change Data Validation for B2 to use

Formula:

Please Login or Register  to view this content.

Hey,

Thanks for your imput...
But I Think I've over 'Excelled' my head today - I'm reading and re-reading your post and I can't seem to make sense of it...

Changing the list of 'PriceGroups' to a named range makes sense - to change the definition of the named range, does this mean to just add "=Table1[#Headers]" in as the range name?
I tried it like this: "=IF(A5="","",VLOOKUP(A5,AshleyAnn,MATCH($B$2,PriceGroups,0),FALSE))" But it doesn't work at all. Why would this not work?
'AshleyAnn' is the Table, and 'PriceGroups' is the named range.
My head hurts, now..

Originally Posted by TMShucks

Did you look at the DV I put in?

Crikey.. Sorry, man, What's DV?...
I can't see any extra code in the page source?.. (Or whatever it's called in Excel - I'm too used to Web HTML and CSS)

Sorry, I get you - Data Validation... Yes, I saw that, but with a list of what will become in excess of 20,000 product lines, I'll need a quicker way of navigating that. A "DV" list would probably not cope with that much info anyway, would it?

Originally Posted by TMShucks

@newdoverman: thanks for picking up. When there are Tables involved, I've started trying to use direct references to the Tables instead of creating a Named Range that simply refers to a column or row in the Table.

Ah, you've answered my query as to why using the named "PriceGroups" range Theory won't work - of course.

@TMShucks: I think that you are referring to me in this post.....I'll assume so.... having a strange day

I saw your DV at the very end of trying to make my posting. I was examining the formula and thought to myself, hey, this can be replaced by the headers of the table! So, I selected the headers of the table and typed in PriceGroups and when I went into the Name Manager, there was the name but the definition wasn't what I wanted! Not thinking that the name had already been taken for the "actual" price groups, I proceeded to change the definition to that which I had intended. Using just the Headers part of the definition meant that columns could be added to the table without having to change the formula. I then clicked on B2 only to discover that I had "stolen" the definition for the DV! So, I created ProductCode for the DV and included the definition for it (same as your definition). However, the definition went into outer space or some place else as it didn't get included in the actual posting.

I was writing in sentence form with the formulae in the sentences using the Fx button to mark the formula as a formula.

I must have made a mistake because that definition and text were gone as I now see leaving only the name. I have had this happen a couple of times lately and I think I am hitting some weird key combination to make this happen.

My apologies for that! Your use of =Table1[[#Headers],[PG0]:[PGF]] is absolutely correct.

If you are referring to the DV in the left column, you can use the same table for the DV list and this will eliminate having to use the INDIRECT in the DV. Instead of having =INDIRECT("Table1[Product Code:]") for the DV formula you could define a name in the Name Manager.... lets call it Product. The definition would be =table1[Product Code:]

The DV formula would then be =Product

I hope that I'm not being a pest.

You say that you have more than 20,000 product lines. Can this list of product lines be divided into "parent" groups that can then be subdivided into "child" groups?

With that many products, you might be better off searching by the description rather than the product code. That way, you can divide your products by subject like Tables, Chairs, Drawers etc...anything to get away from trying to start off with a product code that you don't know the description of.

Originally Posted by newdoverman

You say that you have more than 20,000 product lines. Can this list of product lines be divided into "parent" groups that can then be subdivided into "child" groups?

With that many products, you might be better off searching by the description rather than the product code. That way, you can divide your products by subject like Tables, Chairs, Drawers etc...anything to get away from trying to start off with a product code that you don't know the description of.

The short answer is yes all the products could be reduced to parent and child categories:
Parent - "Wall Units"
Child - "Short wall units" - "Tall wall Units" etc.
The long bit would be separating the current product data into these sub categories - that would take forever.

A Description search would be good, so long as the search could recognise any part of the search dialogue.
For example, a Unit might have a physical description of "1000mm corner base unit, full height c/w 600 door and arena style lemans corner pull out" And it would be perfect if the search term would bring this up just by typing "arena style"...
However all of our user manuals have product codes, and on a similar note, a unit code might be "CFH10/6LMASH" and the perfect solution would be for the search term to recognise a partial search phrase such as "MASH"
Hope this makes sense.

Are these catalogues electronic or hardcopy? If they are electronic, (Excel or Word readable) I wouldn't mind taking a stab at breaking down a sample of these records into logical, manageable chunks to see how practical this would be.

Just for information, this is one of the links I found that has an excellent explanation of using Table references in Data Validation and Conditional Formatting.

http://www.get-digital-help.com/2012...ting-formulas/


I also found this link useful in explaining how to make references absolute when using Table columns.

http://www.excelcampus.com/tips/abso...uctured-table/


Edit: and another useful link I nearly forgot:

http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp


Regards, TMS

Here's an updated sample workbook. All Data Validation and formula refer to the Table now, which has been renamed as AshleyAnn

B2 (DV):

Formula:

Please Login or Register  to view this content.

A5 (DV):

Formula:

Please Login or Register  to view this content.

B5:

Formula:

Please Login or Register  to view this content.

C5:

Formula:

Please Login or Register  to view this content.

Regards, TMS

Originally Posted by TMShucks

Here's an updated sample workbook. All Data Validation and formula refer to the Table now, which has been renamed as AshleyAnn

C5:

Formula:

Please Login or Register  to view this content.

Wow - I don't think we'll get any tidier than that, that is awesome. Thank you so much!

Crikey, next time you're down the south west, be sure to look me up - I definitely owe you a beer!
I can't believe the time and effort you have both put into this, I appreciate it so Much, Thank you.

TMS, it won't let me click your Rep button again, says I need to share the love!

it won't let me click your Rep button again, says I need to share the love!

OK, rep somebody else that has helped you ... or even another contributor whose solution you have found interesting or helpful ... and come back to me later

I don't get down to Taunton often (well, ever really) but I'll bear it in mind.

Regards, TMS

Originally Posted by newdoverman

Are these catalogues electronic or hardcopy? If they are electronic, (Excel or Word readable) I wouldn't mind taking a stab at breaking down a sample of these records into logical, manageable chunks to see how practical this would be.

Just as a quick reply for now, as I'm doing a bit of housework on the whole file before I upload it to show you what I have so far -
The data in my table is actually already broken down into Parent and Child categories - with the day I had yesterday, for some reason I forgot!

When I do upload my file, please don't slate me - I'm just adventurous
But of course very happy to receive any tips and tricks that can make it so much better..

Very Kind Regards

Originally Posted by TMShucks

OK, rep somebody else that has helped you ... or even another contributor whose solution you have found interesting or helpful ... and come back to me later

Lol - I was in the process of doing that anyway. Very good.

I have gone over what I posted and some of my post got lost in the "ether" and I left out some detail. Here is a file as well as I could reconstruct what I was trying to do that got you confused. Perhaps this file will help sort out the confusion that I caused.

I'm looking forward to your listing to see if I can lend a hand there....if you are interested.

Hey Man.

It's been a while, but I'm back - had a mare with some other bits which has delayed me on this.

I've attached the file link on Box as it is too big to attach the file direct, so you can see the entire project as it currently stands - it is absolutely nowhere near finished, and some of the items do not relate to what they should, and some are not laid out as they should be. I also have more to add, but that's fine.

Predominantly, we're looking at the 3rd tab 'Quote' and the 18th Tab, 'AllProds' (which currently only has 7124 product lines, which will become in excess of 25k lines when complete.

Customer567890 XL File on BOX

Interested to hear your thoughts on this...

Regards

I downloaded your file and took a look especially at the worksheets "Quote" and "All Prods". It is looking good so far.

I suggest that you change the range on "All Prods" to a table by selecting K7124 and then Ctrl + Shift + Home to select the area from A1 to K7124 then insert TABLE making sure that the table selection includes row 1 and the last row. Then go to the bottom of the table and select the next row to the end of the worksheet and delete. Any formulae in the table will copy into added rows as you add them so don't worry about formulae in column J or the 0 values in one of the other columns.

Doing this, knocked 2.7MB off the file size.

Seeing that you are going to have in excess of 25k products, you might want to put some thought into breaking these down into categories so that you can quickly identify the product that you want accurately. That is a lot of product to go through!

That is about it for now.