Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

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

Continuance from https://www.experts-exchange.com/questions/29057688/Google-sheets-indirect-function.html
which references my sample spreadsheet: https://docs.google.com/spreadsheets/d/1nRVTl_qhfZgwEdGdL-87GhSHpHM3z9a5PuOvO-_bRyU/edit?usp=sharing

I've added a sheet call Run Charts and I'm trying to accomplish the same thing - a copy/paste of a formula that will dynamically grab that data, in rows this time instead of columns.

The new sheet isn't complete, just a sample of the data that I'm collecting, but the field names/headers should all match up.  If I need to create the entire sheet, please let me know.

Thanks!
0
How to Use the Help Bell
LVL 10
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

How can I view the contents of xml files on an Excel sheet (both in local Excel and Google Sheet) in easy to read format?

I will have each xml file inserted as a link in an Excel cell, and when clicking on the icon I want that the contents of that xml file should be displayed in easy to read format.

Would something like this work?:

https://stackoverflow.com/questions/8984254/how-to-view-the-xml-form-of-an-excel-file
0
Is it possible to stream online videos from my own logged-in area to other persons via Google Sheet, Google Drive or something else? Best would be Google Sheet.
0
For cells in one column, I want to be able to upload a XML file to each cell and have it saved there, and then another user should be able to download this file.

For cells in another column, I want to be able to upload a BMP or JPG file to each cell and have it saved there, and then another user should be able to download this file.

Is this possible to achieve in Google Spreadsheet, or by help from a plug-in?
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,

I need to sum some 30+ products like in the image below.
Basically look for the code in the lookup table, take the value, multiply it with the number in the next column. Then take all results and get the sum.
I can do it the long way, but the result is going to be a very long formula that's hard to maintain.

How can I do it with a shorter formula?
Thank you.

2017-09-22-09_39_44-2017.09.xlsx---E.png
0
I found this app, which seems to do exactly what I need, but it seems much to pay 20 USD just for this feature:

https://zapier.com/zapbook/zaps/8764/save-google-sheets-rows-to-a-text-file-in-google-drive/

Isn't there any free way of doing this?

I will have a chat in a sheet in Google Spreadsheet, and after each chat I need to have this chat saved into a textfile and the chat on the sheet in Google Spreadsheet cleared after saved to the textfile. Instead of a lot of small textfiles, the best would be to have the chats for one year for example all saved to the same textfile.
0
Normally, you write a negative value with a minus sign with no blank space between, like this:

-48.8

But in my Excel sheet, this is written like this:

- 48.8

How can I change this so it's always written like -48.8 (without the blank space)?
0
I have a pick list of months  (January 2017, February 2017, March 2017)  and a corresponding table by month.  
Depending on what month I choose I want the data for that month to show up under the month that i have chosen.
( I can get it for January but not sure how to add if I pick a different month)
(see attached)

thanks so much
Kelly
1A.xlsx
0
I had this question after viewing Module code needed to produce counts in new tab.  The code below works perfectly, but I would like to expand it so that it will sort with the highest count items on top.

I also want to be able to select more than one column to show next to the totals that should remain calculated only based on the first column selected.

As an example, I might select two adjacent columns and I would want that data (provided that the adjacent cells are just duplicate data on all like item numbers), to appear in columns C and D of the new tab next to the item count.

Sub CountItems()
Dim ws As Worksheet
Dim ColRng As Range
Dim Col As Integer
Dim lr As Long, i As Long
Dim x, dict

On Error Resume Next
Set ColRng = Application.InputBox("Please select a column to count items.", "Select Column!", Type:=8)
On Error GoTo 0

If ColRng Is Nothing Then
    MsgBox "You didn't select any column.", vbExclamation, "Column Not Selected!"
    Exit Sub
End If

Col = ColRng.Column
lr = Cells(Rows.Count, Col).End(xlUp).Row
x = Range(Cells(1, Col), Cells(lr, Col)).Value
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(x, 1)
    If Not dict.exists(x(i, 1)) Then
        dict.Item(x(i, 1)) = 1
    Else
        dict.Item(x(i, 1)) = dict.Item(x(i, 1)) + 1
    End If
Next i

On Error Resume Next
Set ws = Sheets("Count")
ws.Cells.Clear
On 

Open in new window

0
Free Tool: Path Explorer
LVL 10
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

I have a large number of spreadsheets in my excel file.  I would like to be able to take a group of the spreadsheets and minimize them so that it is easier to separate them into groups.  Is that possible to do in excel? Thanks for your help!
0
Hi,

I have an excel document that has number rows and columns and it is presented in a standardized format. Each cell has a very specific formula and tends to be either hard coded and wrong or missed and wrong.

I am curious if it possible to prevent users from adding and removing rows and columns and then add a dropdown button to add rows and another button to add columns

Each of these buttons would be placed at the end of a range.

