00/01/1900 In the date cell when linked cell is empty?

Hi I have 52 worksheets all linked up to a total sheet that is working very 
well thanks to RD

All I need to do now is sort out the date this is copied from sheet1 onto 
the totals page and works fine when the date is put into sheet1.

However if the cell is blank on sheet2 the date is put into the totals sheet 
as 00/01/1900 when the date is put in sheet2 all is well with the right date 
entered into the totals sheet.

So how do I get the totals date to stay blank until the weekly sheets have a 
date put into the sheet please.

the format I am using is below


many thanks for all the help I have had with this project!

Regards
Derek





One approach is to set-up your "Start" sheet with the names of all your
weekly sheets in a column.
Across the rows enter labels for the 20 categories that your interested in
reviewing.

For example, starting in A2 and going down,
Sheet1
Sheet2
Sheet3
.....
Sheet52

Now, say the first cell of interest on *all* the sheets would be C20.
Enter this in B2:

=INDIRECT(A2&"!C20")

Drag down to copy, and you now have all the data from C20 in all 52 sheets.

Whether or not you can drag this formula *across* the columns depends on
exactly which cells on the other 52 sheets you are looking to be returned.

Needless to say, the spelling of the sheets in column A must match exactly
the actual sheet names in the WB.
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


0
5/4/2004 8:40:44 PM
excel 39879 articles. 2 followers. Follow

2 Replies
893 Views

Similar Articles

[PageSpeed] 18

Hi
try
=IF(INDIRECT(A2&"!C20")<>"",INDIRECT(A2&"!C20"),"")



--
Regards
Frank Kabel
Frankfurt, Germany


Derek Peters wrote:
> Hi I have 52 worksheets all linked up to a total sheet that is
> working very well thanks to RD
>
> All I need to do now is sort out the date this is copied from sheet1
> onto the totals page and works fine when the date is put into sheet1.
>
> However if the cell is blank on sheet2 the date is put into the
> totals sheet as 00/01/1900 when the date is put in sheet2 all is well
> with the right date entered into the totals sheet.
>
> So how do I get the totals date to stay blank until the weekly sheets
> have a date put into the sheet please.
>
> the format I am using is below
>
>
> many thanks for all the help I have had with this project!
>
> Regards
> Derek
>
>
>
>
>
> One approach is to set-up your "Start" sheet with the names of all
> your weekly sheets in a column.
> Across the rows enter labels for the 20 categories that your
> interested in reviewing.
>
> For example, starting in A2 and going down,
> Sheet1
> Sheet2
> Sheet3
> ....
> Sheet52
>
> Now, say the first cell of interest on *all* the sheets would be C20.
> Enter this in B2:
>
> =INDIRECT(A2&"!C20")
>
> Drag down to copy, and you now have all the data from C20 in all 52
> sheets.
>
> Whether or not you can drag this formula *across* the columns depends
> on exactly which cells on the other 52 sheets you are looking to be
> returned.
>
> Needless to say, the spelling of the sheets in column A must match
> exactly the actual sheet names in the WB.

0
frank.kabel (11126)
5/4/2004 8:43:52 PM
On Tue, 4 May 2004 21:40:44 +0100, "Derek Peters" <derek.peters@btinternet.com>
wrote:

>So how do I get the totals date to stay blank until the weekly sheets have a 
>date put into the sheet please.

You could use a custom format in that cell such that 0's would be blank:

Format/Number/Custom Type:  dd-mm-yyyy;;


--ron
0
ronrosenfeld (3122)
5/4/2004 9:02:34 PM
Reply:

Similar Artilces:

