Help with unusual table problem

I would like to be able to add additional data to a table of approx. 2
rows and 4 columns.  I would also like the newest data to be on to
while the remaining 19 lines all shift one row down and the leas
current row of data to drop off.  I hope this explains my problem.  

Thanks,

Howd

--
Message posted from http://www.ExcelForum.com

0
1/30/2004 12:53:18 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
430 Views

Similar Articles

[PageSpeed] 4

Not sure if you might have missed my suggestion
to your earlier post yesterday in microsoft.public.excel.
( http://tinyurl.com/239sq )

Based on your "revised" specs in this post:

Let's say your table is in Sheet1 in A1:D20
[assume an initial table size of 20 rows x 4 cols, say]

where new data is then progressively added
via new rows inserted right at the top
with "old" data pushed to the bottom

And what you want is to always grab / "show"
the "latest" / first 20 rows, i.e. what's in A1:D20 of Sheet1

Try this in a new sheet, say Sheet2
------------------------------------------
Put in A1: =OFFSET(INDIRECT("Sheet1!$A$1"),,,20,4)

Copy A1 across to D1, then copy down to D20
(or copy down to A20, then across to D20)

A1:D20 in Sheet2 will always return
what's in A1:D20 in Sheet1
i.e. the "latest" 20 rows of data in Sheet1

So you can use A1:D20 in Sheet2 as the "base" table
for any downstream purposes

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------
"Howdy >" <<Howdy.10tkus@excelforum-nospam.com> wrote in message
news:Howdy.10tkus@excelforum-nospam.com...
> I would like to be able to add additional data to a table of approx. 20
> rows and 4 columns.  I would also like the newest data to be on top
> while the remaining 19 lines all shift one row down and the least
> current row of data to drop off.  I hope this explains my problem.
>
> Thanks,
>
> Howdy
>
>
> ---
> Message posted from http://www.ExcelForum.com/


0
demechanik (4694)
1/30/2004 1:43:54 AM
In Sheet1
-----------
you always have to *insert a row* BEFORE
you enter the most current data right at the top,
otherwise you'll just be overwriting what's in the 1st row.

Click on row1 header (or click on say A1)

Click Insert > Row

This inserts a new row,
and shifts existing data down by 1 row

Now you can enter the latest data in the "new" row1

-------------

The OFFSET table in Sheet2
will always reflect the "latest" 20 rows of Sheet1

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

> To: <demechanik@yahoo.com>
> Date: Fri, 30 Jan 2004 09:15:56 -0600

> This is Howdy who you just gave some tips to on the
> Excel forum.

> I am still having a problem.  The formula you gave
> me works to the point
> that the current data is transferred to sheet 2.

> But when I add additional
> data nothing happens except the most current data
> shows up and the data that
> was most current is gone.  Nothing happens to the
> table except that.  The
> least current data does not drop out.
>
> I enter the data on the most current line of my
> table.  Should I be entering
> this data elsewhere??


0
demechanik (4694)
1/30/2004 11:16:51 PM
Reply:

Similar Artilces:

Subdatasheet for linked SQLServer tables...
I have a SQL Server table that has a self joining relationship. In Access 2002 (2000 format) I was able to link the table and set the 1 to many relationship so that Access properly displayed the subdatasheet for all related records. After upgrading my machine to Access 2003 today, I find that the subdatasheet is no longer displayed (no "+" symbol.) I created a new mdb and linked the SQL table in again and set the 1 to many relationship. Still no go. I did notice that Access monkeyed with the relationship when it saved it though. When creating the relationship I linked Table(PK) ...

Help #4
I have a column with values yes no as mentioned below i want 1 if value is yes 0 if value is no in next column yes no yes yes no no yes yes Hi kiran In B1 use this formula and copy down =IF(A1="yes",1,0) -- Regards Ron de Bruin http://www.rondebruin.nl "kiran" <kiran@discussions.microsoft.com> wrote in message news:F3785964-28CB-4D7B-843B-6F684C00370B@microsoft.com... >I have a column with values yes no as mentioned below i want 1 if value is > yes 0 if value is no in next column > yes > no > yes > yes > no > no > yes > yes > ...

Please help!
Hello to all! I recently installed CRM 3.0 professional edition on Windows 2003 server standard edition. Now my manager wants to have sample database Adventure Works on the same server. How can I install Adventure works on already installed CRM 3.0 ? Or I have to reinstall whole CRM ? If yes, how can I specify during the installation that I need sample database to be installed ? This is really urgent question, and any help will be highly appreciated! thank you!!! Ozz, The AdventureWorks sample db isn't so much "installed" as it's run. It's an ..exe which you run...

Need Help Enabling Flags in OWA for Public Folder
Hello-- I set up a public folders (not default public folders) with permissions for access to only a limited group via OWA. Everything is working great except no one, including the Domain Administrator can "flag" a post. The Flag Column header is grayed out and when you click on it, it merely depresses. Any advice on how to enable the flags would be appreciated. Many thanks, sd On Wed, 4 Oct 2006 09:45:02 -0700, razor <razor@discussions.microsoft.com> wrote: >Hello-- > >I set up a public folders (not default public folders) with permissions for >access to ...

StretchDIBits shrink image problem
hi all, When I use StretchDIBits to shrink image from 352*288 to 176*144, it shows the image with red lines, dots. Is there any way that I can do to show a good image after shrinking? >From myphone, it uses StretchBlts and it shows good picture. But when I use StretchBlts, it doens't show any picture at all. Please advise. regards, peggy On 9 Apr 2007 00:28:18 -0700, "peggy83" <teopeggy@gmail.com> wrote: >Is there any way that I can do to show a good image after shrinking? >>From myphone, it uses StretchBlts and it shows good picture. But when >I use Str...

Outlook 2003 HTML formatting problem
After upgrading clients to Outlook 2003 we've started to experience a strange problem with emails formatted in HTML. The scenario goes like this. 1. A local user creates an HTML formatted email and sends it to an external recipient. 2. The external recipient replies to the original message. 3. Originator replies to the reply 4. The external recipient receives the HTML formatted email with portions of the text grossly enlarged. 5. Upon review, it would appear that the external HTML client dropped a (.) changing the font size from 10.0 to 100 causing the enlarged text. Editing the HT...

Outlook Calendar help
Hello Outlook Folks, I use Outlook to keep reservations at a small motel. It's been working well for me for several years. I recently had a minor drive crash. The company the "repaired" it turned it into a major disaster. I have 2 drives, they formatted both drives for me. Now I have a few days work ahead of me to get back to where I was. Now, my question: I have pretty good backups on CD. Where does Outlook keep the calendar file? If I can pick & place this file I can get my reservations back and keep alot of people happy. Is this even possible? Any help is appreciate...

Smart-shape to dumb-shape? Help with cross functional flow shape?
Greetings! For my purposes, I'd like to use the cross functional shape to build up swimlanes that don't change no mater how or what kind of shape is dropped on them. Using a background page is somewhat inconvenient for my pupose. Can you tell me how to kill the code or events that cause the cross-functional shape to interact with other shapes while leaving its other behaviour intact? (in particular, I don't want other shapes to become attached to a lane nor do I want the lane to change its width) Thanks in advance! The quick solution is to go to Tools > Options > S...

Need help saving a file!
Well let me start off by saying that I feel lost using the Office 97 programs! I had to make a new resume so I got myself a template off of the site here. It opened up in publisher so that's where I created my resume. I've put a lot of time into it and now realize that I think I made a huge mistake! How the heck do I save it as a more "acceptable" file without screwing up the format??? (for uploading as an attachment) Or is that just not possible? I've tried saving it as a word document, but like I said, screws up the whole format. I'm really lost, don...

I need complete idiot proof excel setup database help. please
I need to learn all about Excel and how to make a database.. I have read and read and done practise tutorials but I just cannot grasp the concept, it completely goes over my head everytime. I am quick to learn when I am being shown or told exactly what to do, can anyone help me learn all about Excel.. ? The first thing you need to know is that Excel is not a database application, and while it is commonly used for databases, it doesn't do them well. Expecting a database developed in XL to be "complete idiot proof" is setting yourself up for disaster. Without knowing any...

problem with fonts using Word as editor #3
All new messages come up with a superscript font. I remove the check mark and go into default, but new msgs keep coming up in superscrip.What is one to do? Leo Leo Kerner <l.kerner@sympatico.ca> wrote ... > All new messages come up with a superscript font. I remove the check > mark and go into default, but new msgs keep coming up in superscrip.What > is one to do? Since you keep asking, I'll reply with my first reaction to all word-as-email-editor problems: don't use Word as your email editor. It's a simple and 100% effective solution to this and a whole host of ot...

Mouse select problem
I keep running into this problem from time to time. My mouse will act as if the shift key is pressed and selects everything between the current cursor position and the click. This manifests in VBA, in fields on the Property window and in query designer. If I restart Access the problem goes away. Sticky keys are off. Access 2003, XP Pro If I remember correctly this also happened in previous versions of Access This happens when you keep the shift key pressed for too long at a time. There is a solution to undo it that I forgot. You could search for it. My solution -- get out of the ...

Convert list of pairs [a(i),b(i)] to table that shows # of occurrences of each pair
Given something like this: apple,jan apple,feb apple,mar apple,jan apple,feb orange,jan orange,feb orange,jan orange,feb banana,jan banana,feb banana,mar banana,jan banana,feb I'd like to create a table that has three rows (the fruits) and three columns (the months). At the intersection of each row and column I'd like the number of occurrences of that particular fruit-month pair. Thanks for your time. Tom Z. Tom, Assume your data is in the range A1:A14, and you have a grid starting in cell C1 that looks like: C D E F 1 jan feb mar 2 apple 3 orange 4 banana ...

XMLSerializer problem, class inside class
Hi, I'm having a problem with XMLSerializer, I have classes like the following: [Serializable] public abstract class EulerObject { private Rectangle rect; private string guid = ""; public virtual Rectangle Rect { get { return rect; } set{ rect = value; } } public string GUID { get { return guid; } set { guid = value; } } public abstract void Draw(Graphics g); public abstract EulerObject MakeCopy(bool sameGuid); } [Serializable]...

Installation Problem for Outlook Sales for CRM 1.2
I installed the Outlook Sales for CRM 1.2, however I cannot see the details of accounts, orders and etc. When I debug, I found the problem is that there is only one file(dmUpdate.xml) in folder of "\Program Files\Microsoft CRM\Client\res\Web\_xslfiles". All the files such as 1_Update.xsl and so on were not copied. When I copied them from CRM Server, everything works fine. Is it a bug in the Outlook sales installation package ? Thanks Drew ...

Microsoft Access Tables and Forms
I have made up a table and then created a form from it. The table does not reflect the changes made in the form. I have done everything that I can think of in order to make this work (input info, go to next record, close form), but table still will not update. I want to create reports, but I have no totals showing up, because they will not show in the table. Desperately in need of HELP!!! Helen Helen In a standard Access design, you create a table, (optionally, you create a query based on the table), you create a form bound to the table (optionally, to the query). When you open the for...

Need immediate help with Access to Outlook Please
This is my 3rd request.... Please, thank you.... I went through a windows update and was asked to reboot which I did. I had Outlook open and closed it before rebooting - it was opened to contacts. when I tried to access Outlook I got an error message saying "your personal address book could not be opened. the file is either not accessible or not a personal address book. choose retry to access your personal address book, or choose cancel to not open your personal address book with this application." I have no other application to open this address book with! my contac...

Having some problems saving excel files
I had a Excel file that was worked on using a Office XP machine, and know it is back on my Mac. Know everytime I save I keep on getting this message everytime using Office 98 for Mac. "This file was created using a later version of Microsoft Excel. If you save this file using Microsoft excel 98, information created with features in the later version may be lost." I have tried the following. 1) Saving as a new file and renaming it 2) Saving as Excel 5.x, and then back to Excel 98 3) deleting the Cell comments that I created in Excel XP, and redoing them in Excel 98 Thank...

