Retrieving the Value of the First Non-Blank Cell in a List

[leveyc]

Hi, I tried this formula but everytime I use it I always get #VALUE! why? and if I wanted to find the second non blank and so on how would i mod the formula

[ExcelTip]

Problem:

Retrieving the value of the first non-blank cell in Range1 (cells A2:A7).

Solution:
Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX(A2:A7,MATCH(TRUE,A2:A7

To apply Array formula:
Select the cell, press

[leveyc]

Hi I was looking at this formula but I keep getting a Value error, what I really was looking for was a formula that would retrieve data from a adjacent cell if data was entered in to a specified cell i.e. If A1:A5 and B1:B5 had text in them and data was enter in C1 I could return the data in A or B1 in a cell of another sheet

[oldchippy]

The formula in the Excel Tip is incomplete, it should be

=INDEX(A2:A7,MATCH(TRUE,A2:A7<>"",0))

To apply Array formula, so the the braces appear in the formula bar { } like so

Select the cell, press Ctrl+Shift+Enter

For the second part of your question take a look at this link, as a lookup looks like the solution to your problem

http://support.microsoft.com/kb/214252

[leveyc]

Thanks for the info very handy, but its not quite there, the piece missing is....I need the formula in A1 to look in a separate sheet in column C for the first cell in with data in it and report back all adjacent cell information, then in the next cell A2I need it to find the second cell in column C with data and report back adjacent call info....all help greatly appreciated Thanks

[oldchippy]

Hi leveyc,

Sorry for the delay in coming back to you, but I've been trying to come up with a solution to your problem. I've found a formula on Chip Pearson's site that reforms a column to eliminate blanks, the problem I'm having with the formula is writing it on Sheet1 to look at column C on Sheet2 once we've achieved that it a case of then doing a Vlookup for the other information. I'll keep you posted.

Here's the link in question

http://www.cpearson.com/excel/noblanks.htm

[Bryan Hessey]

The formula in the Excel Tip is incomplete, it should be

=INDEX(A2:A7,MATCH(TRUE,A2:A7<>"",0))

To apply Array formula, so the the braces appear in the formula bar { } like so

Select the cell, press Ctrl+Shift+Enter

For the second part of your question take a look at this link, as a lookup looks like the solution to your problem

http://support.microsoft.com/kb/214252

Hi,

To get column C from the first non-blank row of Sheet2,

try

=OFFSET(Sheet2!A1,MATCH(TRUE,Sheet2!A1:A50<>"",0)-1,2)

CSE (CTRL/Shift/Enter)

to get the following row column B use

=OFFSET(Sheet2!A1,MATCH(TRUE,Sheet2!A1:A50<>"",0)-0,2)

and the next row

=OFFSET(Sheet2!A1,MATCH(TRUE,Sheet2!A1:A50<>"",0)+1,2)

however, looking up the 'next non-blank' if further blanks appear in the range could be difficult.

hth
---

[oldchippy]

Hi Bryan,

How about using the formula from Chip's web page to create a list of non-blanks on Sheet1 from column C (with the blanks in) on Sheet2 , then using a vlookup table to get the additional data from the other columns on sheet2

[oldchippy]

Thanks for the info very handy, but its not quite there, the piece missing is....I need the formula in A1 to look in a separate sheet in column C for the first cell in with data in it and report back all adjacent cell information, then in the next cell A2I need it to find the second cell in column C with data and report back adjacent call info....all help greatly appreciated Thanks

Hi leveyc,

This is the only way I can see how to get the data from the non-blank cells from a separate sheet. Hope this helps - let me know?

[leveyc]

Oldchippy

Thank you, I think its nearly there, I guess the only way to get it complete is to send you the worksheet so you can see what I'm trying to work on, I have attached part copy with some notes, do appreciate your help

Thanks

leveyc

[oldchippy]

Can not open your zip file, are you using Excel 2007, if so save it as 2003, then I will be able to open it. If you are not using 2007, try zipping it again.

[leveyc]

OldChippy

Thanks

[oldchippy]

Hi leveyc,

Try this one, I think this will work for you?

[leveyc]

Hey OldChippy


It works like a dream, thanks very much

[oldchippy]

Glad to help - thanks for the feedback