How can I add up time, say 18:00 + 18:00 hrs in excel 2002
No probs subtracting times, but try to add them does not seem to work for me. Subtracting times: e.g. (cells are formatted in Time, i.e. 4:00:00 AM 4:00:00 AM 12:30:00 PM =TEXT(D5-C5,"h:mm") this formula works Adding times: can anyone help please Firstly, when subtracting, D5-C5 works fine, just format the cell as time, you don't need TEXT. When you add, if it goes above 24 hours it errors. Just change the format to [hh]:mm -- HTH RP (remove nothere from the email address if mailing direct) "Malcolm Judge" <Malcolm Judge@discussions.microsoft.com>...

VBA code to delete cell contents
How does everybody write code to delete the contents of a cell, such as cell "A1"??? I normally use the following code: Range("A1").Value = "" Is that correct?? I get the feeling that I am setting cell "A1" to an empty string, rather than actually deleting the current contents. Hi Robert Your method works. Here is another method which gets the job done as well. Range("A1").ClearContents Take care Marcus Hi When I delete the contents of a single cell, I usually use .Value="" as you do, but when ...

BCM crashes when linking an email
When I try to link emails received through Outlook to a business contact, Outlook generates an error and then exits. This isn't killing me but it would nice if it worked correctly. ...

Access form date query
Hello all, I have a form in which I have 2 combo boxes. 1 called StartMonth and another called EndMonth. I have a query called MonthsAvailable, this gets its info from Date Out By Month: Format$(JobsInv.DateOut,'yyyy mm') The combo boxes fill ok with data like 2008 06 and so on no problems. My problem is that I want to capture all data from StartMonth and EndMonth, eg 1/6/2006 through to 31/8/2007. I have this criteria set up in a report: >=[Forms]![DataDrill]![StartMonth] And <=[Forms]![DataDrill]![EndMonth] for some reason the data retrieved only read from the 1st of e...

vlookup help 01-08-10
I'm using the following formula in order to look up a name in another spreadsheet that corresponds to what's in B76, and its working fine. =VLOOKUP(B76,'[2010 GR.xls]Hiring'!$E$4:$F$400,2,FALSE) However, I need a new formula to do something similar, but its not working. I need to look up the whats in B76 then go to the 2010 GR.xls file in the Hiring tab and match it with whats in column E (like above), then return the position title that's in column C. Any help would be greatly appreciated! =INDEX('[2010 GR.xls]Hiring'!$C$4:$C$7,MATCH(B76,...

cannot open web links
When I get e-mails with web links in them I cannot click on them and open them from outlook express anymore. Can anyone help? frustrated <mayfairyouth@sbcglobal.net> wrote: > When I get e-mails with web links in them I cannot click > on them and open them from outlook express anymore. Can > anyone help? 1) Ask in an Outlook Express newsgroup. 2) In IE, click Tools>Internet Options>Programs>Reset Web Settings -- Brian Tillman ...

CRM email error 01-29-04
When I send email from Sales for Outlook or web interface it'd give me error: "An error has occurred. For more information, contact your system administrator." If I click "Go back" then send again it'd appear to send fine with no error. But, when I look in Activities I see 2 emails sent, one with CRM GUID and another without. Please help. Hi, Sorry to jump on your thread! Im having the same issue, im trying to send email from the web based version from one crm user to another as a test. All i get is the message an error has occurred. I click back and try ...

calculating hours into date format
hi I would like a formula that will allow a user to input a figure (hours and minutes) into a cell that will calculate the end date and time based on a start date and time. For example, Enter 30 hours, static start time is 01/01/04 00:00hrs, the formula would then calculate that the end figure is 02/01/04 06:00hrs. Essentially it is calculating the number of hours & mins added onto the start date with the end result a date and time later than the hours & mins entered Hope this makes sense! Hi in A1 put your date in B1 enter your hours in the format hh:mm C1: =A1+B1 and forma...

View pane
When I'm viewing an email in the view pane there is no received date shown. Can I add this field within the view pane. The inbox does show a date but this column moves and the mail being previewed may be "off the screen�" and its a pain to keep scrolling the inbox column just to find the reived date regards, Beemer what version? If you have the header showing, you should see the date (along with subject, from, to)- or hover the mouse over the item in the email list - the date (and other info) displays in the popup. -- Diane Poremsky [MVP - Outlook] Author, Teach Yo...

