Using IsBlank statement to evaluate given fields and update corresponding cells.

[ColdplayCoaster]

Greetings all!

I am trying to use a nested IsBlank function to generate a pre-defined label (B1, C1, D1) into another field given that the corresponding column is not blank (B2:B4, etc...) As each column is given text, the label field should update with the header for it. For example, the user inputs text into B2 and the field should update to "B1". To add more frustration, it should evaluate the entire row and select only the "highest ranking" last level indicated ("D" ranking highest). Is this possible to do with IsBlank or have I reached a limitation with this particular function. Any and all help is greatly appreciated in advance.

Excel Workbook
	A	B	C	D	E
1	NAME	LEVEL 3	LEVEL 2	LEVEL 1	Highest Completed
2	Mr. Smith	X
3	Mr. Bob	X	X	X
4	Mrs. Jane	X	X
Sheet1

Excel 2007

 

[Peter_SSs]

Welcome to the MrExcel board!

If the entries under the 'Level' headings are text, then try this formula, copied down.

Excel Workbook
	A	B	C	D	E
1	NAME	LEVEL 3	LEVEL 2	LEVEL 1	Highest Completed
2	Mr. Smith	X			LEVEL 3
3	Mr. Bob	X	X	X	LEVEL 1
4	Mrs. Jane	X	X		LEVEL 2
Level Reached

 

[ColdplayCoaster]

That was exactly what I was looking to accomplish. Thank you for your speedy, accurate response. I most certainly have added MrExcel to my bookmarks!

 

[ColdplayCoaster]

Another question not to bump but didn't want to create another thread...

Using the code provided by Peter_SSs, I now have working values for my fields, however if there are no values in the field to show, the formula defaults to #N/A. Is there any way to change this to a blank cell until values are provided in any of the fields. I "tried" what would make sense to me in B45, but alas, no success. Thanks for all your excellent ideas out here.

Excel Workbook
	A	B	C	D	E	F	G	H
42	Jo Troller	4062 L3		X	X
43	Brittani Eatmon	4062 L4		X
44	Kylie Torres	4062 L4		X
45	Ashley Coates	#N/A
46		#N/A
47		#N/A
4062 Checklists

Excel 2007

Cell Formulas
Range	Formula
B42	=INDEX(D$1:G$1,MATCH(REPT("z",255),D42:G42))
B43	=INDEX(D$1:G$1,MATCH(REPT("z",255),D43:G43))
B44	=INDEX(D$1:G$1,MATCH(REPT("z",255),D44:G44))
B45	=IF(INDEX(D$1:G$1,MATCH(REPT("z",255),D45:G45))="#N/A", "")
B46	=INDEX(D$1:G$1,MATCH(REPT("z",255),D46:G46))
B47	=INDEX(D$1:G$1,MATCH(REPT("z",255),D47:G47))

 

[Peter_SSs]

Since this is a direct follow-up to the original question, this is the best place to post. If it is unrelated, best to start a new thread.

For row 42:
=IF(COUNTA(D42:G42),INDEX(D$1:G$1,MATCH(REPT("z",255),D42:G42)),"")


Or if you are using Excel 2007+ you could try
=IFERROR(INDEX(D$1:G$1,MATCH(REPT("z",255),D42:G42)),"")

 

[ColdplayCoaster]

You said if I was "using Excel 2007"...

Does this apply to only Worksheets created in 2007 or in worksheets that may be saved in a 2003 format (i.e. backwards compatibility)? The reason I ask is because the end-use computers between creation and use are running these different versions.

 

[Peter_SSs]

If the file may be used on a machine running, say, Excel 2003 it will throw an error because IFERROR is a function that was not available in that version. So if the file may be opened using an older version, then use the first formula from my previous post.

 

[ColdplayCoaster]

Awesome! Just tried it and again, you are right on the ball. Thanks so much Peter_SSs!

 

[Peter_SSs]

Cheers.

I did mean to mention your attempt to deal with the error in row 45 in post #4. You were heading in the right direction. The correct syntax would have been:

=IF(ISNA(INDEX(D$1:G$1,MATCH(REPT("z",255),D45:G45))),"",INDEX(D$1:G$1,MATCH(REPT("z",255),D45:G45)))

In fact, you could use this formula but you can see that it is not as compact (& not as efficient) as the one using COUNTA.