Help with OpenArg
This has been driving me nuts for about a week now so I thought someone more experienced might have the answer as although it's complex for me, the newbie, it really is realtively simple. Q: I am using Access 2000. I have a client database. I am making a search form. The form is in dataview. It gets it's info. from a query that sorts the info from the client table. The info consists of the contactfirstname, contactlastname, ClientID, nextactiondate, and case status. When the user double clicks on a particular client's info. I would like for the "Clients" form to o...

Pie Chart Help #3
I am trying to create a pie chart and having trouble with selecting slices within the chart and grouping them together. I I want it to look like a scattered chart but want to select which pieces will group together. Please help. Thanks. ...

Problem with Executable File generated using Debug Build
Hi All, I am facing a really wierd problem. I am working on a project in which I have implemeted a GUI for controlling laser microscopes. Everything was going fine till I added support for Filter Wheels. The program compiles perfectly fine. Runs perfectly fine if I run it using Microsoft Visual Studios. But When I try to run the program by double clicking on its icon in the debug folder, the program shows that main frame and then hangs. The problem was not there before I added the code for filter wheels. I am placed all the DLL files in the debug folder. Could any body help me out. Regard...

software update problem in $2007 H&B
I've installed Money 2007 H&B and my location is China.Then every time I launched the program,it says something like "To operate correctly,click 'ok' to let Money download the software update." But if I click 'OK',after it download autoupdate.dll(.cab) and autoupdate.xml,it alarms "A software update is already in progress.Please try again when the update is complete." How can I solve the problem ? By the way , can i download the software update manually? I'm having the same problem, and i cannot find the update on microsoft's website...

auto refreshing data in pivot table
hello friends, i am using ms access 2003, vb6. i want a pivot table get refreshed automatically when i open the form. but it is showing me the previous results. so i explicitly needed to refresh manually. how can we refresh data in a pivot table programatically in vb. thanq ...

Update query help 02-06-08
I have Two tables with the same information. Table Two has a column called Errors with data. How can I update table one Error column with the same info in table two errror column. table 1 table 2 nameid name date items errors nameid name date items errors 100 Floyd 2/5/08 2500 100 Floyd 2/5/08 2500 4 200 Anna 2/5/08 6000 200 Anna 2/5/08 6000 7 Use an update query. Perhaps that would look like the following. STEP 1: BA...

need help #3
hi there,i need help in a certain matter regarding the Exchange Server 2003,i need to know,how can i make the Exchange server check mails from my ISP, as the email accounts i have reside at the ISP's server. Thank u in advance You need a POP3 Connector such as www.exchangepop3.com or www.popbeamer.com -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Armen" <armen@link.net> wrote in message news:905101c3ea8b$53011740$a301280a@phx.gbl... > hi there,i need help in a certain matter regarding the > Exchange Server 2003,i need to ...