Help with a query 01-23-08
Hi All, I am sure there is a simple solution to this but my brain doesn't seem to be working at the moment. In my dataset one field has the value "Data Quaternity" and the other values are NULL (but could be another string) and I want to exclude any records from the dataset which have this value. I have tried to put the following expressions in the Criteria for the query. NOT "Data Quaternity" or NOT Like "Data Quaternity" But when I do this all I get is an empty data set. I can sort on IS NULL in the criteria now, but I don't want to do this as ...

due date function
I try to make function for due payment. When I made copy file and then drag down the formula and it's succed. But for the cell that I not fill yet has due date also. How to make that cell became 0 (zero) if not fill the cell yet. example : A1 A2 19/8/05 =A1+30 - 30/01/00 - 30/01/00 Thanks Lando =IF(C5=""; "" ;C5+30) Explanation detail if ([logical test] c5 equals ""; then [result if true] ""; else [result if false] c5+30) Explanation brief if c5 eq...

Windows Live Messenger has stopped working 01-07-10
Looking for some guidance. When I select Windows Live Messenger I receive the following: Windows is checking for a solution to the problem I have tried changing the compatibility settings and re-installing several times. windows 7 home premium (64-bit OS) 6GB RAM ...

Format a date #2
How can I format a date 01/01/05 to show up as Qrt1, 04/01/05 to show up as Qrt2 and so on? Any help appreciated. Thanks Perhaps an alternative to try: Assuming dates are in col A, A1 down Put in B1: =IF(A1="","",VLOOKUP(MONTH(A1),{1,"Qtr1";4,"Qtr2";7,"Qtr3";10,"Qtr4"},2)&", "&TEXT(A1,"mm/dd/yy")) Copy B1 down Col B will display what you want -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "sacredarms" <sacredarms@discussions.mic...

how to return a serial number to a date
Example: 40899 Is there a formula to convert the serial number 40899 above back to 12/22/2011? Thanks, Holly Format the cell as Date -- Biff Microsoft Excel MVP "Holly" <Holly@discussions.microsoft.com> wrote in message news:82F628DD-B506-4A72-B5AC-DF85265ADAB0@microsoft.com... > Example: 40899 > > Is there a formula to convert the serial number 40899 above back to > 12/22/2011? > > Thanks, Holly "Holly" wrote: > Is there a formula to convert the serial number 40899 > above back to 12/22/2011? It would h...

help with formula 01-06-10
variance analysis cell A1 is 10 input cell B1 is 5 input cell c1 is 5 formula A1 - B1 I only want to look at variances that are greater than or less than 25% of cell A1 I can create the formula and it works but if the variance is negative because A1 is less than B1 then I can't get it to show a variance..only 0 Any help would be appreciated... Not sure what is showing 0? what is the specific formula you are using? "Mur" wrote: > variance analysis > > cell A1 is 10 input > cell B1 is 5 input > cell c1 is 5 formula A1 - B1 &...

Is it possible to add dates?
Ok will try and explain with the example below..... in column A as header `Dates` in row A2 `all` (meaning all dates for that month) in row A3 15-29 (meaning from 15th to 29th of that month) in row A4 1-10 (meaning from 1st to 10th of that month) And so on various dates for different people , its about 50 rows long, with a column for each month of the year. My question...... row A2 would = 30 (31) days row A3 would =14 days row A4 would =9 days could I get a formula to give me the total days at the bottom of column A? ie 53 days Any help would be appreciated thanks Why is 1...

formating text cells
I need to format a text cell using bullets similiar to Word's bullets and numbering? Any idea on how to do that? Can Excell automatically generate the bullet? Excel doesn't support this the way Word does. I'd use Word, if at all possible. One alternative in Excel: If you put the text of the bullet point in B1, put this in A1 to generate a "bullet": =IF(B1<>"",CHAR(149),"") Copy this down as far as needed. You will likely want to use Align Right on Column A as well. Not a bad idea to adjust the width of Column A, either. tj "Cathyin...

