To have a 'fixed cell' be equal to the last data entered cell in a column

Good Evening All,

I have a worksheet, example below.
I have frozen the panes to always show rows 1-3.
I wish the cell A3 to be the same as the last 'non-blank' cell in Column
A.(See explanation below).

        A                B
1
2    Header        Header
3__________________
4    M1004
5    M1005
6    M1002
7    M1003
8    M1006
9    M1001
10

So in this case, at present, A3 would be M1001,(A9),  but when I enter a new
value in A10, (eg M1008), I would like A3 to automatically update to A10 ie
M1008.
I think that maybe INDIRECT or OFFSET maybe involved but am fairly
unfamiliar with these functions.
Any help on this would be most appreciated.
Cheers,

Mathew


0
mbp1 (14)
8/22/2003 4:29:40 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
471 Views

Similar Articles

[PageSpeed] 44

Hi Dave,
Thank you very much, I had the feeling that OFFSET would be involved.
However, the range in Column A is ever increasing, and I would like the
formula to accommodate this ever increasing range.
Any thoughts?
Cheers for your help
Mathew
"dave" <breitenbach@cfx.com> wrote in message
news:08d201c368cd$9d5873f0$a001280a@phx.gbl...
> put this formula in a3:
>
> =OFFSET(A4,COUNTA(A4:A10000)-1,0,1,1)
>
> I used 10000 arbitrarily...it should be whatever the
> bottom limit is on your data.
>
> dave
> >-----Original Message-----
> >Good Evening All,
> >
> >I have a worksheet, example below.
> >I have frozen the panes to always show rows 1-3.
> >I wish the cell A3 to be the same as the last 'non-blank'
> cell in Column
> >A.(See explanation below).
> >
> >        A                B
> >1
> >2    Header        Header
> >3__________________
> >4    M1004
> >5    M1005
> >6    M1002
> >7    M1003
> >8    M1006
> >9    M1001
> >10
> >
> >So in this case, at present, A3 would be M1001,(A9),  but
> when I enter a new
> >value in A10, (eg M1008), I would like A3 to
> automatically update to A10 ie
> >M1008.
> >I think that maybe INDIRECT or OFFSET maybe involved but
> am fairly
> >unfamiliar with these functions.
> >Any help on this would be most appreciated.
> >Cheers,
> >
> >Mathew
> >
> >
> >.
> >


0
mbp1 (14)
8/22/2003 4:57:19 PM
Got it to work, Cheers again Dave
"Mathew P Bennett" <mbp1@btinternet.com> wrote in message
news:bi5i1e$oa5$1@titan.btinternet.com...
> Hi Dave,
> Thank you very much, I had the feeling that OFFSET would be involved.
> However, the range in Column A is ever increasing, and I would like the
> formula to accommodate this ever increasing range.
> Any thoughts?
> Cheers for your help
> Mathew
> "dave" <breitenbach@cfx.com> wrote in message
> news:08d201c368cd$9d5873f0$a001280a@phx.gbl...
> > put this formula in a3:
> >
> > =OFFSET(A4,COUNTA(A4:A10000)-1,0,1,1)
> >
> > I used 10000 arbitrarily...it should be whatever the
> > bottom limit is on your data.
> >
> > dave
> > >-----Original Message-----
> > >Good Evening All,
> > >
> > >I have a worksheet, example below.
> > >I have frozen the panes to always show rows 1-3.
> > >I wish the cell A3 to be the same as the last 'non-blank'
> > cell in Column
> > >A.(See explanation below).
> > >
> > >        A                B
> > >1
> > >2    Header        Header
> > >3__________________
> > >4    M1004
> > >5    M1005
> > >6    M1002
> > >7    M1003
> > >8    M1006
> > >9    M1001
> > >10
> > >
> > >So in this case, at present, A3 would be M1001,(A9),  but
> > when I enter a new
> > >value in A10, (eg M1008), I would like A3 to
> > automatically update to A10 ie
> > >M1008.
> > >I think that maybe INDIRECT or OFFSET maybe involved but
> > am fairly
> > >unfamiliar with these functions.
> > >Any help on this would be most appreciated.
> > >Cheers,
> > >
> > >Mathew
> > >
> > >
> > >.
> > >
>
>


0
mbp1 (14)
8/22/2003 5:09:15 PM
Reply:

Similar Artilces:

Get drive letter for old slave hard drive while keeping data
Hello, My master hard disk has failed and I have successfully replaced it with a new one and reinstalled windows XP etc. Before the crash I had a second slave drive with some data I would like to keep on it. When I connect this drive, it shows up in disk management, but does not get assigned a drive letter. How can I get windows to assign a drive letter to the drive without formatting it and losing all of my data? Both drives are IDE drives, the master is jumpered to cable select and is on the master cable, the slave seems to have lost its jumper, or never had one, but sh...

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

Excel, how do I change the column headings from letters to number
I have a spreadsheet that has numbered columns as opposed to the standard letters. How can I change this back to letters? Go to the Tools menu, choose Options, then the General tab. There, uncheck the R1C1 reference style setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "lazybee" <lazybee@discussions.microsoft.com> wrote in message news:030962A3-A111-4780-93C0-1D28003F1F20@microsoft.com... >I have a spreadsheet that has numbered columns as opposed to the >standard > letters. How can I change this ...

I want year in one table to be less or equal year in another table
Hi I have some problems writing a query and I hope someone can help me. I have a database with serveal tables. In one table I have this information, Lake ID-number, treatment, year for treatment. In another table I have Lake ID-number, fish species (I am intrested in pike), year when pike is present. I want to find all lakes that have pike present before the treatment was done, I want the year in the second table to me less or equal the year in the first table. Is there a easy way to do this? Thanks Try something like this substituting your table and field names. S...

Removing Last 5.5 Server that has an IMC
I am following the steps in the article: http://support.microsoft.com/kb/883407/ before removing my last 5.5 server and moving the smtp services over to my 2003 server. The scenario that applies is single\last 5.5 server (it is a single AND last :) Step 3 - verify the smtp connector is seen in your exchange server 5.5 configuration - is the step that keeps failing. I have repeated step 2 now a couple of times and waited for about 3 hours now and the connection still does not show up. I also see no obvious (or any) errors in the event log on my 5.5 server. if I can successfully send a...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Hiding empty rows and columns
Does anyone know the code for hiding all blank rows and columns in a worksheet. Thanks -- Message posted from http://www.ExcelForum.com Hi try the following (adapted from: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows) Public Sub HideBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count > 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFuncti...

Excel moving horizontal columns to vertical
I am trying to find a simple way of moving data in horizontal columns (referencing the key in column 1) to vertical colums (still referenceing the same key). This would mean automatically repeating the values in column one for every entry in the moved columns. I can do this through the pivot table, but this is a long drawn out process. Any ideas I'm not sure if this will work for you, but you can try this: - Highlight & copy on the horizontal value you want to make vertical - Now highlight the vertical area you want these value to got - Right Click when highlighted & choose...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

Transformation of data into columns
Hi, I have the data from a flattened spreadsheet in a table in the following form: f1 f2 f3 period to: Scheme1 Scheme2 31/01/2005 Net Gross 28/02/2005 Net Gross 31/03/2005 Net Gross 30/04/2005 Net Gross 31/05/2005 Net Gross 30/06/2005 Net Gross 31/0...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

Twist Data?...
Hi all... I've got the following in a dataset: 19 30 1200 FI FI 20030906 36 19 30 1324 FI FI 20030906 36 and I want the following result: 19 30 1200, 1324 FI FI 20030906 26 Any guess on how to do this? thanks much!... Hmmm... Not quite clear: The data is arranged in columns, I assume It looks as if both items in the same column are the same, you want just one item, otherwise the one in the top row first, then the one from the bottom row. But what about the last 36s that should yield 26? Typo? Does the dataset have more columns or more rows or both? And how big is it?...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

moving payables data from open to history
Hello: A client says that someone imported data about a year or two ago into Great Plains from their AS400. Many payables documents that were imported should have been coded during the import as open, instead of history. The client knows that she can take care of this herself within two hours, by simply turning off the posting to the GL and entering and posting the payables documents to move them to history. But, she is wondering if there is a quick and easy way to do this on the back-end. I'm familiar with the open and history payables tables within GP. And, I know through a T...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Getpivotdata and greater than or equal to
I'm experimenting with using a pivot table as an intermediate step in a report I'm doing that has been slow. It seems to help speed up the process a bit, I just need help filtering down to the dates I need. Each transaction has a date associated with it, I have gotten the getpivotdata to work except for being able to have it only include transactions with dates after my begdate and before my enddate. I need to figure out how to get something like this to work: =GETPIVOTDATA("Sum of Lbs",'sheet2'!$A$3,"Customer",A4,"Budget LOT #",B4,&q...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...