Date-orientated update of cells in different spreadsheets

Dear Excel experts!

Hope I am right here...

 I would like to create a customer-orientated & task daily checklist. 
Basically, I want to have a single spreadsheet (spreadsheet1) and worksheet 
(worksheet1) with customer names along the left (1 per row) and tasks along 
the top (1 per column) which I update on a daily basis. Once the task has 
been performed for a customer, a value will be written in the appropriate 
field i.e. for customer1, the task1 result "passed" would be typed into B2, 
however, for customer2, task1 might have "failed" which would be typed into 
C2.

I would then like this value "passed" or "failed" (or other value) input 
automatically written to another customer-oriented speadsheet 
(speadsheet_cust1). This value needs to be written in the correct 
spreadsheet (determined by the name of the customer in spreadsheet1) and 
appropriate date-orientated field depending on the day & month the task was 
actually performed in the original worksheet.

If this can be done, I can quickly fill out a daily checklist, the results 
of which are automatically written to their respective 
customer-spreadsheets, which I could open to see the results of tasks on a 
daily / monthly basis. For this 'destination' customer-orientated 
spreadsheet, my thoughts were a new spreadsheet per customer (named 
spreadsheet_cust1, spreadsheet_cust2...) and separate worksheets per month 
(worksheet_jan, worksheet_feb...), with days of the month along the left (1 
day per row) and task along the top (1 per column).

Finally, I don't mind if the original checklist spreadsheet1 requires input 
for the month (i.e. value 10 for October) and day (value 12 for the 12th) - 
if this is automatic, even better. From these values, perhaps the correct 
customer speadsheet's worksheet cell position could be determined?

Is this possible and do I make any sense at all? I am new to Excel and have 
little function or programming experience - go easy on me! Thanks for your 
comments or tips... 


0
buckie (2)
10/12/2005 1:16:36 PM
excel 39879 articles. 2 followers. Follow

3 Replies
784 Views

Similar Articles

[PageSpeed] 10

Excel is for wimps.

use a database for this project.

0
aaron.kempf (776)
10/12/2005 3:13:35 PM
buckie,

Do yourself a favor, and use a database (within Excel). Set up the database 
on a single sheet with four columns, headed as:

Date
Customer Name
Task
Status

Enter your data points by filling out one row per data point. Then you can 
use autofilter to show the data by day, month, year, customer, task, or 
status, etc... simply by using different filters.

You could use data validation on the Customer Name, task, and status fields, 
and that would make you life a whole lot easier.

If you have any questions, post back.

HTH,
Bernie
MS Excel MVP


"omb" <buckie@eversys.de> wrote in message 
news:uRlUu8yzFHA.3720@TK2MSFTNGP14.phx.gbl...
> Dear Excel experts!
>
> Hope I am right here...
>
> I would like to create a customer-orientated & task daily checklist. 
> Basically, I want to have a single spreadsheet (spreadsheet1) and 
> worksheet (worksheet1) with customer names along the left (1 per row) and 
> tasks along the top (1 per column) which I update on a daily basis. Once 
> the task has been performed for a customer, a value will be written in the 
> appropriate field i.e. for customer1, the task1 result "passed" would be 
> typed into B2, however, for customer2, task1 might have "failed" which 
> would be typed into C2.
>
> I would then like this value "passed" or "failed" (or other value) input 
> automatically written to another customer-oriented speadsheet 
> (speadsheet_cust1). This value needs to be written in the correct 
> spreadsheet (determined by the name of the customer in spreadsheet1) and 
> appropriate date-orientated field depending on the day & month the task 
> was actually performed in the original worksheet.
>
> If this can be done, I can quickly fill out a daily checklist, the results 
> of which are automatically written to their respective 
> customer-spreadsheets, which I could open to see the results of tasks on a 
> daily / monthly basis. For this 'destination' customer-orientated 
> spreadsheet, my thoughts were a new spreadsheet per customer (named 
> spreadsheet_cust1, spreadsheet_cust2...) and separate worksheets per month 
> (worksheet_jan, worksheet_feb...), with days of the month along the left 
> (1 day per row) and task along the top (1 per column).
>
> Finally, I don't mind if the original checklist spreadsheet1 requires 
> input for the month (i.e. value 10 for October) and day (value 12 for the 
> 12th) - if this is automatic, even better. From these values, perhaps the 
> correct customer speadsheet's worksheet cell position could be determined?
>
> Is this possible and do I make any sense at all? I am new to Excel and 
> have little function or programming experience - go easy on me! Thanks for 
> your comments or tips...
> 


