Calculating from last inputted cell

I am trying to write a formula to input the data from the last inputted cell 
in a range to another cell. Reading through some earlier threads I found a 
formula that works in 2 stages, the first finds the last inputted cell to 
return the cell reference, and the 2nd stage reuses the result, the formulas 
were 

=MAX(ROW($A$1:$A$200)*($A$1:$A$200<>"")) 

If the above formula was entered into cell B1 the 2nd formula would be 
entered in the cell where I wanted the data displayed and would be

=INDEX($A:$A,B1)

The 2 formulas work  providing the entered data starts at row 1, my problem 
is that the entered data that I want to check is partway down the column, I 
have tried amending the formula just to cover the range that I want to check 
as below  

=MAX(ROW($A$20:$A$29)*($A$20:$A$229<>"")) 

Which returns the correct row value for the last inputted cell but I can't 
get the 2nd formula to use the reference to display the last inputted value. 
The 2nd formula that I have used is 

=INDEX($A$20$29:$A$29,B1) 

This produces a #REF error, I have also tried to use named ranges which  
produces the correct cell reference number but still produces the same error.

I would be greatly obliged for any ideas on how I could get it to work.

Thanks

Petermac
0
Petermac (2)
5/31/2006 9:13:01 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
423 Views

Similar Articles

[PageSpeed] 23

Either adjust the first formula to

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Petermac" <Petermac@discussions.microsoft.com> wrote in message
news:11C60E80-45DE-43C8-8D87-A57FF1E0582C@microsoft.com...
> I am trying to write a formula to input the data from the last inputted
cell
> in a range to another cell. Reading through some earlier threads I found a
> formula that works in 2 stages, the first finds the last inputted cell to
> return the cell reference, and the 2nd stage reuses the result, the
formulas
> were
>
> =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>""))
>
> If the above formula was entered into cell B1 the 2nd formula would be
> entered in the cell where I wanted the data displayed and would be
>
> =INDEX($A:$A,B1)
>
> The 2 formulas work  providing the entered data starts at row 1, my
problem
> is that the entered data that I want to check is partway down the column,
I
> have tried amending the formula just to cover the range that I want to
check
> as below
>
> =MAX(ROW($A$20:$A$29)*($A$20:$A$229<>""))
>
> Which returns the correct row value for the last inputted cell but I can't
> get the 2nd formula to use the reference to display the last inputted
value.
> The 2nd formula that I have used is
>
> =INDEX($A$20$29:$A$29,B1)
>
> This produces a #REF error, I have also tried to use named ranges which
> produces the correct cell reference number but still produces the same
error.
>
> I would be greatly obliged for any ideas on how I could get it to work.
>
> Thanks
>
> Petermac


0
bob.NGs1 (1661)
5/31/2006 11:03:30 AM
Either adjust the first formula to

=MAX(ROW($A$20:$A$29)*($A$20:$A$29<>""))-MIN(ROW($A$20:$A$29))+1


or just leave the second formula as

=INDEX($A:$A,B1)


-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Petermac" <Petermac@discussions.microsoft.com> wrote in message
news:11C60E80-45DE-43C8-8D87-A57FF1E0582C@microsoft.com...
> I am trying to write a formula to input the data from the last inputted
cell
> in a range to another cell. Reading through some earlier threads I found a
> formula that works in 2 stages, the first finds the last inputted cell to
> return the cell reference, and the 2nd stage reuses the result, the
formulas
> were
>
> =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>""))
>
> If the above formula was entered into cell B1 the 2nd formula would be
> entered in the cell where I wanted the data displayed and would be
>
> =INDEX($A:$A,B1)
>
> The 2 formulas work  providing the entered data starts at row 1, my
problem
> is that the entered data that I want to check is partway down the column,
I
> have tried amending the formula just to cover the range that I want to
check
> as below
>
> =MAX(ROW($A$20:$A$29)*($A$20:$A$229<>""))
>
> Which returns the correct row value for the last inputted cell but I can't
> get the 2nd formula to use the reference to display the last inputted
value.
> The 2nd formula that I have used is
>
> =INDEX($A$20$29:$A$29,B1)
>
> This produces a #REF error, I have also tried to use named ranges which
> produces the correct cell reference number but still produces the same
error.
>
> I would be greatly obliged for any ideas on how I could get it to work.
>
> Thanks
>
> Petermac


0
bob.NGs1 (1661)
5/31/2006 11:04:43 AM
Bob

  Thankyou very much for your help, I was trying to make it more complecated 
that it was.

peter 

"Bob Phillips" wrote:

> Either adjust the first formula to
> 
> =MAX(ROW($A$20:$A$29)*($A$20:$A$29<>""))-MIN(ROW($A$20:$A$29))+1
> 
> 
> or just leave the second formula as
> 
> =INDEX($A:$A,B1)
> 
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (replace somewhere in email address with gmail if mailing direct)
> 
> "Petermac" <Petermac@discussions.microsoft.com> wrote in message
> news:11C60E80-45DE-43C8-8D87-A57FF1E0582C@microsoft.com...
> > I am trying to write a formula to input the data from the last inputted
> cell
> > in a range to another cell. Reading through some earlier threads I found a
> > formula that works in 2 stages, the first finds the last inputted cell to
> > return the cell reference, and the 2nd stage reuses the result, the
> formulas
> > were
> >
> > =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>""))
> >
> > If the above formula was entered into cell B1 the 2nd formula would be
> > entered in the cell where I wanted the data displayed and would be
> >
> > =INDEX($A:$A,B1)
> >
> > The 2 formulas work  providing the entered data starts at row 1, my
> problem
> > is that the entered data that I want to check is partway down the column,
> I
> > have tried amending the formula just to cover the range that I want to
> check
> > as below
> >
> > =MAX(ROW($A$20:$A$29)*($A$20:$A$229<>""))
> >
> > Which returns the correct row value for the last inputted cell but I can't
> > get the 2nd formula to use the reference to display the last inputted
> value.
> > The 2nd formula that I have used is
> >
> > =INDEX($A$20$29:$A$29,B1)
> >
> > This produces a #REF error, I have also tried to use named ranges which
> > produces the correct cell reference number but still produces the same
> error.
> >
> > I would be greatly obliged for any ideas on how I could get it to work.
> >
> > Thanks
> >
> > Petermac
> 
> 
> 
0
Petermac (2)
5/31/2006 1:11:02 PM
Reply:

Similar Artilces:

Inseting data in cells
I have a row of cells that have last names in them, is there a way I can insert some data into all of the cells, this data will be the same for all names. Ex Before jones1 After (alias=jones1) then if possible export all of the names to a word or text file all in one line. Ex (alias=jones1)(alias=smith2)(alias=johnson3) Thanks for any help!!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Assuming last names are in A2:Z2 Put in A3 :...

checking input in a cell and return by copying info from a other c
What I want to do is the following. I have a worksheet in which I have created the following; Name-Column , select-Column. I now want to created a formula in a new sheet which check if the are "x" in the select column and copy the names of the name-columns to cells in that new sheet. (which is in the same workbook) is that possible? If so please let me know. Thanks in advance. To find a single "x" you would use VLOOKUP(). For all the "x"'s, see: http://office.microsoft.com/en-us/excel/HA012260381033.aspx -- Gary''s Student - gsnu20...

Splitting Text from single cell in column across multiple Columns
Text To column wont work because I have name and address info in a single cell in this format. Each cell has different data representing different addresses. Name St # St Name Phone Number City, State zip I want a formula or something to take first line and put in one column, the 2nd line in another column and the 3rd line spread across 3 columns. Although if you could just find a way to split each line into a column, that would work. Once again, Text to colun won't work, I have multiple cells with diffeent addresses, they are not all the same and there are no deli...

