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

Oy... You ever make some changes to the underlying data columns in a spreadsheet and then not realize that it inadvertently messed up a whole bunch of other pivot tables and charts as a result? Well, I just did... Fortunately, the Google Sheets revision history feature is pretty darn good and just saved me from having to do a bunch of annoying fixes. Thanks, Google!
3
 
LVL 1

Expert Comment

by:Juana Villa
THAT IS SUPER NICE!!
1
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
Thats why I use Dropbox for everything including development.
0
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Dear Experts,

I really need your help in applying the formula to make the Code sheet working.

I have attached the sheet for your reference.

As per the sheet I need to generate code according to combo box selection in various columns and its references are located in different sheets.

I have applied the formula but it does not work as expected.

Can anyone help me on this task?

(Remark : I have used only Index and Match functions. You may suggest VBA if required)

:)
Regards,
D Patel
Code-Template.xlsx
0
Dear Experts:

I would like to extract the substring located between the 8th and the 9th underscore from the right, i.e. 90-235-58-22

I got a user-defined function for that, but I also would like to have a formula. Hope this is feasible!

G:\KLS_Allgemein\DMD\PM_2_3\IFU_Bos_Driver_90-235-58-22_REV_2_RD_2016-02_5s_SF_S24_bw.pdf

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Keyboard shortcuts in Google Sheets for inserting a new row:

In Chrome on Windows or Linux:
  • Alt + I, then R
In IE or Firefox on Windows or Linux:
  • Alt + Shift + I, then R
On a mac:
  • Ctrl + Option + I, then R

Just remember I for Insert and R for row.
1
 
LVL 7

Expert Comment

by:Brian Matis
It's funny... I'm incredibly used to using keyboard shortcuts at the OS level and in desktop apps, but I often don't think to check for keyboard shortcuts in web apps. JIRA has been the one major exception—at some point early on in my use of it, they did a good job of encouraging shortcut usage—but this is a great reminder to investigate keyboard shortcut support in other common web apps.
0
 
LVL 9

Author Comment

by:Brandon Lyon
I just got tired of right clicking and selecting insert new line all the time. It's an operation I do frequently on one of my sheets in particular.
1
I have an Excel macro that takes various spreadsheets listing clients' offices (headers horizontal).  Uses the "Paste special, transpose" successfully to group each type of office  into its own worksheet - and prints that worksheet to pdf.

Works splendidly for what we need...if there are 6 locations or fewer.  More locations result in more columns, and "fat" worksheet is hard to work with.

So what would you recommend for me to create a "break", eg.  a worksheet with 9 columns from the transpose, I can print column-A( the headers) and 6 columns of locations, then below that print column-A(the headers again) and print the remaining 3 columns?

Bit stumped how to do it in Excel macros.

Example: original worksheet
(row 1)  City, Office, Address, Phone, Delivery Hours
(row2)  Smallville, Sam's,  12 Main, 444-1020
(row3) Smallville, Barn-it, 67 1st St, 444-4024
......
(row9)  Smallville, DQ, 53 Sweet, 444-5050
 
Example: transpose with a "page break", if possible!
City               (row 2)  (row 3) (row 4) (row 5) (row 6)
Office
Address
Phone
Delivery Hours

City               (row 7)  (row 8) (row 9)  
Office
Address
Phone
Delivery Hours
0
I have an Excel table with columns A-H, rows 2-100. It is filled with text data (Application Names). I'm looking to populate column H with a list of common applications that are present in all 6 columns of the table.

Duplicate value formatting doesn't work as it identifies anything that appears more than once, where as I'm looking only for what is present in all 6 columns.
0
Dear Experts:

I got the following file paths entered in column A

M:\KLS_General\DMD\PM_1\MyFile_Final_2.pdf
M:\KLS_General_17\DMD\PM_2_3\MyFile2.pdf
M:\KLS_General_2\DMD\PM_4_5_6\MyFile6_Final_23072017.pdf

Is it possible to extract the substrings after the fourth (4th) back slash using a formula. I got a macro for this purpose but I wonder whether the same could be achieved using a formula, ie. applying the formula the results would be as follows:

MyFile_Final_2.pdf
MyFile2.pdf
MyFile6_Final_23072017.pdf

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
0
Hi,
There is a table  - its called “sub-productLevel”  - it has sales data for the combination of customer + subproduct + month

