Need data result in last cell of array that contains blank cells

Example:  

A1=200
B1=300
C1=400
D1=[blank]
E1=[blank]
F1=[blank]
G1=(last cell from above that contains data...i.e. C1)

I'm a bit of an Excel novice so could really use a little help.

Thanks!



0
Utf
2/8/2010 8:21:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
764 Views

Similar Articles

[PageSpeed] 8

Assuming that range will only contain numbers,
try something like this:

=LOOKUP(10^99,A1:F1)

or...if you want to avoid an error when there are no numbers:
=IF(COUNT(A1:F1),LOOKUP(10^99,A1:F1),"")

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"smesurfer" wrote:

> Example:  
> 
> A1=200
> B1=300
> C1=400
> D1=[blank]
> E1=[blank]
> F1=[blank]
> G1=(last cell from above that contains data...i.e. C1)
> 
> I'm a bit of an Excel novice so could really use a little help.
> 
> Thanks!
> 
> 
> 
0
Utf
2/8/2010 8:29:03 PM
Ron, your first recommendation worked perfectly.  

THANK YOU!

"Ron Coderre" wrote:

> Assuming that range will only contain numbers,
> try something like this:
> 
> =LOOKUP(10^99,A1:F1)
> 
> or...if you want to avoid an error when there are no numbers:
> =IF(COUNT(A1:F1),LOOKUP(10^99,A1:F1),"")
> 
> Does that help?
> ***********
> Regards,
> Ron
> 
> XL2003, WinXP
> 
> 
> "smesurfer" wrote:
> 
> > Example:  
> > 
> > A1=200
> > B1=300
> > C1=400
> > D1=[blank]
> > E1=[blank]
> > F1=[blank]
> > G1=(last cell from above that contains data...i.e. C1)
> > 
> > I'm a bit of an Excel novice so could really use a little help.
> > 
> > Thanks!
> > 
> > 
> > 
0
Utf
2/8/2010 10:51:02 PM
Reply:

Similar Artilces:

Excel 95 user need help
Buying newer version is not an option. I try to open excel files and it says...not a valid file format. Searched updates and downloads for a converter or somethint to open new versions but I cannot find anything that works...tried the SP3 for xp said did not find expected version and quit. Cannot get compatibility 2002 to function Am I up the river on this or is there something...some update that will help? Hi Noobness, Excel (and its file formats) has come a long way since Excel '95 ... It's been unsupported now for quite some years. -- Cheers macropod [Mic...

Displaying active cell in upper left corner
When selecting a range by name, if the range is not in the cells displayed at the time, Excel then shows the first cell of the range at the bottom of the display. This is also true if I have hyperlinked to a particular cell as in an index. How do I get the active cell shown at the upper left corner of the display instead? :) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Sub setscroll()'no need to activate first ActiveWindow.ScrollRow = [a5].Row End Sub This ...

set cell margins
Does anyone know how to set cell margins in Excel? I'm preparing a long list of items for a committee review, and I think it would be more user/reader friendly with some white space. The columns are mixed numbers, dates, and text, but the largest cells are text. June Format>Cells>Alignment. Look at the various options in "Horizontal" and "Vertical" Top, bottom, centered, justified, right indent, left indent and a gang of others. Also row heights and column widths can be manipulated. Gord Dibben Excel MVP On Sun, 5 Dec 2004 10:35:28 -0800, "Junebug...

Creating a Chart from Cells that use a List Box
Hi, I am trying to create a number of charts (e.g., pie, line etc) based on data that is slelected from a list box (i.e., data validation). What i would like to do is create the charts before any data is entered (if that makes sense) and as data is entered the charts will display this. ...

Linking data to drawing
Dear all, Can anybody guide me, how to link the data in excel to make drawin from its drawin g options like lines, circles etc -- sumit487 ----------------------------------------------------------------------- sumit4879's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1628 View this thread: http://www.excelforum.com/showthread.php?threadid=27687 Maybe you're referring to linking a cell's contents to display inside an autoshape ... If so, play around with this .. Draw an autoshape, say: a circle (oval) on the sheet Right-click on it > Select "...

Form problems in a table with merged cells
I have a spreadsheet I've been using for several years in which I had set up a ribbon button to show a data entry form using the table headers. I merged some cells in an area adjacent to the table and the form would no longer work. Clicking the ribbon button simply gave the message that the command could not be used in a table or range containing merged cells. I unmerged the offending cells but it still doesn't work. I'm stumped - can anyone please help> -- John ...

