Populating values for uniquely numbered entities from sheet 1 to other sheets.

[runitsaria]

Hi,

I have got an excel file to calculate costs of various civil constructions.
The Sheet1 of this excel consists of the basic rates of all the entities(almost 2500 entries) that might be used in any kind of civil construction. These entities are given as (Unique code, Entity name, Unit, Rate)

The other sheets consist of individual civil work calculation using a subset of the entities specified in Sheet1.
For eg. Sheet2 could be Road Work and I would have say code 113,115, 2209, 2288, 2800, etc (uses at least 500 entities) used in calculating the costs of the road work. The rates of entities use codes from Sheet1 but their rates are not linked. I tried to link each code in Sheet2 individually to the ones in Sheet1 but since there are more than 500 entities in Sheet2 itself, it's going to take me a lot of time. I want a formula so that I can at once use the rates of these codes for Sheet2 directly from Sheet1, without having to manually link every code in Sheet2 with the one in Sheet1.

For your reference I am attaching a part of the file.

Kindly help with a solution asap

Thanks in advance,
Runit
[email protected]

[smuzoen]

You could make this quite easy with a vlookup - I have a sample attached - in the Basic Rates sheet you need just to place all the Codes and corresponding values in the format sample I supplied. The on the Road Work page you only need to enter the Code into Column B (I have coloured it yellow) - then the formulas will lookup the Corresponding Narration (column C), Unit (Column F), you will need to enter the quantity into Column H, then column I will autofill with the Rate and then in Column J there is a formula to calculate Quantity x Rate.
Finally there is a Total which is a simple sum of the Amounts - All the formulas are in the Road Work sheet - you will not need any VBA code to do this - it can all be done with formulas - I have supplied you with an example and hopefully you can extrapolate from that.
Essentially you only need to enter values in the cells coloured yellow - the rest of the cells are formulas.

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[runitsaria]

Thank you very much Anthony (smuzoen). It solved my problem . You mentioned it has to be in ascending order but it seems to work for unordered as well. However, it really helped me avoid a day of clerical work. Thanks a lot again.