0
Bernie
10/12/2005 7:39:54 PM
listen fucknut

excel isn't a database; it shouldn't ever be used for reporting on
database information.

there are better tools for that.

since 95% of VB applications consume information out of a database; I
have decided to help to inform you that Excel is a dead-end street..
Learn ACCESS

little script kiddie shit

0
aaron.kempf (776)
10/13/2005 3:28:13 AM
Reply:

Similar Artilces:

Cannot syncronize accounts in different currencies on my pocket
Hello, I often need to manage accounts in different base currencies but it seems that accounts different from base currency, do no appear on pocket pc 2005. I am using money 2005 on my desktop and recently downloaded pocket money 2005 to my ipaq. This seems to be a serious limitation. any suggestions? Thanks It is a (one of the) limitation of the pocket pc client. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://regi...

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

Show userform at spreadsheet open
How do you make a userform open when you open a spreadsheet? Can anyone help? Thankyou, Roger [excel2003] hi sheet or file? sheet..... Private Sub Worksheet_Activate() Load userform1 userform1.Show 0 End Sub lookup modal and modaless forms in vb help. file.... Private Sub Workbook_Open() Load userform1 userform1.Show 0 End Sub also see this site.... http://www.mvps.org/dmcritchie/excel/getstarted.htm regards FSt1 "Roger on Excel" wrote: > How do you make a userform open when you open a spreadsheet? > > Can anyone help? > &g...

How do Print out a uniform spreadsheet i.e. all the same size A3
...

Hand entered dates view wrong
A user I support is having an issue with a worksheet in Excel. It is not an urgent or crucial fix, but I am interested in people's ideas, since she says it has happened to her before. In each case she threw away the worksheet and started a new one by hand entering the data again. Here's the scenario: She has a worksheet in which she has been adding rows of data over time (6 months or so) and has only about 30 rows of data. Column A are dates, and are formatted as such. After months of opening and saving in this document once a week or so, she now has an issue - when she types...

How do make a line correspond to date
I have a chart with horizontal bars and I want to put a vertical (trend) date line that corresponds to today's date. -- DMM http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:ADC1BD32-DF02-4A7F-B074-DCBBA9D0AAFD@microsoft.com... >I have a chart with horizontal bars and I want to put a vertical (trend) >date > line that corresponds to today's...

Date/Time Modified
Hello, My Access Database consists of many tables. The Query attached to my main Client Information Form is made up of 4 tables. -Client Table -Account Table -Batch No Table -Advisor Table Basically, I want to be able to insert a Date/Time Modified record at the top of my main form that will show a data modified if any record is changed/updated in any of these tables. Is this possible? Thank you, Mellissa -- Message posted via http://www.accessmonster.com ...

Excel 2007 dates and conditional formats
Can anyone please tell me why I can not use a conditional format (<>=) to identify if a date is greater or smaller then another. Everytime I try it, Excel does it right in the first column but misses by 2 days in all following. It also appears to have problems with the years. Example: Date of propposed compleation of a task is 10/10/07; if the date of actual completion is 10/10/07 or sooner, turn GREEN. If the date of completion is 10/11/07 or older, turn RED. Can anyone help?? Regards Mary Hi Mary, First thing to check: What does 10/11/07 mean? November 10 1907? November 10 2007? O...

