Weird function question

**grizzly7277** · 05-25-2013, 10:54 AM

I have a spreadsheet I am trying to create. I work for the railroad and we have thousands of different engines we use. However we are limited on how many we can use depending on horsepower and braking power. I would like to create a spreadsheet that allows you to enter the engine number , and have it provided certain values of that specific engine. Lots of these engines are are similar, like numbers 1 - 458. But then numbers 459 - 999 are different. These engine numbers range from 1 - 9999.

Example: engine numbers: 1 - 458 have 9 power axles and a 9 dynamic brake value. Yet engines 459 - 999 have 9 power axles and an 11 dynamic brake value.

Is there a function that allows me to enter a specific number and it provide me the the values I'm looking for.

Thanks

**MarvinP** · 05-25-2013, 11:01 AM

Hi grizzly and welcome to the forum,

This sounds like a job for VLookup. You could build a table of engine numbers and have it return the horsepower and/or break power for any/all of them. You would then simply assemble which engines are rolling and the spreadsheet would show the totals.

Better yet would be a large list of engines with their specs. You could use the Auto Filter feature of Excel (or even a Pivot Table) to simply select the engines that are active and it would total the numbers for the checked engines.

**grizzly7277** · 05-25-2013, 11:09 AM

Would I have to enter each engine number individually or can I group them as 1 - 458 in a cell?

**MarvinP** · 05-25-2013, 11:11 AM

You can group them. Look at the VLookup Function with a TRUE as the last argument. It will do ranges and return values.

**grizzly7277** · 05-25-2013, 11:15 AM

Thanks I will give it a try.

DaveDeV · 05-25-2013, 11:14 AM

Hi G7,

Can you provide a bit more detail regarding the various "values" you need to incorporate per engine - such as:

- Common Type category
- Description of value (eg Base Location, Power Axle count, Dynamic Braking Power, Horsepower, etc)
- Maximum values for each
- Identify those that need to be "searchable"

I'm trying to get some idea of the nature and scope of the challenge...

Dave