Now there is a table called productLevel – which rolls up all the subproduct underneath it.

I did create pivots on both those data. Sometimes there is a data discrepancy between the product level and sub-product level.
How do I create a pivot:
1.      which is based of the subProductLevel tab, but also references the productLevel tab.
2.      If there is a difference between the subProductLevel and productLevel, it shows it by the name “xdiff”
3.      So that finally when the diff is added up, the subProductLevel data total match exactly to the productLevel data

so explain the above, I just added a dummy xdiff row to the subProductLevel data to make the pivot show what I needed here...

Thanks
pvtSol.xlsx
0
I am trying to see how many there visits are per day for each visit, so that when I put it in Minitab I can see if there is a correlation between number of visits and another criteria.  I currently have the following formula and the following data layout.

A                         B                                 C                                    D
Provider              Date/Time In             Date/Time Out     Number of Visits
Richardson      6/21/17 13:50      6/21/17 14:20
Richardson      6/21/17 14:20      6/21/17 14:41
Richardson      6/21/17 15:20      6/21/17 15:42
Richardson      6/27/17 10:17      6/27/17 10:58

=SUMPRODUCT((DAY($B1:$B151)=DAY(B2))*(MONTH($B$1:$B$151)=MONTH(B2))*(YEAR($B$1:$B$151)=(YEAR(B2))))
0
I need a formula to give the average of all the cells in a row with numbers but excluding the cells with 0's in them.
0
Want to be a Web Developer? Get Certified Today!
LVL 9
Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

I need to figure out how to do check templates with Microsoft excel 2017 spreadsheets  for work can u help me ?Or quicken books  premium or accountants can u help me? I need to edit it he just told me can anyone help me ?
0
I've done this in Excel with a bit of manipulating, and I'm hoping there's a way to reproduce it in Google Sheets.

I am tracking seasons stats for a sport.  On the first sheet, I list the season totals.
In the same format, I list the match totals on separate sheets, labeled accordingly.
I want to have the season totals sheet calculate the sum of those specific stats for every other sheet.
I don't plan on having any sheets that won't be setup this way, so if there's a possibility to have the Season Totals sheet show a sum of (example) Sheet2!A4 + Sheet3!A4, etc... that should work.
0
Hello,

Is there an Excel formula (or formulas) which will merge data from multiple worksheets into a single summary worksheet and keep the combined rows sorted?

For example, suppose you have three checking accounts, each with identical column headings and each occupying a separate worksheet  as shown here:

2017-08-03a_EE.png
2017-08-03b_EE.png
2017-08-03c_EE.png
Note that in this screenshot:

• each colored worksheet tab is named named by the last 4 digits of its account number
• the account for each transaction is identified by the same 4 digit code in column B
• the transactions in each account are sorted by date

I'm looking for an Excel formula that will capture the transactions from all three accounts and combine them into a single summary worksheet as follows:

2017-08-03d_EE.png
In this screenshot, note that:

• each transaction is still labeled by its respective account number in column B
• the transactions are in chronological order
• the balance shown in column G is a combination of all three accounts

I'm hoping there is a solution which will populate the Combined worksheet automatically and which involves only Excel formulas since that will make them tweak-able for me. The 2nd option is to use VBA but then I will be dependent on EE for changes. I prefer not to use an Excel add-on since I've found them to usually be un-tweak-able.

Thanks a bunch

File attached:

Combined-Accts_EE.xlsm
1
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
HOw to sum Across Multiple Sheets as per criteria ??

Where D9:P9 are sheet names, C3:C87 is the criteria range, H3:H87 is the sum range
0
Hi,
There is a big list of people with all their demographics.
There is a sheet called “Prefferred_Names” – which currently has two names

Using sumproduct or similar, I would like to do a partial search for all the strings mentioned in sheet “preferred names” against the column A of sheet1.
partial-Search.xlsx
0
I need to add a summary column in a spreadsheet called "Linked", summing minutes from multiple columns with the following criteria:
If [WO_Change] column does not contain text string "Cancelled" or "Deferred", then in [Total_Minutes] column, sum minutes from [Minutes], [Minutes2] and [Minutes3]: otherwise, [Total_Minutes] column to be blank.

In attempting to solve this I came up with the following formula in the [Total_Minutes] column, but got some whacky results:

=SUM(SUMIF([WO_Change],{"<>*Cancelled*","<>*Deferred*"},([@Minutes]:[@Minutes3])))

I  have attached a sample spreadsheet with the "Linked" tab as outlined above.

Thanks,
Andrea
Sum_if_or_EE.xlsx
0
I have an Excel spreadsheet called 'WO_Tracking'. There is another spreadsheet, called 'Linked', that is linked to 'WO_Tracking', and is formatted as an Excel table. There is a third spreadsheet called 'Summary' to serve as a dashboard for supervisors.

In the Summary spreadsheet, in cell A2, I am trying to get the median (excluding cells with zero values) of Column F (Total Minutes) in the 'Linked' spreadsheets, using the following formula:
=MEDIAN(Table2[Total_Minutes])

But I am only getting '0' as a result.

I have provided a sample workbook with all three spreadsheets included. The formula needs to be based on the 'Linked' spreadsheet, not the 'WO_Tracking' spreadsheet.

Thanks!
Andrea
Linked_Spreadsheet_Median_Formula_E.xlsx
0
I have an Excel spreadsheet called 'WO_Tracking'. There is another spreadsheet, called 'Linked', that is linked to 'WO_Tracking', and is formatted as an Excel table. There is a third spreadsheet called 'Summary' to serve as a dashboard for supervisors.

In the Summary spreadsheet, I am trying to total the following from the 'Linked' spreadsheet:
Linked![Minutes] column + Linked![Minutes2] column, excluding minutes in rows that have either 'Cancelled' or 'Deferred' text string in [WO_Change] column

I have provided a sample workbook with all three spreadsheets included, and filled in the expected result in red in the Summary tab. The formula needs to be based on the 'Linked' spreadsheet, not the 'WO_Tracking' spreadsheet.

Thanks!
Andrea
Linked_Spreadsheet_Table_Formulas_E.xlsx
0
Free Tool: IP Lookup
LVL 9
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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
A student is giving the test in 12 rounds and every round has 4 subjects. I want to make sheet where I can show student result of all rounds on choose subject. For example, if I choose English subject then the formula should show a result of every round which he was taken .
Sample is attached
Book1-V2.xlsx
0
Hi,

I have attached a spreadsheet with some code and functions, but I want to know if I can achieve the following.

Spreadsheet has 7 tabs.  The tabs I am interested in is tabs 2 - 7

Each tab has one row of data.  Now if you go to tab 2 - Risks, the go to column J and in the dropbox click Action.  You will now see that the row has gone.

Go to the Action tab, and you will see that the row appears here, and that is correct.  I column B you will see an R.  As it was moved from the Risk tab, it was enetered as a R for Risk.  But as it now resideds in Action, I want the cell automatically update to an A.

This movement and changes will only aplly for rows that move between Tabs 2-4,  Risk, Action, Isse and Dependancy.

Please let me know if any more info is needed.
Sample-Project-Raid-Log-0.1a.xlsm
0
I am attaching a file that has two Start entries (C4 and C31). This is just a sample set so this would be repeated for thousands of rows. I want to know how to do two things: how do I count the number of entries between each Start e.g. it should be 26 - but to do that for all the rows in the data set. Secondly how do I show the time elapsed (in hours) between each occurrence of start. (cell T7) - again for each occurrence  of this start...basically the idea is that a pump starts...does its thing. Stops and then starts again...
EE--calculating-rows-between-entrie.xlsx
0
I have an Excel 2013 spreadsheet with over 3,000 entries.  The spreadsheet has customer numbers and Company Names (see attached sample) - my original spreadsheet has many more columns but I tried to simplify for explanation purposes. On the sample I have attached there are five customer numbers (3, 4, 5, 6, 7) and only two company names.  I am trying to find a quick way to determine what customer numbers are listed with both companies.  So in my example, customer #4, #5 and #7 are the customers that are associated with both companies.  That is what I am trying to extract.  I don't need to know the customer numbers that are only associated with one company. I need to pull only the customer numbers that are listed for both companies.  Any help is appreciated.  Thank you.
0
How do i write a nested if statement for the following?

If greater than  14 but less than 16 return £10000
BUT If greater than  16 but less than 18 return £20000
AND if greater than 18 return £30000

I tried =IF(AND(C9>=14,C9<16),10000,0)
Which returns £10000 however when i try nest it, everything just returns #VALUE?


Thank you
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.