Hopefully, these buttons would, when click, copy the formulas from the columns/rows within the set range and append a row/column at the end of the range
0
I'm running win xp,How do I Recover Excel Docs infected by (fEstasAzulCorrupta)? without deleting them.Very important spreadsheets.
0
Good Afternoon

You will see on this sheet that I have a 'Statistics - Made' table which shows 'Pure Bristle', 'Mixed Bristle' and 'Nylon'.

Rather than me spend time each month recreating the formulas for each month, I would like the table to automatically populate with the relevant numbers for that month.

- Pure Bristle = B1, SB1, B2, SB2, B3, SB3, B4, CB4, EB4 and SB4
- Bristle & Nylon = BN1, BN2, BN3 and BN4
- Nylon = N1, NU2, NG2, N3, N3 and N4

I would be grateful of someone's assistance on this matter.

Best Wishes
0
Dear Experts,

I have two different users working on the same workbook/spreadsheets (attached), resulting in two different versions.  Could you please show me a way to merge these spreadsheets/workbooks together without overwriting the data by copying and pasting?  Thanks.
Sample-A.xlsx
Sample-B.xlsx
0
User is receiving an error message "Microsoft Excel is waiting for another application to complete and OLE action.
The user was in an imbedded spread sheet in a work doc or trying to embed a spreadsheet in Word. They exit Word, the error message appears as a delayed action.  And it keeps popping up even though I do not have any excel spreadsheets open. Sometimes they can't get rid of it unless they reboot. This situation happens randomly. It does not happen all the time.

Anyone familiar with this error? Have a fix?

DLH
0
I have many Excel workbooks where users are allowed to only edit specific ranges. In order to audit the setup, is there a way to list all users that have permission to access ranges. I know I can do this manually, but I am looking for a way to do this through VBA.
I can loop through the workbooks and worksheets. Once I am on a particular sheet, how do I access the permissions?
0
In a new SUMMARY worksheet, I want to be able to copy the names of every worksheet and specific cells from all other worksheets to it.

In the Summary worksheet, I want to enter the FROM and TO cell ranges to get from the SOURCE worksheets, and press a button or command eg. CTRL+SHIFT+R

So in the SUMMARY worksheet, I have:

Worksheet1Name
      Worksheet1Content

Worksheet2Name
      Worksheet2Content

Worksheet3Name
      Worksheet3Content

etc.
0
Hi,
I received a new computer last week but cannot get Task Scheduler to successfully run a VBS script.  It says it is running but the spreadsheets are not updating.  I created a new script to open Notepad and type a few words and Task Scheduler says it is running, ran successfully, but it did not..Notepad never opened.

I can manually run both scripts and they work fine.  Spreadsheets get updated and Notepad opens, types a few words, and stays open until I close Notepad.

My company's computer support has not been able to help at this time and I am currently waiting for the next level of help.  However, that could be more than a week until someone contacts me.

Attached are some screen shots I hope will help, with some identifying text blacked out with a description of what info was there.

Would any of you have any ideas as to why the scripts work when I double click on them manually to get them to run but Task Scheduler will not successfully get them to run, even though it says they are running?

Thanks.
0
On Demand Webinar - Networking for the Cloud Era
LVL 10
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

I have a worksheet in Excel that I keep duplicating.

1. Select the sheet that you want to copy
2. Right click on the sheet tab and choose "Move or Copy"
3. Choose (move to end)
5. Check the "Create Copy" checkbox
5. Press OK

In the SOURCE worksheet there are 2 parts (top and bottom), and the bottom part has formulas that depends on the numbers in the top part.

How can select the cells in the TOP part to copy and it makes X copies into new worksheets?  

In the copied worksheets, I will make changes to the top parts where EACH work sheet has DIFFERENT data.  

Now I go to the SOURCE worksheet, and I want to select and copy the BOTTOM part into the previously copied worksheets, which now has different data in them.  The bottom part inside each worksheet uses the numbers in the top part of each worksheet, so all newly BOTTOM part copied worksheets have different data now since the data is based on the TOP part of that worksheet.
0
Hey EE, I'm currently a little over my head in my a project for my work, what I'm trying to do is make an excel spreadsheet where after inputting how many of each product we need to make to reduce excess prep and wastage, for example 200 Ham Salad Sandwiches, the spreadsheet would in turn say how much of each ingredient would need to be prepped, each of our products have a set portion based off grams, then work out how much of that ingredient would fit into our Prep Tubs and how many tubs we would require each day, I've only recently started using Excel after about 10 years so my knowledge is spotty at best, any assistance would be appreciated.
0
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
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
Good morning Team,

I would like to thank you for your good work.

I need to know what does OrignalTable & OrignalKey mean.
UpdatedTable& Key mean when we are comparing two spread sheets.

I have attached Macro spreadsheet for reference. Once again. Thank you for your goodwork.

Const ksOriginal = "OriginalTable"
Const ksOriginalKey = "OriginalKey"

Also Const ksUpdated = "UpdatedTable"
Const ksUpdatedKey = "UpdatedKey"
Compare-Two-Worksheets-and-Paste-di.xlsm
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.