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

get data from different spreadsheets ( in a folder) to a single file

I have many files in a folder, I need to copy data and paste it to single workbook. I have worked, I have worked little bit on a macro by googling but needs help

I am attaching the input and output files
sample_-Input.xlsx
sample_output.xlsx.xlsm
0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I have two workbooks (Calling.xlsx, Source.xlsx); Calling.xlsx links to Source.xlsx

I would like to have the folder address update automatically in Calling.xlsx if the workbooks are moved to another folder; effectively auto updating links to the other worksheet.

I have found the following formula on Google somewhere: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

     So this formula would return, for example, the following: C:\Users\User\Desktop


My problem is trying to concatenate it with a workbook, sheet and cell reference so that the formula would work like a direct link made manually:

     ='C:\Users\User\Desktop\[Source.xlsx]Sheet1'!$A$1
0
Hi,
I would like to combine a start and end Index formula with a sum probability formula
to greatly speed up the calculations.
Both formulas are displayed on the attached spreadsheet.
Many Thanks
Ian
Sum-Probabilities-to-one-Version-3.xlsx
0
Hi, I'm using openpyxl to format cells in excel from 'General' to other builtin formats. Although the script completes and states the format has been changed, when I try and open the excel file, excel pops with and error box, repair or cancel.

If the report option is selected, then another pop up prompts read only or exit. If I select read only Excel crashes.

Is the below the correct way to set number formats using openpyxl?

runfile('/.../test.py', wdir='/...python/...')
Reloaded modules:

Text
Number
Accounting
Currency

import openpyxl
wb = openpyxl.load_workbook(xlfile)
ws = wb['Sheet1']
ws['B1'].number_format = 'Text'
ws['B2'].number_format = 'Number'
ws['B3'].number_format = 'Accounting'
ws['B4'].number_format = 'Currency'
print (ws.cell(row=1,column=2).number_format)
print (ws.cell(row=2,column=2).number_format)
print (ws.cell(row=3,column=2).number_format)
print (ws.cell(row=4,column=2).number_format)
wb.save(xlfile)

Open in new window

Screen-Shot-2018-09-02-at-17.44.21.png
0
I am looking to create a dashboard in google sheets. With functional buttons and charts. Are there many templates out there for Google sheets dashboards?
0
The company I work for recently upgraded from Office 2010 to Office 2016. Since the upgrade we've had multiple users across the company experiencing issues with Excel freezing particularly when working in file share hosted spreadsheets, no matter the size of the document. We narrowed down some of the issue when it occurred on local files as well by disabling the graphics hardware acceleration. Attempts to quick repair, online repair, uninstall/reinstall, different computer, etc. have been unsuccessful. It doesn't seem to happen all of the time but it does happen frequently.
0
hi

I have a MC55 pocket scanner with Excel Mobile 2010. I have 2 barcode to scan for each product (EAN code and Serial number)

I want to scan into Excel in the following sequence

start of in row  1

Scan EAN code into column A , it tabs automatically to column B and the serial number is scanned.

Then I want it to jump down to next row and column A and keep repeating until all scans completed

so it scans like
Row 1 Column A     Column B
Row 2 Column A     Column B
Row 3 Column A     Column B
etc


thanks
Mecr
0
Hello
I like to know more about excel sheet.
I have microsoft excel sheet with macro function and now i need to move this excel into google but by macro function not working on that.
Can you please help me to solve this ?
0
I use an Excel spreadsheet to calculate time for payroll.  I would like to be able to use a sumif statement to calculate holiday, vacation, and sick time based on cell color.  How can I do that?  I have attached a copy of the spreadsheet I use.
0
I have a large cash forecasting model with weekly data in each column.  Each column has numerous index/match formulas that look up values from other sheets in the workbook.  Each column concludes with ending cash and the next column begins with with that ending cash from the previous week.  

For example, B1 = A20, C1 = B20 and this format continues outward for 75 weeks or until the end of 2019.  So, there are literally thousands of index/match formulas and a few hundred circular references in this model.

Everything works fine until you reach ~30 columns and then the numbers start to drift.  For example, assume AC20 = 100, therefore AD1 should = 100 also.  But it doesn't, It displays 98.  However, if you press F2 and then F9 the answer will be 100.  It's the strangest phenomenon I've ever seen in Excel.  

Oddly the error pattern seems to run in a sine wave fashion.  The first column will be off -2 as in then example above and then the next column is off +4 and then -8 and then +16, etc.  This same pattern continues through the last column and the variances get very large.

I have tried numerous combinations of formula iteration tweaks in Excel Options, I have tried "precision as displayed" and I have tried rounding the formulas in the target cell and the destination cells of the index/match formulas.  Nothing seems to make a difference.

Does anyone have any idea what could be causing this error to occur in Excel?  I've never see anything like this before.
0
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

How can I see total sales by month in a Google Sheets Pivot Table?

Here is the source data:
0
what is the formulae or method to return as no change,  if a key phrases,  for e.g.   Senior Engineer  exists in both strings

Senior Engineer (ABC)  =   Senior Engineer,  Enterprise Infra Branch

using search(A2, B2),  some how don't recognised after the phrases with space, special characters etc.

Thank you !
0
For reference here's the sheet:

https://docs.google.com/spreadsheets/d/1CS-fYhqeKRmEiJC5XA6kRIwIjyBDlBcXj-K9FHjCZjA/edit?usp=sharing

I am trying to create a filter that shows me all the data for May based on type and course category. I highlighted these in blue in the sheet.

I worked out how to count the type and category using this formula:

=arrayformula(SUM((Resolutions!D:D="Complaints")*(Resolutions!J:J="Training and Assessment")))

but I would like to further count by month in the future.

Can you please help?
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
How to round to 0, 5 or 9 which ever is closest?
0
i need a newest to oldest order i google spreadsheet.
0
PMI ACP® Project Management
LVL 12
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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

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.