HolidayBuyer's Guide

Office & Productivity Software forum

General discussion

EXCEL - data capture

by gczerkawski / March 24, 2004 3:27 AM PST

I am trying to capture data in Excel, here is what I am trying to do:
When the value of A9=1 and the time of day is between 8am and 3:30pm, the value in A1 is stored in worksheet MONDAY at B1.
When the value of A9=1 and the time of day is between 4:00pm and 11:59pm, the value in A1 is stored in worksheet MONDAY at C1.
When the value of A9=1 and the time of day is between 12am and 7am, the value in A1 is stored in worksheet MONDAY at D1.
This is done each day Monday through Friday so I have data for the whole week. Ideally I would like to create a workbook for each week so I can go back to previous production data.
I have tried using conditional IF statements, however, when the logic is false, the data returns to zero.
Thanks,
George

Discussion is locked
You are posting a reply to: EXCEL - data capture
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: EXCEL - data capture
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re:EXCEL - data capture
by Kees Bakker / March 24, 2004 5:41 AM PST
In reply to: EXCEL - data capture

1. I see holes in your time frame (form 7am to 8am, from 3:30 pm to 4 pm, and from 11:59pm to 12am.
2. Apparently, your IF statement is not doing what you want it to do. Better write one that works. Most programmers write statements that don't fully work the first time, so there's nothing to be ashamed of.

It might help if you give an example of such an incorrectly working formula, and it's results with certain data, and what you should like to see.

Kees

Collapse -
Re:EXCEL - data capture
by Kees Bakker / March 24, 2004 9:01 PM PST
In reply to: EXCEL - data capture

Think I got your idea now (after a night's sleep). However, this is simply not how the product works.

As soon as you fill in something in A1, all cells directly or indirectly dependent on it are recalculated. If the condition is true, it gets the if-value, otherwise the else value. At any time at most 1 of the conditions is true (because they contain the day and time), so it gets the true value, and all other conditions are false, so they get the false value (FALSE if you don't give another one). Nothing to be done about that in standard Excel. It's one of the products powers, in fact.

What you need is a function, activated by a change in A1. You can for example put =myfunction(A1) in B1. The function (to be written in Visual Basic for Excel) can do everything you can do in Excel yourself (and quite a lot of things outside Excel). How about a msgbox to congratulate you the first time you changed A1 on your
birtday? In this specific case, it can find out where to put what value, and leave all other cells alone and unchanged. Moreover, it could create new worksheets for each week automatically (or a new workbook if you prefer, but I would advise worksheets).

Depending on your exact wishes it would take an experienced VBA-programmer say half a day ($40 an hour makes $160 in total). You might find a student to do it for $100.
If you're new to the subject, you've got to spend say $40 for a good book on VBA-programming in Excel and quite a few long evenings to program it.
Or do it on paper in a simple notebook!

Hope this helps.

Kees

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

The Samsung RF23M8090SG

One of the best French door fridges we've tested

A good-looking fridge with useful features like an auto-filling water pitcher and a temperature-adjustable "FlexZone" drawer. It was a near-flawless performer in our cooling tests.