Solved

Timestamp in Excel 2003

Posted on 2011-02-15
7
633 Views
Last Modified: 2012-05-11
Experts - I need your advice?

I have a timestamp problem.  This formula:
=IF(A1<>"",IF(B1="Date/Time Stamp",NOW(),B1),"Date/Time Stamp")
works for me.  However, the formula must be populated throughtout column B to accomodate scan data entered into column A.  With worksheets added for each day and hundreds of lines used in each worksheet, I reach Excel 2003 size restrictions.

Can I create or move the formula in column B down one row as I go?  Can I do this with a formula instead of a macro?

Gary - Cincinnati
0
Comment
Question by:garyrobbins
  • 3
  • 3
7 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
Comment Utility
Formulas are fixed parts of cells and must be there for them to function.
To dynamically add formulas, you will need VBA.

BTW
If you move up to Word 2007/2010 (as scary as it is) the limit on the number of rows changes form 64K to about 1 million rows, which might help a bit.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry for asking but what r u trying to acheive ? when move the formula to B. I maybe slow but did not understand your problem expect that it is obvious that you are reaching limitss and in this case nothing but the solution of 'More Limit' like rspahitz suggested could help you.

If you state clearly what is the problem then maybe we could help. Finally why not VBA ??
gowflow
0
 

Author Closing Comment

by:garyrobbins
Comment Utility
Turns out the reason my worksheets were growing so large was due to some formatting issue with my Master worksheet.  First I tested how much capacity I was using with the data I was adding -- this was not an issue.  Then I noticed the large size of my empty Master.  By clicking Cntrl-End I detected that I was using the whole worksheet!!  Then, by replicating this Master I quickly reached Excel 2003 limits.  

I learned a lot through this issue.  With your help I was able to save my design project from disaster.

Thanks for the prompt reply.  I feel confident having you guys to back me up.

Gary
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Glad you found the answer.  Excel has a lot of weird things like that, where it doesn't necessarily handle things in an optimal way (like formatting things in entire blocks) and the results can be mysterious (like large file size even if only one cell has a formula in it.)

Just one more tip, maybe not related:  to get to what Excel thinks is the end of the spreadsheet (excluding formatting), use Ctrl+G then click on the Special button, the select Last Cell.

0
 

Author Comment

by:garyrobbins
Comment Utility
rspahitz, where is this "Special" button?
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
After Ctrl+G, bottom left corner of the window that appears you should see the Special button.  From there, you have many additional options including Last Cell.
0
 

Author Comment

by:garyrobbins
Comment Utility
Thanks for the handy tip.  

On my PC, Ctrl+G opens up the VBA tool.  However, I found that Edit>Go to...  (or F5) got me to the GoTo screen you referenced.  Lots of interesting options in that "Go to Special" box.

Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now