search and return cell value

I hope that someone may be able to help.
I have a worksheet that has 7 fixed columns and a variable number of rows.
The rows can range from 1 to many. Cell A1 is dependent upon the value in
the last row in column G. I can locate this with nested "if" statements or
by creating a new column H that will test to see if the next row contains
values.
Is there any function that I can put in cell A1 that will locate the last
row in column G that contains a value and return the value of this cell?
Many Thanks


0
5/21/2004 1:48:36 PM
excel 39879 articles. 2 followers. Follow

2 Replies
643 Views

Similar Articles

[PageSpeed] 21

Hi
if you have no blank rows in between try
=OFFSET($G$1,COUNTA(G:G)-1,0)

--
Regards
Frank Kabel
Frankfurt, Germany

"The Jackson Family" <jackson.family@optusnet.com.au> schrieb im
Newsbeitrag news:40ae07af$0$31679$afc38c87@news.optusnet.com.au...
> I hope that someone may be able to help.
> I have a worksheet that has 7 fixed columns and a variable number of
rows.
> The rows can range from 1 to many. Cell A1 is dependent upon the
value in
> the last row in column G. I can locate this with nested "if"
statements or
> by creating a new column H that will test to see if the next row
contains
> values.
> Is there any function that I can put in cell A1 that will locate the
last
> row in column G that contains a value and return the value of this
cell?
> Many Thanks
>
>

0
frank.kabel (11126)
5/21/2004 1:49:37 PM
Thanks Frank,
It worked and will save me a lot of time
regards
Allan Jackson
Melbourne
Australia

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OrdHdpzPEHA.3380@TK2MSFTNGP11.phx.gbl...
> Hi
> if you have no blank rows in between try
> =OFFSET($G$1,COUNTA(G:G)-1,0)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "The Jackson Family" <jackson.family@optusnet.com.au> schrieb im
> Newsbeitrag news:40ae07af$0$31679$afc38c87@news.optusnet.com.au...
> > I hope that someone may be able to help.
> > I have a worksheet that has 7 fixed columns and a variable number of
> rows.
> > The rows can range from 1 to many. Cell A1 is dependent upon the
> value in
> > the last row in column G. I can locate this with nested "if"
> statements or
> > by creating a new column H that will test to see if the next row
> contains
> > values.
> > Is there any function that I can put in cell A1 that will locate the
> last
> > row in column G that contains a value and return the value of this
> cell?
> > Many Thanks
> >
> >
>


0
5/21/2004 2:20:49 PM
Reply:

Similar Artilces:

"unable to display all the cells due to low memory"
I have a co-worker getting this error whenever I try to open my Tasks folder. He has a lot of tasks (1000+), but they are all very small with no attachments. Running Outlook 2000 on Exchange 2000. Thanks! -- Remove 'spam' from email address to contact me directly ...

how do i automatically archive old cell contents to a separate .
I have a worksheet that contains lists of "action items", that is used to keep track of jobs that need to be done. I want to be able to automatically move the contents of a row of cells to another worksheet once the action item has been completed. In other words, once I have changed the value in a cell to "yes" (the action has been completed), the information is archived in a separate worksheet. Is this possible, and how? I'd keep them in place, but use Data|Filter|autofilter to show/hide what I want. I think it makes life much more simple--especially when you...

how to determine the color with a value of column
I have the following problem. I have a percent column chart. I have only one series. In this case all columns will have the same color on the chart. I want these column colored regarding to the value of a column. I mean: - value: 80% color: yellow - value: 100% color: greeen - value: 40% color: red How to condition the color of the column with a percentage shown on the chart? I mean something like the conditional formatting, but on charts? Thanks in advance Marcin Jon Peltier has instructions for conditional charts: http://www.peltiertech.com/Excel/Charts/format.html#CondChart Kamyk ...

Search information using the main form
I am making a access db with a main form and linked a subform. I can show the record by entering the primary key in the main form and the subform will show the relevant information --- that is ok! But i try to enter infomation in the other text box in main form but no action only the table content changed! ---- problem How to use the other text boxes to search the record and the result can be seen in main form and of course related record will be shown in the sub-form as using the primary key. How to prevent the content to be changed as mentioned but only after the button will created lat...

instantaneous cell value
This is probably incredibly simple but I've been going in circles with it for some time. I'm looking for a way to grab and hold the instantaneous value of a cell whose contents are constantly being updated. Any thoughts? You can "fix" the values of a cell by <copy>, then select another cell (maybe in another sheet) and Edit | Paste Special | Values | OK. This would leave the formula in the original cell to continue to update itself. Hope this helps. Pete thanks for the reply. this works as a "manual" solution but how can I assign only the contents o...

