Forum Discussion

griffanskee's avatar
griffanskee
Copper Contributor
Oct 20, 2023

Summary of a Sheet

Basically i have a sheet that has a lot of columns on it and i update it frequently each day and i want to take 4 specific columns and put it on a a separate sheet to send to my boss at the end of each week. I tried using the CONCAT formula to auto copy each cell automatically instead of copying each column and pasting it each week. This worked great until i tested it with a column with dates in it, for example 10/20/2023. when i did this it showed some weird number like 45219. i also tried the TEXT formula and using the MM/DD/YYYY format, but it gave a date to the blank cells, for example 01/00/1900. I want it to show nothing when its blank. what formula should i do. Ill add a picture of basically what i want it to look like.

so say michele is the only one thats had her eval signed and i put the date on the other sheet on this sheet it should look like this

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor

    griffanskee 

    You can use a formula in those (column B) cells. Assuming the source for B3 is D5 on Sheet 7, its formula would be:

    =IF( ISNUMBER('Sheet 7'!D5), 'Sheet 7'!D5, "" )

    (Most of those spaces are optional, but not ones inside a worksheet name; I included the others for  improved readability.)


    That "weird number" is Excel's internal representation of a date, specifically, the number of days since Jan 0, 1900 (Dec 31, 1899) ... not exactly right because of a design flaw, and it's different with an Excel for Mac workbook, but you need not worry about that.  So alternatively you can just apply a custom format to your date column(s) that hides any zeros (and negative numbers, which should not occur) that you generate; for example, m/d/yyyy;;;@ 

     

     

    You could use that same custom format in your TEXT function.  But the result of a TEXT function is text, which makes for extra work if you boss wants to do date arithmetic or manipulation.

     

    More info on custom formats: Custom Excel number format 

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    tried using the CONCAT formula to auto copy each cell automatically instead of copying each column and pasting it each week?

    if you upload the workbook.and expected result,I guess the question is easier to be understood.

Resources