Averaging List with Blank Cells
Hi, Someone gave me this formula to average the most recent five items in a column of numbers: =AVERAGE(OFFSET(B2,COUNTA(A:A)-5,0,5,1)) It works great unless the list has blank cells. In that case it does not work. 1. 6 2. 3. 7 4. 9 5. 6. 2 7. 8. 1 9. 23 10 4 How can I make it work when the columns have some blank cells? Thanks, Frank Try =IF(count(A1:A10)=0,",Average(A1:A10)) >-----Original Message----- > >Hi, > >Someone gave me this formula to average the most recent five items in >a column of numbers: > >=AVERAGE(OFFSET(B2,COUNTA(A...

Pivot table overwrites neighbouring cells #2
Hello, I have multiple pivot tables on the same worksheet to allow for easy comparison of data. The source data is based on the week and expected to be refreshed daily. As the week progresses the size of the pivot tables will grow and overwrite data below it. I am looking for a workaround to this problem or if there is a way to set the pivot table to insert a new row below it if the size will increase and before data is updated. Thanks in advance, Anissa Hi, There is no feature of pivot tables to do what you wish. You pretty much have two options that I can think of: 1. Prepare e...

How should I calculate elasticity by using excel?
I want to calculate elasticity of expenditure (by households) in education with respect to increase in income. Pls help me. I have huge data set from household survey Yam This is one of those cases in which I kind of go out on a limb for the poster. Since its the 17th and nobody has come to your rescue yet, I think I can safely presume one or more of the following are true. One, this community is as baffled as I am about what Elasticity of Expenditure IS, in which case you probably want to provide us with a mathematical relationship to work from. Two, you may have implied a questio...

MS Excel VBA Pivot table link cell fetch records
MS Excel VBA Pivot table link cell fetch records I have a table 'tbl_Final' in MS Access 2007 where the data is used and cached in a pivot table found in the 'Data' worksheet in MS Excel 2007. In this MS Excel 2007 workbook, I have various worksheets reports which links to the pivot table values found 'Data' worksheet. I linked formulae in one of the cells found the worksheet reports, looks like this: =GETPIVOTDATA("Amount",Data!$B$4,"Month", 2,"Year","2008","Scenario","Budget") Lets sa...

Separating First Name(s) and Last Name into Separate Columns
I have a spreadsheet with one column ("name") that contains names in the following formats occuring randomly down the list: Firstname Lastname Firstname & Firstname Lastname Firstname Lastname & Firstname Lastname I need to get them all into two new columns "firstname" and "lastname" and I'm having difficulties writing a formula to account for all 3 scenarios in one cell. (If I have to do it in multiple steps - so be it!) For the 1st example, the contents of the new "firstname" column should be Firstname, while the "lastname"...

Custum Cell Format
I have an array formula which automatically summarizes all available values of a certain column, in ascending order. The summary is fixed to a maximum of 8 cells and in many cases it uses less than the available 8 columns. Is it possible to format the cells by showing a 0 when the result/cell is empty In other cells in the sheet the cells show empty instead of a zero by using the custom format of 0;-0;"" Now I want an empty cell to show 0 Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ----------------------------------------...

How do I draw a circle around a cell I want to highlight
How do I draw a circle around a cell I want to highlight - I want to see the text within the cell and colour the circle red. I tried inserting a circle from the 'drawing toolbar' but I couldn't see the text in the cell. i have seen Excel worksheets with this feature and would like to emulate it. Thanks, Janev See Tools/Formula checking/Display Formula checking Toolbar/Circle unvalid values Regards, Stefi „Janev” ezt írta: > How do I draw a circle around a cell I want to highlight - I want to > see the text within the cell and colour the circle red. I tried > ...

Calculating peak and off-peak hours while filling out employee's schedules
This is a bit complicated. I will try to explain the best I can. I would simply like to know where to start looking to solve this problem. I need a way of tracking several 'types' of hours including calculating peak and off peak hours when scheduling employee's time sheets. these are the criterias. - off peak hours are between monday (9am) through wed (7pm) - peak hours are wed (7pm onward) - sunday (end of day) - if an employee works over 8 hours, it's only counted as 7.5 hours of pay (subtracting lunch/dinner) i need a way of entering times into excel so that these ho...

Is there any way of calculating a running total within a single cell in a column
Can anyone help with this query? e.g. I want to work out how many people are working at any time in a 24 hour period. The number of people rostered on differs each day of the week. Monday Time No. rostered on 6.00 2 7.00 2 8.00 2 9.00 2 10.00 (and so on) Can I add additional staff number in my 'no. rostered on column' by just entering the number into the cell. Say, if there were an extra 2 staff starting their 8 hour shift at 9. am. is there a way of just typing in '2' into that cell and getting '4'? What I do at the moment ...

Macro to calculate exponential decay
I'm using Excel 2007. I need a custom exponential decay function for a worksheet. The parameters are Y0, H, and X. It will return Y. The formula is Y = 1 + (Y0-1) * EXP((-LN(2)/H)*X) EXP is the standard exponential function (e^x). LN is the natural logarithm. I'd like to call the new function ExpWt(Y0,H,X). I just need it to be available to this worksheet (or workbook), but may want to move it to someplace where it can be used by any workbook. Thanks Pegs. Copy to Module (ALT + F11, Insert Module) and use as normal. e.g. =expwt(B3,b4,b5) Public Function ExpWt(ByRef YO, ByRef...

Referencing only certain cells in a table
Hello, I posted this question earlier in the execl.worksheet.functions group, but did not get an answer. I apologize for the repost. I need some help referencing certain cells from one worksheet in another. Here is an example of what I am trying to accomplish: Worksheet 1: Item # Name Value 1 item1 100 2 item2 0 3 item3 0 4 item4 54 5 item5 0 6 item6 0 7 item7 70 I need to create a separate table in Worksheet2 that as only the NON ZERO values from the table above in Worksheet 1. S...

BOND CALCULATIONS & GRAPHING
I am looking for a template that would graph price vs. time for corporate or municipal bond. This graph would show the amortization o a premium for bonds purchased above PAR and would show the accrual o original issue discount for OID bonds purchased below PAR. An optional call features or mandatory put features would be considered i the calculations. Chuckles12 -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/show...

Cell formatting behaviour question
Hey guys, I have 2 related cases that I dont understand how to get Excel formatting to work: 1. If I imported or copied rows of data (numerical) from another file (Word or Access) into Excel, the data is presented as raw numbers in each cell. Now I apply a formatting (i.e. I want comma separation for thousands, etc.) and it will not show up. However, if i then double-click inside a cell (as if to edit the contents directly), then hit enter the formatting I want shows up... but only that cell. I have hundreds of rows and I really need to force excel to display the formatting i want immediately...

Hide multiple rows when particular cell is zero
Hi All, Can anyone tell me how I can hide multiple rows when a particular cell is zero? It has to go automaticly, so no buttons... When D37=0, hide rows 37-48 When D49=0, hide rows 49-58 When D59=0, hide rows 59-68 When D69=0, hide rows 69-78 When D79=0, hide rows 79-88 etcetera. How can help me with this macro?? Thanks in advanced. Best regards, BL Hi, What should happen if e.g. D38 = 0? Maybe this applied to the desired sheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a, rng As Range Set rng = Range("D37:D1000") For Each a In rng ...

adding number only if CDN is displayed in next cell
I have a list of sales for Canadian and US Customers. I marked in the column besides the sales amount if it is CDN or US. How can I add the CDN or the US Sales only. Thanks for your help. Use SUMIF() See HELP for details -- Kind regards, Niek Otten "ED" <ED@discussions.microsoft.com> wrote in message news:7B61595A-17DD-4254-A46F-A8D26BF0D9AB@microsoft.com... >I have a list of sales for Canadian and US Customers. I marked in the >column > besides the sales amount if it is CDN or US. > > How can I add the CDN or the US Sales only. > > Thanks for y...

Calculations based on 4 possible text strings?
I have this formula that goes into E2 in the first line: =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),"")) which works if the text string PST+GST is found in D2. But I don't know how to branch out from there. There are 4 possible answers to go into D2 which will affect outcome of E2. They are, and I'll list them all including the PST+GST case above: if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13) if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06) if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06) [c...

Conditionally highlight cells or ...
:confused: Hi, Could anyone help me with this problem? Here is th table: Apple Banana Orange Pear 15 45 20 335 85 65 44 77 11 787 88 936 I want to either to be able to 1)use a function to return the name o the fruit containing the lowest level in each raw, so for first row i would return "Apple", sedond row would return "Orange", etc. Can an of you help me writing this function? 2) or if possible, i'd like t highlight the cell in each row that contains the lowest value, so fo first row, cell containing 15 will be highlighted. Can anybody help m with that? Thank...

