Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
How do I choose the last five sets of data in a column. I want to make a
chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Assuming source data (ie numbers) are running in A1 down (data is assumed
continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Assuming data is columns A & B:
Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Assuming your data start in A1 down
=OFFSET($A$1,COUNT(A:A)+ROWS($1:1)-6,0) copy down "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Consider putting your most recent data at the top of the column (pushing
older data down) instead of the bottom. That way you will always know where the last five sets are. -- Gary''s Student - gsnu200727 "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Ok, that worked for that problem but I have another issue. I also want to
choose the last five columns to graph. I have data, 30 cells worth, that I want to graph in columns but I only want the last five of them also. For instance, I have graphed data right now in columns K through O with 30 cells of data in each column. If I put data in column 'P' is there a way to drop off column K and add column 'P' automatically? "Toppers" wrote: Assuming data is columns A & B: Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Assuming headings/data in row 1 and NO blanks, then use this for named range:
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,COUNTA(Sheet1!1:1)-5,5,5) HTH "Erik" wrote: Ok, that worked for that problem but I have another issue. I also want to choose the last five columns to graph. I have data, 30 cells worth, that I want to graph in columns but I only want the last five of them also. For instance, I have graphed data right now in columns K through O with 30 cells of data in each column. If I put data in column 'P' is there a way to drop off column K and add column 'P' automatically? "Toppers" wrote: Assuming data is columns A & B: Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Some cells have blanks, I can't get this to work for anything.
"Toppers" wrote: Assuming headings/data in row 1 and NO blanks, then use this for named range: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,COUNTA(Sheet1!1:1)-5,5,5) HTH "Erik" wrote: Ok, that worked for that problem but I have another issue. I also want to choose the last five columns to graph. I have data, 30 cells worth, that I want to graph in columns but I only want the last five of them also. For instance, I have graphed data right now in columns K through O with 30 cells of data in each column. If I put data in column 'P' is there a way to drop off column K and add column 'P' automatically? "Toppers" wrote: Assuming data is columns A & B: Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
Just to clarify: I am assuming P1 is blank UNTIL you add your new data. If P1
has a heading BUT no data, change 1:1 to 2:2 BUT again it assumes NO blanks from A2 to O2. "Toppers" wrote: Assuming headings/data in row 1 and NO blanks, then use this for named range: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,COUNTA(Sheet1!1:1)-5,5,5) HTH "Erik" wrote: Ok, that worked for that problem but I have another issue. I also want to choose the last five columns to graph. I have data, 30 cells worth, that I want to graph in columns but I only want the last five of them also. For instance, I have graphed data right now in columns K through O with 30 cells of data in each column. If I put data in column 'P' is there a way to drop off column K and add column 'P' automatically? "Toppers" wrote: Assuming data is columns A & B: Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
It WON'T work if there are blank cells in the row we are counting ..
.... so if cannot use the "column count" approach, ..... how can we determine that your last column was (say) column O? "Erik" wrote: Some cells have blanks, I can't get this to work for anything. "Toppers" wrote: Assuming headings/data in row 1 and NO blanks, then use this for named range: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,COUNTA(Sheet1!1:1)-5,5,5) HTH "Erik" wrote: Ok, that worked for that problem but I have another issue. I also want to choose the last five columns to graph. I have data, 30 cells worth, that I want to graph in columns but I only want the last five of them also. For instance, I have graphed data right now in columns K through O with 30 cells of data in each column. If I put data in column 'P' is there a way to drop off column K and add column 'P' automatically? "Toppers" wrote: Assuming data is columns A & B: Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
I can put zeros in these columns if necessary, but it still won't work. How
can I chart the last five columns in my worksheet? I have data in colums D through P but I only want to chart columns L, M, N, O, P. And then when I put data into Q, I only want to chart columns M, N, O, P, Q. Is this possible. I can't get your other formaula to work at all. I define the name with the Insert, Name, Define function and then try to use this name as the data source but it won't work. "Toppers" wrote: It WON'T work if there are blank cells in the row we are counting .. ... so if cannot use the "column count" approach, .... how can we determine that your last column was (say) column O? "Erik" wrote: Some cells have blanks, I can't get this to work for anything. "Toppers" wrote: Assuming headings/data in row 1 and NO blanks, then use this for named range: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,COUNTA(Sheet1!1:1)-5,5,5) HTH "Erik" wrote: Ok, that worked for that problem but I have another issue. I also want to choose the last five columns to graph. I have data, 30 cells worth, that I want to graph in columns but I only want the last five of them also. For instance, I have graphed data right now in columns K through O with 30 cells of data in each column. If I put data in column 'P' is there a way to drop off column K and add column 'P' automatically? "Toppers" wrote: Assuming data is columns A & B: Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing last set of data in column
With "headings" in A1 to P1 and D1 to P30 this works for me:
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$D:$D)-5,COUNTA(Sheet1!1:1)-5,5,5) If data in row 1 starts in column D, try: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$D:$D)-5,COUNTA(Sheet1!$1:$1)-2,5,5) "Erik" wrote: I can put zeros in these columns if necessary, but it still won't work. How can I chart the last five columns in my worksheet? I have data in colums D through P but I only want to chart columns L, M, N, O, P. And then when I put data into Q, I only want to chart columns M, N, O, P, Q. Is this possible. I can't get your other formaula to work at all. I define the name with the Insert, Name, Define function and then try to use this name as the data source but it won't work. "Toppers" wrote: It WON'T work if there are blank cells in the row we are counting .. ... so if cannot use the "column count" approach, .... how can we determine that your last column was (say) column O? "Erik" wrote: Some cells have blanks, I can't get this to work for anything. "Toppers" wrote: Assuming headings/data in row 1 and NO blanks, then use this for named range: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,COUNTA(Sheet1!1:1)-5,5,5) HTH "Erik" wrote: Ok, that worked for that problem but I have another issue. I also want to choose the last five columns to graph. I have data, 30 cells worth, that I want to graph in columns but I only want the last five of them also. For instance, I have graphed data right now in columns K through O with 30 cells of data in each column. If I put data in column 'P' is there a way to drop off column K and add column 'P' automatically? "Toppers" wrote: Assuming data is columns A & B: Create named range e.g MyData Refers to: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-5,0,5,2) Use Mydata as your source "Max" wrote: Assuming source data (ie numbers) are running in A1 down (data is assumed continuous with no blank cells) Select a 5 cell column range, say select B1:B5, then place in the formula bar: =OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-5) Array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) B1:B5 will return the last 5 cells in col A. Plot the graph using B1:B5 as source. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Erik" wrote: How do I choose the last five sets of data in a column. I want to make a chart that continually updates as I add in data but only with the last five sets of data. Is this possible with "offset" and how would I do this? Erik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Report choosing a Pivot table data | Excel Worksheet Functions | |||
Choosing data based on Match to several items | Excel Worksheet Functions | |||
Choosing Data and skipping other | Excel Worksheet Functions | |||
Choosing a value from a row with more than one condition | Excel Discussion (Misc queries) | |||
Randomly choosing | Excel Worksheet Functions |