Referencing Cells to correspond with their rows for autofill

To be more specific,

I work with highway signs, and they all have codes. the codes are priced according to their size, sheeting, and aluminum structure.

so codes can be something like : GS-4, IB-12, RB-21, and their sheeting could be: 231, 241, 245S, 420P, and their price is dependent with the size, materials, and quantity ordered. So a quantity of 3 or less of a flat sheet of aluminum, with HIP reflectivity sign would be a 231, where 4 or more of that same sign would be 241.

A workbook has the contracted price, the cells are in order. The order sheet I use is separate from this. But I have to look for each sign for all the info. How can I get the one cell to recognize the Code, along with the Quantity ordered?

Uploading a sample workbook would make the question a lot more clear. I think this should be easy to take care of with an example workbook.

I'll do that! Give me a moment...

Essentially, when in Sign_Orders, when you enter the sign Code Column, i would like at least the Unit Price to autofill from the Contract workbook

Very well done! Now Why can't I make it work on my end when I try to duplicate it..?!

My actual contract workbook has about 787 signs in it, 17 pages in Excel. I only sent you the basic idea, not sure the legalities about sending actual contract prices, etc..

To start on the left, the pick list is created buy referencing column T? is this an important part of making the formula work, or just aesthetics?

The important question I had for you was in relation to the file locations. You saved yours way in the C drive, where mine is on a network, it doesn't have to be necessarily..

It would be great if I can get everything to autofill! but if i can get the prices, it would save me a lot of time! second would be the 'spec'.

I'll keep fiddling trying to make it work, but please help otherwise

Originally Posted by NINmh

To start on the left, the pick list is created buy referencing column T? is this an important part of making the formula work, or just aesthetics?

It is an important part of making the formula work...not aesthetics. It is the list that populates those drop down lists. Would you like help building the drop downs?

The important question I had for you was in relation to the file locations. You saved yours way in the C drive, where mine is on a network, it doesn't have to be necessarily..

As the formula is typed Excel apparently uses the location of the files as Excel stored them on my computer at the time.

Just replace the references using the file you use. I simply select / highlight what I want to replace then

click and drag on the appropriate ranges in whatever file I want to reference. My file references will be replaced by yours.

The operation is tedious and prone to errors; I don't know another way around it.

It would be great if I can get everything to autofill! but if i can get the prices, it would save me a lot of time! second would be the 'spec'.

I'll keep fiddling trying to make it work, but please help otherwise

Once the formulas are taken care of would be the time to talk about the auto-fill part. It sounds like you could benefit from using a table if I have interpreted correctly what you mean by auto-fill.

I don't know what the limitations if any of tables are....you are going to have a large file and I have never worked tables into anything much larger that 200-300 rows. I need to study up on that part.

On the other hand perhaps filling the formula down far enough to handle future entries would do what you want.

Has any of this helped?

Yes, help please! I can't seem to get it to work

The sign codes go from Row 3 to Row 787.

I can eliminate some of them from this, if it helps, since some signs are unique, and therefore have no specific price to them ( think of leaving a community on the highway, and a green sign tells you how far the next community is)

where the majority of signs in this list are a standard, some with different sizes ( think of stop signs, yield signs - these are a standard design, but have a few sizes to choose from ) Where a deer crossing sign, or a route shield would be a standard design, and only one size.

What I need to ask is how do you wish to enter those codes into column A? I have made an experimental list of

random "codes"......787 of them....sorted them and assigned them to some drop downs. Would you like me to upload

the experiment so you can see if this is practical. The consideration here is that the codes in column A must be

exact matches to the codes in the contract WB. You'll need these to get the correct prices.

Would you like me to upload?

Sorry if I don't respond quickly, this is an at-work project, which I don't take home with me.

I did make the list work, I first copied all 787 rows of Codes into the Workbook I put my orders into. I 'Defined Name', then 'Data Validation' for the pick list, made it work for all column A. So that's in place. I did read that you can put an error message if a person were to enter the wrong code, I will look into that after I get the rest going smooth!

Where I have all the problems, is with the price referencing.

Returning prices from the wrong column / wrong row on the contract WB?

Well, I've tweeked the formula you sent, but now it comes as '#N/A' with a 'Value Not Available Error'

Here is the formula I'm using:
=SUMPRODUCT(($E265<4)*($A265='G:\Sign\Traffic Guidance\Sign Prices & Costs\AA Sign Purchase Contracts\2013 Sign Contract\[Yr 2010 Contract last rev May-13.xlsx]Sign Index Prices'!B$3:B$10)*('G:\Sign\Traffic Guidance\Sign Prices & Costs\AA Sign Purchase Contracts\2013 Sign Contract\[Yr 2010 Contract last rev May-13.xlsx]Sign Index Prices'!H$3:H$787))+SUMPRODUCT(($E265>3)*($A265='G:\Sign\Traffic Guidance\Sign Prices & Costs\AA Sign Purchase Contracts\2013 Sign Contract\[Yr 2010 Contract last rev May-13.xlsx]Sign Index Prices'!B$3:B$10)*('G:\Sign\Traffic Guidance\Sign Prices & Costs\AA Sign Purchase Contracts\2013 Sign Contract\[Yr 2010 Contract last rev May-13s]Sign Index Prices'!J$3:J$787))

are spaces in the file name/location a problem? I know sometimes they can be!

I believe i see what the problem is? In the workbooks I sent you, the prices were typed in, where in my contract sheet i'm referencing, there's a formula to reference another sheet & cell in it's workbook.

Is there a way around that, or do I have to change the method of how the prices are entered in the Contract sheet?

=VLOOKUP($D2,[Contract.xlsx]Sheet2!$A:$C,2,0)

Is this a formula to use? how would this work?