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

Hi,

I have lots of quotes stored an Excel spreadsheet.

Something like:

The quick brown fox jumped over the lazy dog.

Have already set the cells to Text format.

No matter what width I set the cells to, there are

CR & LF

inserted randomly into the text.  So that, if I try to copy the quote and paste into Word, it comes out as

The
quick brown fox
has jumped over the
lazy dog

Same for Notepad.  Or Wordpad.  Any text-focused destination.  

Is there any way I can copy text from a cell in an Excel spreadsheet and have these CR & LFs suppressed or removed automatically when I hit paste.

Many thanks!

OT
0
OWASP: Avoiding Hacker Tricks
LVL 13
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

There is a hidden column L in the source file ( keno results) which is upsetting the formula when  I delete the column L  in the source.

I deleted the column L in the source file and now I am able to do a exact copy and paste into the manually entered excel file(destination file) and it pasted correctly into the cells c:v

but in doing so I lost the formula because the monitored numbers count did not get updated.

How do I save the formula, while at the same delete the hidden column L in the source file ?

I also type 20 numbers for the 31st March (just to test out)  in the source file and to see the if results get updated in the destination file , but it did not get updated. I highlighted row 122 and pulled the fill handle down to row 123 to populate the formula and the source file was requested and I clicked on the source file and after that the destination file did not get updated.

keno-reuslts.xlsx source excel file
manual-entered-CondFormatQ29141105.xlsm destination excel file
0
In the attach example, i have two sheets, sheet one has total amounts for each Roll number and sheet two has detail amount for each roll number. I would like to highlight those amounts  in sheet two which will be equal to the total amount in sheet 1 for each roll number.
For example in the attach sheet, For roll number 225 in the sheet one, the amount is 40, so it has highlighted  5 10 and 25 in sheet 2 because if I add it, total will be 40. It did not highlighted 18. Is there anyway if we can do this through VBA.
thans
example-2.xlsx
0
I have one of my users that is having an issue in Excel.  It seems that any excel file he has, complex or simple calculations has this same issue.  If he leaves the file open, and does other things on his pc, or steps a way for a while, Excel is very slow to respond when he clicks a cell on the spreadsheet.  He has several spreadsheets he updates every day.  He is so used to this issue, that he knows to click on a cell and go work on other things for a minute or so, then comes back and works in spreadsheet.  It acts like excel is hung, but always comes  back and stats working.  You just have to wait.  Everything else on his pc responds great.  He said many other co-workers have told him they have the same issue.  We run Microsoft Office 365 ProPlus, and we do use cloud storage such as Microsoft's OneDrive.  I think most of his files are stored on SharePoint, and this also has cloud storage back to Microsoft.  Almost seems like Excel has its own hibernation mode.
0
We have Office 365 and have has issues for over a month with Excel spreadsheets becoming corrupt. We've had issues with various spreadsheets, some become corrupt after a few days. It's truly been hit or miss, sometimes we have issues 3-4 times a day, then we go 1-2 days without an issue. I scanned the file server for viruses and nothing. I've called Godaddy who we purchased Office 365, they're not much help. I called Microsoft and they told me to call Godaddy since I purchased our licensing through them.

I've gone through the below link and performed the suggested actions to remedy the issue.
https://answers.microsoft.com/en-us/office/forum/office_2016-excel/file-is-corrupt-and-cannot-be-opened-in-excel-2016/3e378431-83c6-474e-a852-eaea3bb802a8/?fbclid=IwAR2nEOy3kdUJLOKb3df5ZdhwWALN08wu70q--zOUNujEc5AHDU_U2mpAjtk

I will try and clarify, its not one or two people. We have 5 users that modify documents and send them out to clients. On another hand our HR department makes changes to Time Card files within Excel where nobody else can access. There is not one specific user or file to pin point the issue. Our head of HR doesn't access the files from the staffing side that are experiencing these issues, and vice versa.

So the pop-up users receive is attached, only thing the changes is the file name. Most of the time  they click YES and it opens but the formatting is lost. This is a big issue when sending the documents to clients and they receive the pop-up, they proceed …
0
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
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
PMI ACP® Project Management
LVL 13
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.

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
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
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
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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
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

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.