Calculating a field based on what is in another column
Hi everyone, I have a 3 column spreadsheet. Amount Frequency Weekly$ (Calculated) 1200 F 600 i.e. divides it by 2 if F 100 W 5200 i.e. times it by weeks in year 5200 Y 100 ie divides it by weeks in year As you can see, I want the Weekly$ amt to be calculated based on whether the Frequency is W (Weekly), F (Fortnightly) or Y (Yearly). any ideas as to what to do ? cheers, Adam Assuming your data is in columns A and B, one way: =A1*INDEX({0.5,100,0.01},1,MATCH(B1,{...

Contact Name First to last
whenever I create a contact information the name appearing the fileas box as"last name to first", is there a permanent way to keep the file as is exactly as full name? Dear, Whynot - In OL XP and 2003 Go to Tools -Email Accounts-click on View or change existing directories or address book(next), click on change, Select show names by File As. (Please respond to this question Was this post helpful to you?) Hope this helps you more Regards APK "Alexbj" wrote: > whenever I create a contact information the name appearing the fileas box > as"last name to f...

Opening Form Based on Value Input
I am wanting to open a form within a form based on the value input on a field on the main form. How do I accomplish this? I am a newbie to Access 2007. Thank you!!! Don't have 2007, but in 2003 you could change which form a subform control was displaying with <subform>.SourceObject = "XXXX" "Deb" wrote: > I am wanting to open a form within a form based on the value input on a field > on the main form. How do I accomplish this? I am a newbie to Access 2007. > > Thank you!!! ...

Data and Color calculations
I need to find a way to get total for a particular name. I work in a vineyard and we want to track how many of each vine we have. we have our rows and blocks of vines in a spread sheet. I now have to sort by name then count how many of each vine we have. I also assign the vines a color based on when they will be harvested. Can excel do the above type of calculation based on sum or total of a particular color Thanks so much for any help Ron redwards(at)ap.net Hi Ron, Try something like this modified to suit your ranges. Does not count Conditional Formatting colors. Dave McRitchie has a...

How to separating first and last name (in a single cell...
I have a worksheet with a persons name in a single cell. I need to sort these and I forget how to separate them to another set of cells to get the last name. Since some names have 2, 3, 4 or more separate item (ex: Thomas G. Roberts, CPA) I can figure it out. Any help appreciated? Tommy Boy wrote: > I have a worksheet with a persons name in a single cell. I need to > sort these and I forget how to separate them to another set of cells > to get the last name. Since some names have 2, 3, 4 or more separate > item (ex: Thomas G. Roberts, CPA) I can figure it out. > > An...