Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.

Share tech news, updates, or what's on your mind.

Sign up to Post

How can I stop Smartsheets.com from sorting my online spreadsheet alphabetically by the first column?

I have pasted this spreadsheet directly from Excel 2016 and I want it to stay sorted in the original order; I don't want the spreadsheet to be sorted by alphabetically by the first column.

How can I stop Smartsheets from doing this?
0
Free Tool: Site Down Detector
LVL 12
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

In excel I have a column of data with information including a name. They look like this:

QuickPay with Zelle payment to John Doe 7135261870

In the next column, I need it to take out just the name. The names are always right after the word "To" and before the number which always starts with a 7.

Thanks!
0
Hi, we are looking for a solution that will allow us to encrypt a spreadsheet and auto send.

We currently have data in Google Sheets that automatically emails out a sheet at 6am every morning using a free plugin called Email the spreadsheet.

(https://chrome.google.com/webstore/detail/email-spreadsheet/nmjdgbcladefaegbhmndjchmpbnhlcnp?hl=en)

This works fine but now we need the sheet to be password protected before its sent, possible with a shared password for the user at the other end.

We don't want to go backwards and add a manual process of a user having to password protect and manually send. We need it to still be automatic.

The problem is, the add in converts it to an excel doc as an attachment and sends it out. In the conversion it losses any security/password.

We've looked at using smartsheet but it does the same.

Is there anyway of automatically encrypting a google sheet and sending on schedule.
0
How do i use macro to auto update my formulas and cell references? I have a pivot table containing sales data, with the columns stating months from January to March. But for every start of the month, i need to update the latest month (i.e. update to April when it's May) to show to a new column. I cant use getpivotdata because i need to track the cell reference, and the data will change for each month so i cant pre-count April's values. Beneath the pivot table, I have a normal table stating sales data total [i.e =sum(Jan-Mar)] which i have to manually update. Is there any way for macro to automate the normal table to update =sum(Jan-Mar) to Jan-Apr? Example like =sum(b1:d1) to =sum(b1:e1) and for subsequent months?
Book1.xlsx
0


How to add the texture in rows?
0
I'm posting this as a solution for the record and the benefit of others, as it's a satisfactory workaround to a common problem using array formulae in google sheets.

Often with spreadsheets you may need to fill down the same value into blanks below until a new value is found, see screenshot attached.

screenshot
Easy with regular formulae, a little more tricky with arrays, but possible, see formula also in screenshot.

If anyone has a better way please post here. I'll leave this open for a short while in case anything comes up.

Some google sheet formulae don't currently work with arrays (eg INDEX), but both MATCH and VLOOKUP do, so this workaround is possible.
0
i want to import data (dynamic) to Google sheet from excel online (onedrive). i know it can be done from other datasources but unable to pull to google sheet
0
Hello and thanks in advance for any help you can give me.

I have written a simple SSIS package in Visual Studio 8 that reads from MSSQL and outputs to Excel 2003.  The package has two dataflows each writing to a different worksheet in the same workbook.  The first dataflow works fine but the second fails with the following error:

[OLE DB Destination [344]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
[OLE DB Destination [344]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (357)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (357)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (344) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (357). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

During development both flows worked, but now I cannot get the second to work at all.  It …
0
Hello Experts

Something very strange just happened.  I have a client who, following  some automatic Microsoft updates, was suddenly unable to expand all his sub-folders in his Outlook 2007 mailbox.  His .pst file is over 15GB in size and I updated his registry, but to no avail.  I wound up upgrading him from 2007 to 2010.  Now all his sub-folders open but he also has several spreadsheets that he works with nearly every day and several of those are suddenly days, weeks or months old.  So he has lost some data, but not really lost.... just replaced.  I haven't seen any of the files yet, he just reported it to me by phone.  I did tell him not to disturb any of the files that appear to have been replaced.  I'd like to help him get back what seems to be missing, but I don't really know what the heck happened.  I mean, I can see a file being missing, but replaced by an older version?  I don't see a path to that scenario.

Does anyone have any ideas?

Thanks

thedslguy
0
How to round to 0, 5 or 9 which ever is closest?
0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

i need a newest to oldest order i google spreadsheet.
0
Hi All

I'm trying to build a macro where it will prompt the user to select a csv file for import. I have looked around the net and found several macros however they won't prompt the user to go to the file for importation but rather have the spreadsheet and csv file within the same folder to allow for the import to work.

What I'm looking for is an Excel macro where it can update a tab called Commission and columns D11:O427. I'm wondering when the csv file is imported can it be formatted in the same way the data is presented, see Capture for an example.

Thanks all!
Capture.PNG
0
i am trying to create the below.

Jan 18
Authorised      13
Pending               8


when i enter the letter A or P i another sheet, like that to be counted in another cell as above, also to follow the month year order

Dave
0
Hello,

This post is a follow-up to two previous threads located here:

Extract certain items from one list to create another list in Excel (closed 2017-10-28)

Understanding a few parts of an array-entered formula in Excel (closed 2017-11-21)

Briefly, the solution to those threads consists of an Excel formula which selectively extracts certain items from a source list and displays the extracted items in a new list while maintaining the correct order of the extracted items. Following is the solution formula and a screenshot from the attached file:

=IFERROR(INDEX(B$1:B$13,AGGREGATE(15,6,ROW(C$4:C$13)/(C$4:C$13=F$2),ROWS($4:4))),"")

Open in new window

2017-12-10b.png
Now I'm wondering how to modify the above formula to allow for a date column in the source list (which may or may not be in order chronologically) and then display the results in a list which is ordered by date as shown here (eg Abigail was listed first in the previous result lists but is now listed last because her associated date is later than the others):

2017-12-10c.png
As shown in the next screenshot, the attached file contains solutions from the two previous threads (AutoFilter 1 and AGGREGATE() 1) and accompanying notes (which are not directly related to the current thread) as well as a new sheet tab, DisplayByDate from which the previous screenshot was taken.

2017-12-10d.png
Thanks

AutoFilter_array-formula-evaluation.xlsm
0
While trying to find out how VLOOKUP with conditional formatting.  I'm using a random picker that highlights the random name and would like to pull the highlighted name to another sheet/cell, etc.
While reading the post with the same Title I've done a work around for similar by formatting the destination with if not empty or what ever condition that will highlight the destination when in place.
As a rookie, I'm still trying to learn how to do the task above.
Thank you in advance,
D, Smith
0
I have two columns (A and B) on a sheet in Google Sheets (and my local copy) that when changes are made in any of these, an e-mail notification should automatically be sent to person A.

Then I have two other columns (Y and Z) on the same sheet that when changes are made in any of these, an e-mail notification should automatically be sent to person H.

Is this possible?
0
Hi everyone,

I need help on a worksheet I am working on in Google Spreadsheets.

I attached the screenshot of the document I am working on.

2017-08-03_15h16_45.png
1, 2 and 3 have been imported from another Spreadsheet via the Import Range function.

In 4, I need to average 1, 2 and 3, but it's not working because the formula is not reading the value, but the import range formula.

Any help would be appreciated.

Thanks!
0
I have a database containing data for about 20 quality measures for a about 80 physicians.  I have a workbook built that lists the providers names, email addresses, clinic, etc.  I have pivoted the database to create spreadsheets for each provider for one measure.  (I will duplicate this for each measure).  What I can do is run a macro I've created to automatically email each physician their associated worksheet, based on the their email which I've placed on their individual worksheets.  This sends them a read only version of excel as an attachment to their email.  What I would like to do is have my macro embed a snapshot of the worksheet into the body of the email, instead of sending it as an attachment, but I'm not quite sure how to do that.  I'm not very excel savoy and am happy I have gotten this far.  Below is the macro I am currently using and I need to know how to edit this to get what I'm after.  I appreciate any help!  Thank you!


Sub Mail_Every_Worksheet()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    TempFilePath = Environ$("temp") & "\"

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2016
        FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    …
0
hi I'm taking destination as a lookup reference I taken ID in reference table and nomatch ouput map to destination ,partial cache mode I'm using ,I'm getting duplicates to destination
0
I have already make a "XML Maps" Mapping list under the XML Sources.
It works file when I do the XML import to the Excel sheet.

However, I would like to add the file name of the XML which imported to the excel.
Can anyone help me on this?

Enclosed with the Excel sheet with the mapping list included
https://drive.google.com/open?id=0B-cvVcO00sRvVldicS1kWFhGSGs
0

Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.