date format
without having to call in my VAR for this (hopefully) simple change ... how do you change the date format from MM/DD/YYYY to DD/MM/YYYY in version 8 The VAR set up cheques one way, and the Great Plains date is the other. Now we've printed cheques supposedly March 1st, and the cheque date reads 3 Jan 2008. JD, I believe this is controlled by the Short date format in your Windows regional settings, not GP. So it's going to be computer specific. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "JD" <JD@discussions.microsoft.com> wrote in message new...

Converting numerical date to text
Does anyone know how to convert to text and keep the integrity of the numerical date? (example: 4/06/97 to 04061997) I can make it look okay, however; the cell is still reflecting the previous value. Not sure what you mean by "the cell is still reflecting the previous value". 1) Formatting doesn't affect the underlying value stored in the cell. Since XL stores dates as integer offsets from a base date, 4/06/97 is the displayed value for the number 35526 (windows default 1900 date system). You can change the formatting to Format/Cells/Number/Custom mmddyyyy but t...

Date Formats #3
Hi, One of our diagnostic devices downloads the data in the format of: 26/08/2003 13:26:15,13.69 26/08/2003 14:26:15,13.46 26/08/2003 15:26:15,13.46 where the first column is the date, the second time and the third the differential pressure reading. I import these into Excel, eliminate the comma column and end up with pretty much what I want, except that I cannot get Excel to "understand" the European (UK) date/time format of 26/08/2003 13:26:15 or especially the date format of 26/08/2003. I tried using the Custom Cell Format of dd/mm/yyyy but when I add the date and time fiel...

Re: 02-01-08
"jose miranda" <mirandajose@bellsouth.net> wrote in message news:... > ...

Help with populating cells using formulas or macros
Hi, I have a problem that I've spent hours working on but even Microsoft customer support has not been any help. I'm trying to work with a large data file where the data is grouped into two types. Let's call them A & B. For each day of data, I have two data points, A & B. These are entered next to each other in the same row. It looks something like this: A1 B1 A2 B2 A3 B3 ... That is, for Day 1, I have data point A and data point B. I want to be able to easily group all the "A" data and all the "B" data separately. That is, in a separate part of ...

Stopping Macro After Certain Cell
I have created a form that staff members have to fill in, the data o this form is submitted to a database. So basically when ever someon fills it in he/she clicks submit and a new row of data is created i the next sheet. How can i disable the macro that does this say after 10 new rows hav been filled in. So when all ten members have filled in there data n more can. The macro will not work. Is there a way to do something like this!! Please help than -- Message posted from http://www.ExcelForum.com Maybe instead of disabling your macro, you could just check to see if the limit had been rea...

Count cells based upon criteria in other cells
The cells in column J contains donations made by various individuals. Cells in column R contain certain member classifications. Am looking for a formula that looks at column R, then counts the number of entries in the corresponding cells in colum J for each member classsification. EX: in column R there are three classifications: RB1, RB2, RB3. Need to count how many donations in column J are made by each classification. Many thanks! Try the below formula =COUNTIF(J:J,"RB1") -- Jacob "JT" wrote: > The cells in column J contains donati...

How do i calculate the time between 10:00 pm and 6:00 am in excel?
I working on a 2 week schedule for my employees. What i need to know is, how do I calculate the time between 10:00 pm and 6:00 am in excel? XL stores times as fractional days (so 03:00 = 0.125). Since times roll over at midnight, you have to add 1 (a full day) to the later time if your values times spand midnight. You can use the fact that XL coverts boolean TRUE/FALSE values into 1/0, respectively, in math functions: A1: start time B1: end time C1: =B1 - A1 + (B1<A1) or the more obscure but equivalent C1: =MOD(B1-A1, 1) See http://cpearson.com/excel/datetime.h...