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 an Excel 2013 spreadsheet with over 3,000 entries.  The spreadsheet has customer numbers and Company Names (see attached sample) - my original spreadsheet has many more columns but I tried to simplify for explanation purposes. On the sample I have attached there are five customer numbers (3, 4, 5, 6, 7) and only two company names.  I am trying to find a quick way to determine what customer numbers are listed with both companies.  So in my example, customer #4, #5 and #7 are the customers that are associated with both companies.  That is what I am trying to extract.  I don't need to know the customer numbers that are only associated with one company. I need to pull only the customer numbers that are listed for both companies.  Any help is appreciated.  Thank you.
0
Free Tool: Site Down Detector
LVL 9
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

How do i write a nested if statement for the following?

If greater than  14 but less than 16 return £10000
BUT If greater than  16 but less than 18 return £20000
AND if greater than 18 return £30000

I tried =IF(AND(C9>=14,C9<16),10000,0)
Which returns £10000 however when i try nest it, everything just returns #VALUE?


Thank you
0
Hello All,

The strangest thing started happening this morning at our office. We can open spreadsheets just fine. We can be working in excel just fine, but then we will go to open a spreadsheet one minute later and it will hang at 100%. At that point, any spreadsheet we try to open freezes at 100%. After a few minutes, it will open and work fine. However, if we exit out of the 100% loaded window, it leaves the temp file on the desktop. When we go to open the spreadsheet again, it says it is locked by the user. It will eventually go away after a popup comes up saying "could not quit excel."

Things to note:

This is happening on all LOCAL spreadsheets. These are not networked, they are saved right to the desktop of a non-AD computer. We are running office 16 on windows 10 and windows 7 computers. This is the click to run version of  office. All updates have been done to office and the computers themselves.

Things we have tried:

