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
1052 Views

Similar Articles

[PageSpeed] 11

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:

Retaining date format from CSV
I am generating an excel file from another application. One of the columns is a date. However when I open up the spreadsheet the data is displayed as 04:43:0 when I double click it the date is displayed as 16/10/2004 14:04:43 How can I force the column to be read as a date column? I know that I can select the column and format it as date/time but this is a bit of a pain! Can anyone help? CSV, by definition, contains no inherent formatting information, so how XL displays the value depends on your default date/time format. To make things less of a pain, you could, among other thi...

Would slow links cause write latency on DB?
We have 3 RAID 10 volumes, with various DB's. We have one database that is just for remote users. They have slow links and I was wonder if these would cause phyical disk write and read delays for that DB only, as it seems this DB is higher than the others. Thanks It would seem unlikely but exactly what counters are you measuring? Exchange commits transactions to memory and logs files first. Nue "SW" <SW@discussions.microsoft.com> wrote in message news:7A0DCFDE-B5DF-4229-87C6-9659A9BBA470@microsoft.com... > We have 3 RAID 10 volumes, with various DB's. We h...

calculating weekday date
I need to calculate a date 10 days from a start date that, if the final date is a Sat, the result defaults to the previous Fri, or, if the final date is a Sun, the result defaults to a Mon. Is this possible? I use 2003. Thanks for your help. In your response, remember I am a novice and need a little extra explanation. :) On Tue, 10 Jun 2008 19:23:01 -0700, Khardy3352 <Khardy3352@discussions.microsoft.com> wrote: >I need to calculate a date 10 days from a start date that, if the final date >is a Sat, the result defaults to the previous Fri, or, if the final date is a >Sun...

Copying multiple cells formula and paste as Values in same locations
Hi, I hope someone can help me with a code to handle this problem I hav been facing at work. Column AW (Order Qty) and column DZ (Order By) are the vlookup formula that display the result. The PartNumbers are filtered that show only selected rows. How can I selectively copy certain formula cells and paste as onl values in the same cells again ? Thanks so much in advance. columnA columnAW columnDZ PartNum. Ord.Qty OrderBy row228 100-344 223 SIEMENS row610 10-236W 173 SONY row997 2202246 454 HITACHI row2169 2244W 6...

Converting dates in text format to date format.
I can do this manually using =Datevalue("02/14/2007"). But all my dates (several hundred of them) are in the text form (02/14/2007). I've tried writing a macro to add the quotes at either end of the text form date within the parens but I can't get the macro to add the quotes without copying the first date in the column. I should be able to figure this out but so far all my efforts have been futile. Any suggestions would be appreciated. Thanks and regards, PJF Try Data>Text to Columns>Next>Next>Column Data Format>Date>MDY>Finish. Assuming you don...

Leading zero is suppressed when I merge 2 cells
When i merge two cells i get this example: A1 B1 C1 124 05 1245 What I want is this: A1 B1 C1 124 05 12405 I used this formula to merge A1 & B1 but it supresses the leading zeros in column B Hi Jeff i'm guessing its because the 05 is displayed through cell formatting (custom number format of "00") rather than actually being there in the cell .. in this case try =A1 & text(b1,"00") Cheers JulieD "Jeff" <anonymous@discussions.microsoft.com> wrote in message news:396501c49c0a$d3b384a0$a501280a@phx....

I need 19:00
My sheet shows tool down times for a works project A1 = 19:00 (in time format) B1 = 7:00 (in time format) C1 = =B1-A1 which displays ############# How do i minus time after 00:00 0:00 If i take 19:00(B1) from 19:01(C1) it gives me the correct answer of 0:01, its just with numbers after 00:00. The WinXL default 1900 date system can't show negative times (times are stored as fractional days, so 19:00 = 0.791666667). The (Mac default) 1904 date system (Tools/Options/Calculation, check the 1904 date system checkbox) can, but the result would be -12:00 One solution would be to add 1 ...

cell formatting #4
I am trying to format a column so that it keeps track of computer hardware mac addresses. -Each cell must be limited to 17 characters only. -Cells must have 5 visible permanent colons so that users may only enter the remaining 12 letters/numbers. -cells must be restricted to entry of letters and numbers (no special characters). -users must be presented with an error message if they try to leave the cell without entering 12 letters/numbers. -column must not accept duplicate mac addresses. Can anyone give me some direction on how to do this? -----------------------------------------------...