how can I split a single cell diagonally in Excel 2000
Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...

Tools/Options/Charts-Active cells is dimmed. Want to select leave
I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in chart, but still showed zeros. When I went to Tools/Options/Charts, the Active Cells area was dimmed and I could not select "leave gaps". Any suggestions? Thanks, Hi, Was the chart selected when you did Tools>Options ? Cheers Andy teds wrote: > I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in > chart, but still showed zeros. When I went to Tools/Options/Charts, the > Active Cells area was dimmed and I could not select "leave gaps". Any > suggestions? &...

VBA code to insert pictures in a column of cells
I have a folder of 99 pictures with filenames: image01.jpg, image02.jpg up to image99.jpg. All of these images are small, and the same size. I want to insert them (in order) in a column of cells (A1, A2, to A99) using VBA. The images should be embedded, not linked. Resizing cells to the right size would be a nice extra, but not necessary - it's easy in this case to resize manually. Thanks for looking at my question - and I hope to hear some good suggestions! ...

Need help with a function
Hi I'm sorry but I'm not too much familiar with Excel. I have some listing in column A For example in A1 I have: D:\blabla\folder1\file1.xxx in A2 I have: D:\blabla\folder2\file2.xxx in A3 I have: D:\blabla\folder3\file3.xxx I want the word between the 2nd and 3rd '\' to be in color red. So in A1 folder1 should be in red, in A2 folder2 in red etc I could do it manually but there are like 3000 lines. It would take forever. Is this simple to achieve with a script/macro/? ? I hope someone can help me. Thanks in advance. Christophe Presumably you want to pick that value o...

I need to round up to the nearest 50.
I am trying to calculate the number of raw material bags I need in a batch. If I use the mround function is will round down. Is there a way to only round up? =ROUNDUP(A21/50,0)*50 or =CEILING(A21,50) -- HTH Bob Phillips "karenm" <karenm@discussions.microsoft.com> wrote in message news:DBE54F5E-0524-4DFF-BC52-D966BBEE26E9@microsoft.com... > I am trying to calculate the number of raw material bags I need in a batch. > If I use the mround function is will round down. Is there a way to only > round up? > Hi karenm, First of all, I believe MROUND rounds...

Need help with a simple Time calculation
Hi I'm a little new in Excel, I need some help with at sheet, that are able to calculate time something like this: text1 3:30 (3 min 30 sec) text2 2:10 3:40 (result of text1 and test2) text3 1:40 8:20 (result of text1, 2 and 3) total 7:20 Thanks in advance if the fields have times in them as they appear, just add them like any other cells and format as mm:ss or hh:mm:ss Regards Trevor "Bjarne Hansen" <bhansen1@hotmail.com> wrote in message news:42ed31aa$0$7385$ba624c82@nntp02.dk.telia.net... > Hi > > ...

ways to search for data...
I have my excel as a database with cities, names, addresses, emails, etc. I was wondering if there is any way to search on excel to pull up all columns of a certain city. EX. Say i wanted to find out everyone in my database who lives in cleveland, and i want ONLY all cleveland contacts to show up, is there a way to do that? For now, i go to find, and type in cleveland, but that still shows everyone else on the list (and highlights the cleveland ones). I would like it to show only every cleveland contact. Any help? Hi Rocker, This seems an ideal candidate for AutoFilter. If you...

Hiding Columns based on cell value
I am a novice when it comes to writing VBA code, I would greatly appreciate any help I can get in figuring out my ?. I would like to write a macro that automatically hides columns of data based on the value of a cell (I2) with a picklist. Cell I2's picklist is monthly values (formatted as Jan-10 though Dec-10 but real values are 1/1/2010 through 12/1/2010). I have a range that contains work week end date values (1/8/2010 to 12/31/2010) in L6:BK6. I would like to have the macro hide columns that are less than date value chosen in I2. For example, if a user selects "...

Displaying Image(RGB888 data ) (buffered data ,not from file) on a Dialog box
Hi, I want to Display the Picture on a dialog box I have the Image(RGB888) data Which is taken from the driver I want to display it on the window I tried with CreateBitmap(320,240,1,24,NULL); but it is not working How can i proceed Thanks See if this will help http://www.kbalertz.com/kb_Q94326.aspx#appliesto AliR. <darshan.tapdia@gmail.com> wrote in message news:1155227161.797014.51780@i42g2000cwa.googlegroups.com... > > Hi, > > I want to Display the Picture on a dialog box > > I have the Image(RGB888) data Which is taken from the driver > > I want ...