Issue with cell ranges and formulas
I've got an interesting issue that I'd like some help with. I'm doing a performance analysis where I'm trying to average some numbers. Here's what I did to create the worksheet: In cell A1 enter 'Time', in B1 enter 'CPU #0', in C1 enter 'CPU #1', in D1 enter 'CPU #2', in E1 enter 'CPU #3', and in F1 enter 'Average'. I then format the cells in column A to use the 'time' category and '1:30 PM' type. Now I enter in cell A2 '8:30 PM' and A3 with '8:35 PM'. Next enter '17.368' in ...

Splitting cells into multiple records
Hello, Here is what I am trying to do. I have an access DB that Excels pulls info out of and puts in a nice little format. My issue is that some of the fields have multiple records in it. Here is an example of the DB: First Name | Last Name | Issue | Remediation You would only have one first and last name, but there might be multiple instances in the issue and remediation cells. First Name | Last Name | Issue | Remediation John |Smith | Issue Number 1 | Remediation Number 1 | Issue Number 2 | Remediation Nu...

Soft Returns
Hi, I've got a column of data that has multiple values entered into the cells (derived from another formula) eg cell A2 has List Item 1 List Item 2 List Item 4. There can be any number of items and they are not a set length. Can I automatically put soft returns (Similar to pressing Alt + Enter) in between the values so that the cell looks like: List Item 1 List Item 2 List Item 4 I could easily put a separator into the cell. Hope someone has an idea as this is driving me insane. Cheers John Hi try in your formula ="Item1" & CHAR(10= & "Item2" and...

Filtered cells return after save???
Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of...

How to combine text from 3 cells into 1 cell
How to combine from 3 text-cells into 1 cell with space between them? See your other post for answer. John "JOF" <JOF@discussions.microsoft.com> wrote in message news:35A395E5-81A3-428E-BED7-1DF41F5370FF@microsoft.com... > How to combine from 3 text-cells into 1 cell with space between them? =A1&" "&B1&" "&C1 or =CONCATENATE(A1," ",B1," ",C1) "JOF" wrote: > How to combine from 3 text-cells into 1 cell with space between them? try this if your text are in a column A in B2 =A2&" "&...

Write scatterplot point ID to a cell
Hello all I'm trying to click a point in a scatterplot and have its ID (label, values, whatever) written to a cell. Any ideas? Thanks jcamoes Hi, Have a look at Jon Peltier's article "Chart Events in Microsoft Excel" http://www.computorcompanion.com/LPMArticle.asp?ID=221 Cheers Andy jcamoes wrote: > Hello all > > I'm trying to click a point in a scatterplot and have its ID (label, > values, whatever) written to a cell. Any ideas? > > Thanks > > jcamoes > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks! That&#...

Formula to copy multiple cells onto another sheet
I have a worksheet containing names and address that are repeated onto other sheets based on criteria in another column. What formula if any can I use to determine if that person meets that certain criteria to automatically copy the 3 columns that the name, phone, and address are located in to another sheet? A little more info: I have multiple worksheets and one master list containing 6 columns: First, Last, Birthday,Phone, and Address, and class. In the class column is a variety of class names. I would like to write a macro (I have never done this before!) ,if possible, that will ...

Office 2007 Styles create issue with "Too many different cell form
I'm not sure if anyone has come across this issue. I have a file that is used by both 2007 and 2003 users. Everything was working fine until one day the 2003 users were getting "Too many different cell formats." The issue was that there were now 3000+ styles on the spreadsheet. There were now custom styles created for each of the new cell styles in 2007 like the accents. It looks like everytime this was saved in compatibility mode, this was turned into a custom style. Eventually there were Accent1, Accent 1 1, Accent 1 2, etc. This looks like it will continue to...

Sticking cells
BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 and 2 on my spreadsheet to 'stick', so when I scroll down my sheet the top 2 rows are always at the top. How can I do this? Thanks in advance. -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" "Laura ( '_' )" <laura@invalid.example.com> wrote in message news:u%235COkd8FHA.4012@TK2MSFTNGP14.phx.gbl... > BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 > and > 2 on my spreadsheet to 'stick', so when...

Insert Cell Text Into Chart
I have a cell of text(on sheet 1) that I want to paste into the top portion of a chart (on sheet 2) - I have done this before, but I cannot remember how - HELP! Hi insert a textbox, select this textbox and enter the equation sign '='. Now select your cell on sheet 1 and hit ENTER -- Regards Frank Kabel Frankfurt, Germany Karen wrote: > I have a cell of text(on sheet 1) that I want to paste > into the top portion of a chart (on sheet 2) - I have done > this before, but I cannot remember how - HELP! Thank you for your help - I'm sorry, maybe I didn't explain it ...

