Would not work for me
leveyc wrote on February 02, 2007 22:34 EST |
| 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 wrote on February 03, 2007 07:07 EST |
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
[url]http://support.microsoft.com/kb/214252[/url] |
Would not work for me
leveyc wrote on December 31, 1969 19:00 EST |
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
|
leveyc wrote on February 05, 2007 12:00 EST |
| 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 wrote on February 07, 2007 07:56 EST |
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
[url]http://www.cpearson.com/excel/noblanks.htm[/url] |
Bryan Hessey wrote on February 07, 2007 08:40 EST |
[QUOTE=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
[url]http://support.microsoft.com/kb/214252[/url][/QUOTE]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 wrote on February 07, 2007 08:49 EST |
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 wrote on February 07, 2007 10:20 EST |
[QUOTE=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[/QUOTE]
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? |
I think its nearly there
leveyc wrote on February 07, 2007 21:42 EST |
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 wrote on February 08, 2007 04:20 EST |
| 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. |
Try this one
leveyc wrote on February 08, 2007 16:41 EST |
OldChippy
Thanks |
oldchippy wrote on February 09, 2007 04:32 EST |
Hi leveyc,
Try this one, I think this will work for you? |
Thank You
leveyc wrote on February 09, 2007 10:54 EST |
Hey OldChippy
It works like a dream, thanks very much |
oldchippy wrote on February 09, 2007 16:05 EST |
| Glad to help - thanks for the feedback |
The 11 Immutable Laws of Internet Branding