Complete un-install and reinstall of office 365 (registry, temp files, even microsoft's tool to remove office)

Deleting all printers

Setting Microsoft XPS printer as default

Disabling all add-ons (we dont run any, but we checked anyway)

OffCat is showing no errors.

Any ideas?
2.PNG
1.PNG
0
HI Guys

Hope you can help

This may be an easy answer but Im racking my head over it for some reason

I have the following formula that re-assembles a date in a spreadsheet I have:-

=IF(TRIM(A2)="","",IF(RIGHT(B2,1)="M",LEFT(B2,9),TEXT(B2,"dd"&"/"&"mm"&"/"&"yyyy")))

What I need though is an OR statement, after the ..IF(RIGHT(B2,1)="M"... so that if RIGHT(B2,1)="M" OR RIGHT(B2,1)="0", then LEFT (B2,9) etc.

DO I need to change this to an array? Or is there a way I can insert this OR statement within this formula?

As always, your help is much appreciated

J
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
I simply want to sum the totals next to 2 different names in a range. Can't seem to put the formula together, which I believe would be a SUMIF function. No arrays, please, because my list can get longer or shorter in any given month. Please see attached example. Thanks!
0
I have attached a chart which shows data entered on non-consecutive days. Excel is showing me all the days in that sequence. How can I get  Excel just to show the data on the dates entries were made.. thanks as always :-)
Control_chart_data_audit_EE_only_sh.xlsx
0
hi I'm taking destination as a lookup reference I taken ID in reference table and nomatch ouput map to destination ,partial cache mode I'm using ,I'm getting duplicates to destination
0
I have already make a "XML Maps" Mapping list under the XML Sources.
It works file when I do the XML import to the Excel sheet.

However, I would like to add the file name of the XML which imported to the excel.
Can anyone help me on this?

Enclosed with the Excel sheet with the mapping list included
https://drive.google.com/open?id=0B-cvVcO00sRvVldicS1kWFhGSGs
0
I will be entering data, moving around a spreadsheet with the arrow keys.  Then the keyboard will go completely unresponsive.  I can change cells with the mouse, but still not enter any data.  This "spell" can only be broken by hitting one of the Enter keys, which pulls up a spreadsheet (none in particular) that had been minimized.  From there, i can go back to the previous spreadsheet and continue working as normal.  
I have not noticed any pattern to this.  There do not seem to be any favorite spreadsheets.
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Just started a few weeks ago...while working in a spreadsheet, the program starts acting independently, moving from cell to cell...was I am typing formulas, half of the formula will be in the original call and the rest of it will be deposited in a random cell...it's like watching a video game...there is so much activity on the spreadsheet not being controlled by me...any suggestions?...I have run 2 full scans with Defender and they both came up clean...
0
I have an Excel (2013) table that contains a number of records which I need to generate a chart from and each record has its own date and time in the first column - easy enough.

In the cart the horizontal access represents the date / time from column one

When I create a line graph of the data all the records for one date are combined into a single entry on the horizontal access

I have checked in the series config for the chart and there are defiantly separate data points for the same date on the horizontal access.

The result is the graph is rather confusing since rather than just have a single point for each entry on the horizontal access (that will be joined to another series' data point by a horizontal line) one of these 'combined' dates shows a vertical line with the horizontal line connecting it to another series point projecting from somewhere on it's vertical length

I have never come across this before - how can I prevent the summation of data points ?
0
This is in reference to a previous question:
https://www.experts-exchange.com/questions/29009094/Opening-Multiple-IQY-Files-for-full-page-queries-saving-one-cell.html?notificationFollowed=186018718

What I would like to have happen is if I could run a macro, and it would delete everything except the specific data in a cell whose location changes. This cell contains the text "( Displaying ### of ### )" and I would like to automatically delete every cell in the workbook except this cell. The location of this cell varies from workbook to workbook, so I cannot simply use a named-cell macro. I have hundreds of excel documents in this format, and in the end, I need one excel document that has the searched cell data from each sheet compiled into one workbook.

The Workbooks follow an identical format. I'd need a script as mentioned above, to delete everything but the necessary data and then save the sheet, then a second script that would take every Excel file in a folder and turn it into one excel file, with the title of the excel file being in Column A, and then the data from the searched cell (which would now be the only cell in these workbooks) in Column B.
0
I had this question after viewing Win7 x64 Host + Winxp guest + DOS Application Full Screen mode?.

I admit to being a dinosaur that actually misses MS DOS 6.2 and loved Windows XP. The reason being that all my financial records are on Lotus 1-2-3 spreadsheets (dating to 1984) and I am not going to try a transition all of that to Excel.  Lotus 1-2-3 ran beautifully full screen on my old XP machine but with MS refusing to keep XP alive I have transitioned to Win 7. The Lotus program will run in the XP mode of Win 7 but the screen is about postage stamp size regardless of how the  Window options are configured in the shortcut.  VMware is a bit better by allowing me to get to a 10" X 6" 20 line screen that is readable but I am still looking for a real "full screen" option.

"Aciddo" indicated that DOS programs ran full screen in Ver 7 of workstation after tweaking the options feature but I am using a 30 day evaluation version of Ver 12.5 and have had no success with the Ver 7 fix.  I have tried adjusting the screen resolutions in both host and guest to lesser values but that totally scrambles the desktop icons causing consternation upon exit from the DOS programs.   I have even tried DOSBOX but even though the screen goes to full the Lotus 1-2-3 program still is about 3" X 5" within the full screen.

There may be no viable fix for this problem but if anyone has ideas they will be welcome.
0
Background

  • I have a workbook which has a simple table of all numeric data (except for the axis' labels of course) and a basic line chart based on that data.
  • This chart is embedded in the worksheet where its data is located.
  • I am using Excel 2013
  • Unfortunately I can't upload the actual sheet where I have the issue as it is confidential client data. I also can't recreate the issue.

Question

Normally when you click a line in the chart Excel will automatically select / highlight the source data for that series. In one chart however, although it shows the correct graph / values when you click on the series nothing is highlighted.

If I create a new graph of exactly the same type based on the exactly the same data (i.e. using the same series) then clicking in a series line highlights the data as expected.

Has anyone come across this before or have an idea what could cause this ?
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.