[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

I have a user that saves a networked excel spreadsheet using excel 2013. They save it as a 97-2003 workbook. At that point it opens just fine on the 2013 excel but users that have 2007 it only shows a blank page.
I have done the following:
  1. unregistered excel with excel /unregserver
  2. i have unchecked DDE
  3. I have repaired office
  4. I have removed the XLStart folder and let it recreate it
nothing seems to work. Oh and the 2007 system is running the compatability pack and is able to open other excel spreadsheets just fine.
Thanks for your help
0
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

I need a function for column K of Sheet1, that says "If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2.
0
Is there a way to make all Excel spreadsheets open in the same instance, instead of opening a second, third instance of Excel?

For example, if open a saved spreadsheet, then I have one doc open. Then if I go to file, open and open a different worksheet, I want it to open in the same instance.


I am using Excel 2016.
0
I am currently using the following LOOKUP formula in spreadsheet EE_Pub (columns N, O and P) to find 'last match' values in spreadsheet EE_Cat (columns J, K and L), unless the value in column K of EE_Cat spreadsheet = "No Change" or "CX'd" in which case it ignores these rows.

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

I need to update the lookup formula to incorporate additional conditions:

If Column L of EE_Cat spreadsheet (Stage_4) column is blank, use the current lookup formula that finds last match:

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

If Column L of EE_Cat spreadsheet (Stage_4) column contains a date, then lookup formula needs to change from last match, to row that has the latest date in EE_Cat spreadsheet (Stage_4) column (based on Column M value in EE_Pub-Test spreadsheet as per the current lookup formula).

Note that Column L is always formatted as: AA–08 Jan 16  13:02

I have provided the two sample spreadsheets. I have highlighted the cells and included comments on the EE_Pub spreadsheet containing the results I am looking for with the …
0
How to import live data from redshift (database) to google?
Currently im using metabase as my sql studio or a tool but not able to fetch live data to google sheet to feed my live dashboards
0
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
Amazon Web Services
LVL 12
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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 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
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
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

How to round to 0, 5 or 9 which ever is closest?
0
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

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.