transferring program and data to new computer
What is the easiest way to transfer both the program and the data files for money 99 (running on an old computer with windows 95) to a new computer using windows xp professional? this is really about the only thing I would like to transfer from the old to the new machine. does anyone have a simple solution to this? My computer skills are marginal at best. any help would be appreciated. thanks R. B. You will have no choice but to reinstall the application from the original CD. Transfer your .MNY data file the same way you transferred any other data file: network, zip drive, burn a ...

Clear data without deleting formulas
We use several spreadsheets on a monthly basis, so every month must delete the data that was input for the prior month to put in the new data. However, we also need to be careful not to delete any of the formulas while we're at it. What is the best way to clear out data without deleting the formulas too? Deleting contents row by row (or column by column) takes too long. Regards! Winston Select your range of cells that can be cleared. Be careful not to select too much (but you can eliminate formulas later). Now with that range selected: Edit|Goto|Special|Constants hit the delete...

Attach Data Table to Bottom of Plot Area
Using Excel 2007, I have a graph with the x-axis showing date values (Jan-09, Apr-09, etc.). Beneath the plot area, I have a data table with the top row showing the same date values. A work associate suggested I delete the values on the x-axis and attach the data table to the bottom of the plot area. I can't seem to find any info on how to do this. Any suggestions would be greatly appreciated. Thanks, Mike M. The contents of the data table are defined by Excel with little, if any, discretion given to the user. You can simulate the effect of a custom data table. See Custom Chart ...

isv products needed for tips & food items
Hello: I have a prospect that sells vine by the bottle and case, but they also have a bar area. At the bar, they sell glasses and tastes of wine, and or cheese platters, and pizza, so they need some of the aspects of a hospitality program. In particular, adjustments when ringing up the sale for tips, and printing or screen display in the kitchen area only the food items for preparation management. The prospect has one location now, but will be expanding shortly to two locations. Please let me know if you can point mein the direction of an ISV that can help with these add-ons. ...

need to join/combine two tables into one table
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Is there a way to make two tables into one table? Somehow the table I was working on got broken into two tables and I need to rejoin them. I've searched the list for 'join tables' and 'combine tables' and get a thousand hits dealing with all 'tables' issues. I must be searching for the wrong thing. Select the empty paragraphs separating the 2 tables (turn on the � so you can see them) then do a forward delete (fn+delete on most laptops). HTH |:>) Bob Jones [MVP] Office:Mac On 8/6/...

Office 2008 finds update is needed, but won't update...
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel When I check for updates, Office 2008 recognizes that I need an update (12.0.1). It downloads and when the software tries to search for a drive that needs the update, it can't . I get this message next to a STOP sign with exclamation point... You cannot install Microsoft Office 2008 for Mac 12.0.1 Update on this volume. A version of the software required to install this update was not found on this volume. This doesn't make sense to me. Here is what I have done so far... Had Office 2004. Installed Office 200...

need query results showing Table 1 records both w/ and w/o matches in Table 2.
I am trying to write a query in Access 2000 and would appreciate some guidance as to how this should be approached. I am at a very basic skill level. My goal is to create a query that determines what type and size of packaging should be used depending on the mix of products purchased. We know that if 3 products from category X and 7 from category Y are purchased then 'the medium corrugated box' should be used (and we have reduced this to a grid), but we would like the process automated so that a simple list can be printed. Simplified (but hopefully adequate): I have 2 tables (Tb1...

Is there Formula to Tab to certain cells
Is there a formula to tab through certain cells? I have a form with about 15 cells that I would like to access easily through the tab key....is there any way to do that? One way to explicitly control the exact "next cell of focus" selection, is to select the cells in the desired order of travel, and then preserve this ordered movement by creating a named range. This old post describes the steps that can be taken to create such a "named range". http://tinyurl.com/39vzv -- HTH, RD ============================================== Please keep all correspondence within ...

SOP Blank Report to show in print Picklist
Hi All, I just modified SOP Blank Order Form by adding a Calculation field and Saved it. Now, when I go to Transaction Sale History, open it, and pick one Document Id. It's showing data including of Total amount. However, when I click print and I check Order checkbox but there's no SOP Blank Order Form showing up, it's just showing Blank Form, Short Form, Long Form in dropdown list. So, how can I add SOP Blank Order Form I did modify to show in dropdown list so that when I click Print it'll show all data containing custom calculation field correctly? Please advice ...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...