SQL update.
Hi NG. I have a: Table_1 Query_1 Query_2 Report_1 Form_1 Module_1 with 1 macro. In my Formula_1 I have a ButtonA to activate Module_1 where I Delete all records in Table_1 and import a txt-file to Table_1. With ButtonB I can activate Report_1. Next to this Button I want count from Query_2. Query_2 (Count records in Table_1 (by using Query_1) and is also for Excel): SELECT Count(Ellos_SE_Alm_Q.Postby) AS SE_Alm FROM Ellos_SE_Alm_Q; I have Query_2 as a SUBform in Form_1. My problem is that I must close/open Form_1 for update and that is annoying. I do hope for a...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Date Range #2
Hi, Can someone please assist me with the following issue: I am currently using the formula noted below to direct another application ( Bloomberg ) to select prices from the previous 6th business day. My question is> Is there any way to revise the formula to exclude US holidays for successive years (i.e. for 2005, 2006...) without having to manually change the date entries each year, in the formula? =WORKDAY(Today(),-6, {"5/31/04","7/5/04","9/6/04","11/25/04","12/25/04","1/1/05" }) Thank you I'd recommend listing...

Cell QA for Bob Phillips
Good afternoon Bob: In reference to your QA: (Which sheet was the active sheet when you right-clicked on the tab? It should have been Data Sheet). Yes, I did as you said, but I am not sure I placed the text string in the right place. I pasted it in View Code on the blank area to the right of "Project / Properties" section but for some reason it is not moving the text from "Data Sheet" to the "Activity Sheet". Is it possible to communicate with you directly so I can get this worked out? Thanks John ---------------------------------------------------...

sorting by date
I have a sheet with date amount description 12.12.06 5.00 PLUMBER 04.02.01 50.00 ELECTRICIAN ETC How can I sort into date order. When I try I get all the 2001, 2006, 2005 together but not 2001, 2002, 2003,2004 Cananyone help please Is 12.12.06 text or a date value? In a blank cell, enter ISTEXT(A1) where A1 is a cell reference to this date. True means it is text and 03.01.07 will come before 12.12.06. You can set up a helper cell, enter =DATE(RIGHT(A1,2),LEFT(A1,2), MID(A1,4,2)) where I'm assuming 12.12.06 is month/day/year. The parameters...

Change color on date field in form for re-certification
My main form has two different date fields on it. One date field needs to be re-certified every 6 months, and the other field needs to be re-certified every 12 months. I would like each of those fields to automatically change color depending on how much time as elapsed since that training. For the 6 month field, months 1-5 should be green, 1 month left should be yellow and anything expired should be red. For the 12 month field, months 1-11 should be green, 1 month left should be yellow, and anything expired should be red. Thank you in advance for your help! messingerjc wrote...

GP Update Company
I receive this error in GP utilities after upgrading from GP 8.0 to GP 10.0, SP4 on SQL Server 2005. Interestingly the update goes seamlessly for another company. The error seems to be associated with professional services tools library. The following SQL statement produced an error: create procedure dbo.taFSRMALineLot @I_vReturn_Record_Type smallint, @I_vRETDOCID char(15), @I_vLNSEQNBR numeric(19,5) = 0, @I_vQTYTYPE smallint = 1, @I_vSERLTQTY numeric(19,5) = 1, @I_vITEMNMBR char(31) = null, @I_vLOTNUMBR char(21) = null, @I_vLOCNCODE char(11) = null, @I_vSLTSQNUM inte...

