[SOLVED] Combining cell values in another cell

dwtaxguy · #1 07-01-2009, 05:10 PM

Probably not the best description, but I don't know how to word what I'm trying to do...

I have a spreadsheet with multiple worksheets that track hours spent on equipment repairs. Each worksheet represents one piece of equipment and is labeled with it's serial number. In each worksheet the total number of hours has been calculated and the cell that contains the total has been named.

Example: eng1035 is the engineering hours spent on unit 1035.

What I want to do on the summary page is somehow use the value in the row above my totals and the value in the column to the left to reference the named cell. The column contains the serial numbers; 1035, 1067, 1076, etc. The Row across the top contains, ENG, CAL, TEST, etc..

What I want to do is some how combine the "ENG" and the "1035" to reference the cell value and not have to manually input "eng1035" for each unit and labor category.

I've attached a spreadsheet with values only, no formulas for a visual reference.

mewingkitty · #2 07-02-2009, 02:21 AM

Bit of a wild guess here, since you didn't include anything showing the intended layout of your summary page, but see the attached sheet, it's all on the same page, since I'm guessing wildly... but it checks for values, then combines the column header with the item number, and the number of hours associated with it.

???

mew!

DonkeyOte · #3 07-02-2009, 03:16 AM

I think you're actually saying you want to combine header and Box# to create the named range reference to retrieve associated value, ie:

I2: =INDIRECT(I$1&$A2)

should reference named range eng1035

NOTE: INDIRECT is a Volatile function - see link in sig. for more info.

dwtaxguy · #4 07-02-2009, 08:49 AM

Thank you both for your assistance! DonkeyOte your suggestion works perfectly. I've received other warnings about the INDIRECT function, but thus far it's worked well for me.

Thanks again for your help, and I'll make it a point to read your suggested material.

Cheers!

dale

Welcome to the Excel Forum

Forum Statistics