I need help looking up a value in a row, and reporting the column title and the value

[soundengineer]

I have a sheet I am working on, and I dont know how to look up by a row

basically
I need to Look at a known Row, find the Column that is populated with a number value, and then report the value in a cell at the top of the column and the # in the populated row


Link to the Document:

*Link Removed because somebody else posted the file for me a couple of posts down*


when you donwload it...

the formula will end up in cells on Page "NC-40"

I want to look at a row on Page "Input List" that matches the vale in the upper left corner of each box
meaning the Box labeled 1, would look at "input list" Row 4
then it would look for a value in E4 thru N4
it would find that a value of "1" is in Cell E4 and would report "Brown 1" in the cell I pick on the NC-40 Page

Even more ideal...if its possible to do this...
it would just report the value "1" and change the Cell to the color in the row(meaning the formula cell would change itself to match the Brown Color)

thanks if you can help...
I'm currently baffled by how to make this work.

[TMS]

Not everyone, me included, will download stuff from the cloud. Please upload the sample data to the forum

Thanks, TMS

[bebo021999]

Ohh, In fact I didnt pay any attention to the risk till geting the warning. Thank God there is nothing happenned to me...
Anyway I'd like to share

[TMS]

@bebo021999 : I did say "not everybody" probably little to no risk, but who knows. The forum still gets spam and stuff.


Regards, TMS

[icestationzbra]

in cell 'NC-40'!A2 (which is a merged cell, which is a venial crime):

Please Login or Register  to view this content.

this will just show 'Brown 1' etc., and not colour the cell.

encase the above formula as IFERROR(above_formula,0) in case you do not want to see #N/A errors.

UPDATE:

use a set of Conditional Formatting rules to colour result cells.

if you have 2007 or higher, you could use this method.

highlight cells A1 through J18 -- after you have executed the above formula in appropriate cells. then, navigate to:

Home > Conditional Formatting > Highlight Cells Rules > Text That Contains

in the resulting box, type "Brown" (without the quotes). in the adjacent drop-down, select Custom and setup a formatting. do this for each colour that you want to setup, and for each page that you want to setup CF for.

[soundengineer]

Thank you, that seems to work very well


is there any way I can make the cell change color too?



and if its a Venial Crime... then why make a big deal about it.....am I forgiven..LOL

[FDibbins]

Trevor, its more than just "Not everyone, me included, will download stuff from the cloud"...its also "Not everyone, me included, can download stuff from the cloud" Some company's (yes, I do a lot of work here from my work PC) restrict access to certain web sites

[TMS]

@FDibbins: ah, very true. Working for myself, from home, I tend to forget about the corporate shackles

[dredwolf]

...use a set of Conditional Formatting rules to colour result cells.

Post #5

Generally, to actually get the cell color requires VBA, so to color the cell, use CF and apply the rules that made the original cells the color they are..

I think 2010 or 2013 may have a way of finding the color out, but you would still have to use CF to apply...I think ..

Hope this helps

[soundengineer]

the conditional formatting works well in my case
I Just made a set of rules for the cells that needed to see color according to labels

so any cell that contains the word "Red" would get colored Red, any cell that contains the word "Blue" would get colored blue.
takes one rule per color per page that needs it...so theres a handfull of rules... but it works well and does what I need it to do

[dredwolf]

that's a good solution, Congrats !