Macro to update information if date changes
using Exel 2000.. I want excel to compare dates on two seperate worksheets and if the date on the first is greater than the second to insert rows and than copy cells on the second to different cells also on the second page. ie..main sheet date is 11-05-08 compared to sheet2 date is 11-04-08...insert cells at a21:d21 and shift cells down (on sheet2) then copy cells b13:h13 to a21:b21 The description seems a bit inconsistent, but here is a shot With Worksheets("Main Sheet") If .Range("A1").Value > Worksheets("Second Sheet").Range("A1&q...

how do I get the if function to return a blank cell, not 0?
I am trying to create a chart from a series that contains data for each month. The series is calculated on other worksheets and copied to the worksheet containing the chart. I would like to have the cells for the months that have not been updated yet (now is January, there are 0' in all cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's. Can this be done? Unfortunately, what you want, and what many of us have requested but doesn't exist, is a worksheet function like BLANK() or NULL(). The best we can do is use NA() in a chart's data source, which is...

Update Charts X-axis
I have a program with 76 charts. They all use the same range to get their x-axis (dates). At the first of every month, I change the dates to show the upcoming month. The table shows the entire year, but we just view it one month (fiscal) at a time. The series is by rows, but could be changed to columns if necessary. I inherited this program. Currently I have to update each one of them manually (click on each and change their XValues). Is there a way I can update all of them at one time? Could vba be used to select and update each chart with me just selecting the range? I am no stranger...

How to slot cell values into pre-defined ranges
I would like to slot cell values starting from 500 upto 75000 in ranges (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15 bins. pls help Not sure what you want here. Do you want to count the number of times values within a range occur? -- Ian -- "KDD" <KDD@discussions.microsoft.com> wrote in message news:766017CE-A55E-42FF-AD8D-9E74E48CD568@microsoft.com... >I would like to slot cell values starting from 500 upto 75000 in ranges > (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have > 15 > bins. > > pls...

different number format
Hi, this is probably an easy question but I am at a loss been an excel newbie. I have XP with my local regional settings, where the number format is 1.000,29 (onethousand and 29/100) on a specific excel sheet I need to revert these settings, in order to paste a large amount of data 1000.29 (onethousand and 29/100) anybody can suggest how to do this? TIA Hi Daniele, I think you shouldn't change your setting, paste the data and then use the code below to fix the numbers: Sub TextToNumberOnActiveColumn() Dim r As Integer Dim c As Range r = ActiveCell.EntireColumn.Range(&qu...

Two date validations
How do I create two validation rules for one cell containing a date? 1. The date (Cell A1) must be between a certain range (12/01/2009 and 12/31/2012). 2. The date must not be greater than Cell A2. So far I got the first rule working, but I can't figure out how to do both. Any advice would be greatly appricaiated. Thanks, ~Gabe I'd try: Select A1 Data|Validation (xl2003 menus) formula is: =AND(ISNUMBER(A1),A1>=DATE(2009,12,1),A1<=DATE(2012,12,31),A1<=A2) Gabe wrote: > How do I create two validation rules for one cell containing a date? ...

How to change default cell formats
When I open a file, all negative values are shown with a - before them, whilst my colleagues, when opening the same file, the negatives are shown in brackets ie. ( ). How do I ensure that when I open the same file, my negative numbers appear in brackets This is a windows setting. Close excel Change that windows regional setting|currency tab|Negative currency format to show ($1.1) Open excel You may have to reformat: Format|Cells|number tab|Number (adjust the decimal places and comma options) Easty04 wrote: > > When I open a file, all negative values are shown with a - before t...

macro to copy into different worksheets
I have a excel worksheet which shows a list like below. Directory of C:\Sarah\files\Dec04 09/02/2005 13:30 6,312 Dec04.sav 1 File(s) 6,312 bytes Directory of C:\Sarah\code\Admissions\0001 20/04/2004 14:45 290,929,896 adm0001.sav 1 File(s) 290,929,896 bytes What I want a macro to do is to look down this worksheet and select each record on the list. I.E find the line starting with the word Directory, selecting all the lines below until it gets to another line starting with Directory, it then pastes this into a different worksheet. then goes back and sele...

graph creation from table using cells with links not possible
Hello, I have problem with MS Excel 2003. It´s impossible to create a graph from the table which is automatically fullfilled by the links to another sheet. I have to fullfill the table manually (numbers not links to them are physically in the cells) and only then I´m able to create the graph correctly. Is there some possibillity, how to create a graph from the table wich has links instead real numbers in the cells? Thanks for Your ideas. Regards, Bobr. ...