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 Excel user using 2016 Professional 64bit ….     her spreadsheets are fairly large and with 50K plus rows in multiple tabs.    Her computer is running and i7 processo with 32mb of memory and a M2 solid state drive …..    

Some of the time when she goes to filter, sort or create a pivot table the program locks up and eventually closes excel.    I have looked on the web for settings to increase the performance of Excel with lots of data and was able to disable hardware acceleration which helped for a bit …..

Any thoughts on additional excel settings to solve the issue ….

Joel
0
Angular Fundamentals
LVL 12
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Dear Experts,

I'm having trouble to resize or delete the combo box in Excel on Mac.

Can you please help me ?

Thanks,
Combo-Box-Practive.xlsx
0
Attached is an example Excel spreadsheet with syntax similar to a table in a 60 page word doc. It represents a data structure that has many substructures in it that are controlled by loops and if statements. It is hard to see the structure, so I have learned a couple of techniques to try to indent sets of rows to the next column. For example, I now have a macro that will indent a manually selected set of rows one column to the right.
Nested-labeled_Conditions.xlsm

To get an idea of the desired result, this link gives a similar example of pictures of the a sheet before indenting and after indenting.
https://www.experts-exchange.com/questions/29130882/Excel-Spreadsheet-Error-when-moving-rows-one-column-to-the-right.html#a42766586

If you have a solution, I'll add an extra nested structure to see if it works in general.

<<EDIT 2019-01-16 12:40am EST>>
I changed the title in an important way. No longer should the Headers and Footers be considered as expressions. I now can see that expressions are much too difficult to maintain and vary immensely from table to table as there are potentially many different authors with their descriptive writing style.

The Headers and Footers should be considered as a single known word(s) - and it will be the first word in Column A. Currently, it is safe to say that the Footer will be always:
End - but test should be case insensitive, so end also is a footer.

Some headers I have seen in general are as …
0
Hello Excel Experts

I have a spreadsheet that includes 4 columns that involve date and time.

1 need formulas to do the following things.

In the "Bad" spreadsheet the date and times are in the same column  I need the date column to contain only the date, with the time copied over to the adjoining column.

Also the time format is currently (24 hour time) like this format  " 9/23/2017 5:00" and 4/1/2015 15:00

I need it changed to 10:00 AM and 3:30 PM format (based on 24 hour time)

I think the two attached spreadsheets help explain all of this.  The "Bad" spreadsheet is the one that includes the time combined  in the date columns.  The "Good" spreadsheet shows the times copied over to the adjoining column, including the AM and PM based on 24 hour clock.

NOTE: As you can see, there are 4 columns affected.  START DATE, START TIME, END DATE, END TIME

I can temporarily copy and paste the Bad columns into the Good spreadsheet if it makes the formula easier.  

Thanks!
Rowby
EE-GOOD-Date-and-time-for.xlsx
EE_-BAD-date-and-time2.xlsx
0
Hello, I would like to get some values at openoffice calc importing from google spreadsheet. I put two pictures files as reference: The one is google spread sheet picture file - Googless.jpg, Another one is Oracle Openoffice calc picture file - Openof.jpg. First of all, I add some values in each columns(PID, category, product, test c, price from the left to the right). And I have made open office calc file at the desktop / my local PC. Everytime I add PID number at my local open office cal file, I would like to automatically grab the values at category, product and price at google spreadsheet and put the values at the row where PID number is located at Open Office on my local PC. How can I get it? Sincerely, portal
googless.jpg
openof.jpg
0
Hi, I am wanting to find out if it possible to set up hyperlinks in a spreadsheet that will still be correct  and unbroken (I think they may be called dynamic links) if I move the document to a different drive or computer. I develop the spreadsheets on my computer and then once I have finished it I save it on their computer and it would save me so much time if I did not have to redo all the hyperlinks once it is on their computer.  Thanks in advance for your help. Regards Dot
0
need a hand with an excel formula.
Here is very simplified view of my columns

Ord#	Line#	Backorder
100	1	0
100	2	1
100	3	0
100	4	1
101	1	0
102	1	1
103	1	0
103	2	1
104	1	0
104	2	0
105	1	1
105	2	0
105	3	1

Open in new window


the third column acts like a Boolean. 1= backorder, 0 = not backorder
My goal is to report, how many orders are affected by back orders?

in this example 3 orders contain backorders

How can I accomplish this total in Excel ?
0
Hello,

I have two columns of computer names - please see attached.  

I want to list computers which are in column 1 but not in column 2 and put the result in column 3

Please advise.  

Thanks.
test.xlsx
0
I have customers using Office 365 that are experiencing issues with the formatting of Excel.  One user creates the spreadsheets for distribution, some employees can view and print them as they were created but some must edit the document or it displays and prints with the top of the next page on the current one.  All of their settings are the same, views, margins, etc.  We have even looked at printing to the printer of the creator but the same issue is there.  What setting(s) are we missing that the users might have set differently?
0
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
Exploring SharePoint 2016
LVL 12
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

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.