Look up multi values
I’m struggling figuring out how you would do the following? Work sheet data. A1 =25 B1 = 40 C D 1 25 30 2 25 40 3 27 41 4 28 42 What I want to do is lookup the values 25 & 40 (A1 & B1) from table C1 D4 Matching the value 25 in Column C & 40 in Column D & return the location in the table to Cell A2? If there is no matching value, were decimals come into play, i.e. 25.8, then return the next largest from column C with its parent value from D, Returned value = 2 or 3 if 25.5. Sounds simple but I get lost very quick! Any Help greatly appreciated. one way. This is an array...

"Checking for updates" does not stop searching
I just installed Microsoft Update and when it went to see which updates I needed it just kept going on and on and on with the search. Its message was "Checking for the latest updates for your computer." Does anyone have any suggestions? Much appreciated. Olaf Olaf Egeberg wrote: > I just installed Microsoft Update and when it went to see which updates I > needed it just kept going on and on and on with the search. Its message was > "Checking for the latest updates for your computer." > > Does anyone have any suggestions? > > Much a...

How to change the active cell outline indicator color in Excel?
I want to change the outline color of the active cell indicator to something other than black, so that it is more distinct on screen, since I often have black gridlines in my worksheets. How can this be done? maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm YoYo74 wrote: > > I want to change the outline color of the active cell indicator to something > other than black, so that it is more distinct on screen, since I often have > black gridlines in my worksheets. How can this be done? -- Dave Peterson I had a need for...

universal formatting of all cells in all worksheets
I have 140 sheets in an excel document. In column AF I want the width to be set to 15.86. Instead of going to every sheet and making the change, is there a way that I can set the width for that column in all of the sheets right click on a sheet tab>select all sheets>set whatever on the sheet you are on>select any One sheet. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "jeremyrod" <jeremyrodriguez@cmsmechanical.com> wrote in message news:0b4301c351e9$39fe41e0$a501280a@phx.gbl... > I have 140 sheets in an excel document. In column AF I >...

Will excel 2003 format a cell to the year?
It seems like it should be obvious, but I am finding it impossible to get excel to do a date format that recognizes a year. Is there some way? I typed a date in A1 (09/28/2006) and used format|Cells|number tab|custom category|yyyy with my USA settings. kateofmd@msn.com wrote: > > It seems like it should be obvious, but I am finding it impossible to > get excel to do a date format that recognizes a year. Is there some > way? -- Dave Peterson ...

selecting non-contiguous cells
In EXCEL 2007,trying to select multiple non-contiguous cells. Unlike in Excel 2003, where holding down the CTRL key enables selection of non-contiguous cells, that method does not work. How can I do this in Excel 2007. Works exactly the same in Excel 2007 as in 2003. Maybe because the color is so washed out you don't clearly see the selections. Try SHIFT + F8 to get into Add mode then select non-contiguous cells. Any difference? Gord Dibben MS Excel MVP On Mon, 1 Feb 2010 13:43:01 -0800, facinadr <facinadr@discussions.microsoft.com> wrote: >In EXCEL ...

Filtering Lookup values
I want to filter the lookup values when you click on "source campaign" on an opportunity to only include active campaigns. I'm not familiar with JScript and creating OnLoad statements. Can someone help me with the code for this? Thanks! Lisa ...

Assigning Cells in worksheets to other data in other worksheets.
Assigning Cells in worksheets to other data in other worksheets (Theoretics) What my intensions are; I have a unique identifier in worksheet 1. e.g. 41-01, a concatenatio of two cells. I have data corresponding to the unique identifier in worksheet 2, *th reason's being i needed a unique identifier to relate the data, i hav seperated textual and numerical data into two worksheets. Worksheet 1 = Numerical Worksheet 2 = Textual through cells "A1:A50" (worksheet 1) i have the unique identifiers e.g. 41-01 through cells "A1:A50" (worksheet 2) i have the text e.g. tex...

Excel 2007: Selection windows slows down when highlighting cells
Yeah, i know this is a minor issue but it's adding to my frustration when using Office 2007. When I'm selecting a range of cells (top/down) Excel knows when I'm getting close to the end of the range of cells with data in them. When I get close to the end it stops scrolling down the page and I have to fiddle with my mouse to get it to finish highlighting them. Does anyone have a cllue howo to turn this feature off ? Thanks in advance for any advice. I, too, have found the conversion to 2007 slowing my machine down. I cannot address your issue, but I rarely us...

Chart X Axis value label on both left and right sides
Can I add a second X axis value on both left and right sides of a chart? At present scrolling the chart to the right there are no axis label values to easily read the chart. Beemer Beemer, You have to have at least two Data Series to do that. Right click on the second of them, choose Format Data Series, then on the Axis tab, choose secondary axis. Note that Excel may change the relative values between the first and second axes, so you should set the Max and Min for both manually. HTH, Bernie MS Excel MVP "Beemer" <Beemer@nowhere.com> wrote in message news:MPG.1b2a2779...