email links in Publisher pdf
Why won't Publisher 2007 convert my email links correctly when saved in pdf format? It puts "mail to:" in twice automatically. It is converting website links without a problem. If memory serves the Office 2007 SP1 fixed this in Publisher. The SP2 is also now available. There have been some reports of not being able to open existing Publisher files after installing it, and a report that a fix for that bug is due by the end of the month....you might want to wait to install SP2 until after the first of the month, or just install SP1. DavidF "Rora" <Rora@discu...

=Sum(Abs) and Date Range Question
In a report I have 2 simple text boxes. One is =Sum(Abs([First_Level_Appeal_Determinations]="Denied")) and the other is =Sum(Abs([First_Level_Appeal_Determinations]="Granted")) Both are from the same field in the same table - no query involved. I want the user to be able to input a date range and then run the report to count the number of each criteria. How would I do that? -- Thanks knowshowrosegrows schrieb: > In a report I have 2 simple text boxes. One is > =Sum(Abs([First_Level_Appeal_Determinations]="Denied")) and the other is > =Sum(A...

Issues with 1900 & 1904 base date
All, I'm having the cross-platform issue with the base dates 1900/1904 that we all know and love. I've followed the steps below (in quotes) from the following thread: http://www.mackb.com/Uwe/Forum.aspx/excel/4803/Problems-between-the-1900-1904-date-systems-Mac-to-Win-Users "1. Create a Workbook with the 1900 base date and other standard formats that you want for a workbook and then save in /Applications/Microsoft Office 2008/Office/Startup/Excel as Workbook (template) with no File Extension. 2. Create a single sheet with the 1900 base date and other standard for...

Installing CRM 3.0 03-01-06
I have to put together a test or proof of concept system for CRM 3.0. to do this I only have the one machine. I have set up the machine with an AD and it's own domain, installed Exchange and SQL 2005 all running fine and with out problems. Now when I go to install CRM 3.0 professional version onto the machine all starts to go well until I get to the OU selection. When I get to this point the selection box does not list the domain that the machine is installed on and won't allow me to connect to it. What are the next steps to allow me to get it running? Does the CRM 3.0 Pro not work on...

Outlook XP (2002) startup error: "...requires Microsoft Outlook Express 4.01 or greater."
On a system running WinXP Pro w/ SP2 and Office XP. Attempts to open Outlook result in the error message: "Cannot start Microsoft Outlook. Outlook requires Microsoft Outlook Express 4.01 or greater. You can install Outlook Express by running IE5Setup.exe from the IE5 folder located on your CD or through Windows Update website." Outlook Express version 6 is installed and runs without error, though it is not used. The version of Outlook installed is Outlook XP. I have viewed and tried to follow the advice in several Microsoft support articles, to no avail, including, but not lim...

Autofilter by Date in Excel 2002
I am having a problem filtering using the custom filter in Autofilter. I want to custom filter by dates greater than - for instance 1/01/1994. I only get the 1900 dates and not the 2000 dates. At first this worksheet had dates in 2-digit year format, and not all of them were recognized as numbers - some were seen as text. I inserted a column and manually re-entered dates using a 4-digit year and the problem continues. I've multiplied by the number 1. I've copy/paste special\values. I've copy/paste special\values the entire worksheet to a new workbook. Is there something inh...

tt 01-11-08
tttt -- Peter Widmer Allenbergstrasse, 21 8708 M�nnedorf Tel. +41 44 201 49 68 Mail: widmerpeter@bluewin.ch ...

selecting non-contiguous cells
In EXCEL 2007,trying to select multiple non-contiguous cells. Unlike in Excel 2003, where holding down the CTRL key enables selection of non-contiguous cells, that method does not work. How can I do this in Excel 2007. Works exactly the same in Excel 2007 as in 2003. Maybe because the color is so washed out you don't clearly see the selections. Try SHIFT + F8 to get into Add mode then select non-contiguous cells. Any difference? Gord Dibben MS Excel MVP On Mon, 1 Feb 2010 13:43:01 -0800, facinadr <facinadr@discussions.microsoft.com> wrote: >In EXCEL ...

Workflow Rule Date
Hi I really need assistance with this. I am trying to create a worklfow rule that fires when a particluar date is reached. I am aware the the workflow rules are triggered by the Create, Assign, Change Status and Manual events however I tried to create a workflow that after a records status changes and the date is reached, a task would be created. Unfortunately this workflow rule never fired. I set the workflow rule to fire if the date field equals the Execution Time and nothing happened after I selected the Date field as todays date on the record in CRM. Please assist? Thanks It s...

cell data management
is it possible to take a cell with a full name, john bob jones for example and split into 3 cells easily without having to retype the information manually? Data - Text to columns, delimited, use space as a delimiter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "stumped in atl" wrote: > is it possible to take a cell with a full name, john bob jones for example > and split into 3 cells easily without having to retype the information > manually? Stumped - Check out the Text to Column (under the Data menu). ...

Crystal Reports Logon Error 01-19-05
When I try to view a report I get the following error: Crystal Reports Logon Error Please verify that you have enough Crystal licenses. I have restarted all the services in crystal configuration manager APS service is running And confirmed that NT Authority\Interactive and NT Authority\Authenticated Users are in the Built in Users Group of the SBS server. I am still getting the error. I have CRM 1.2. Can anyone please help. Thank you We ran into this at a client site. It's a known problem. https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;875086 The short of ...

multiple hyperlinks to a cell
It possible to add two hyperlinks into a cell? No. One hyperlink per cell. But you could cheat. You could add a couple of rectangles over the cell (make the borders invisible) and assign a different hyperlink to each of the rectangles. You could even format the text under each rectangle so that it looks like a hyperlink (blue, underline, smaller??). sk wrote: > > It possible to add two hyperlinks into a cell? -- Dave Peterson ...

Displaying time from 00:00 to 0.00
I want to do the opposite to what I have seen on the forums and convert time back to decimals ( I work on aspreadsheet but it has been passworded so I can't crack it. :) I want to be able to write (van out) (Van back in)(Total Van time) 7.25 11.25 4.00 Can anyone help me please ( or a way to crack passwords):cool: -- North for Short ------------------------------------------------------------------------ North for Short's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22695 View this thread: http://www.excelforum.com/showthrea...

Links in database to be converted?
I have to convert a database that contains many linked tables. Do I have to convert the back-end database first? Do the links have to be resolvable? This database is from someone elses machine and the links don't map to my machine because I don't have the drive letters, folders or ODBC connections. I don't want to have to set up all that stuff up to convert the database unless I have to. Do I? > I have to convert a database that contains many linked > tables. > Do I have to convert the back-end database first? > Do the links have to be resolvable? > > This ...

date string as sheetname
I have a macro that copies the last sheet to a new sheet at the end. I would like to note the date that is in A2 prior to the copy, and add one month to it. This result will then become the name of the new sheet in the form 1-Oct-05 or something equivalent (ie: 1 Oct 05, 1Oct05,...) How can I get the date from A2, and how can I modify it to the correct string? Thanks. Hi Keyser, Try: '==================>> Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim sStr As String Dim i As Long Set WB = ActiveWorkbook '<<======== CHANGE i = WB.S...

cell outlines
I have one cell in an excell doc (office xp) that has no outline, it is open to the cells on either side as well as above and below... This happened when I tried to paste some text into that cell. I have since deleted the text, but am unable to correct the cell. I have tried "format cell" and the only option is "outline" that created the desired borders but is conciderably darker than the other gridlines. thanks in advance, trevor Hi Trevor! When you select Format Cell>Borders there should be a selection of line styles to choose from. From the sound of your des...

Using a Message Box to display �Random Quotation� stored in cells
Hi Can any one help me with this one ? What I�m trying to do is to click on a button which runs a Macro whic in turn selects a random quotation i.e. �to be or not to be�.� whic is already stored in a cell. I have about twenty quotes but I need to added to them all the time s the list would keep on growing Any help would be greatly appreciated Many thanks Ro -- Message posted from http://www.ExcelForum.com Hi Rob if your quotes are in column A of the active sheet try the following code (assign this to a button on the active sheet): Private Sub CommandButton1_Click() Dim wks As Wor...