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

How to stop rows from scrolling when trying to copy when freeze panes.
Let's say I freeze row 8 and I scroll down to row 20 and want to copy cells  A1:A30
Meaning rows 1:8 and rows 20:30 are copied which excludes rows 9:19
Is this possible ?
Thanks
Ian
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Please modify below formula to only return a value and not a formula cell with no value

=INDEX(M:M,COUNTA(M:M))

Many thanks
Ian
0
How can I post excel data to a forum without it scrambling as below

Off Time      CourseNo.HorseBet
12:30 PMNAVA2Minella Times5
12:40 PMASCO5Belargus2
12:55 PMHAYD5The Character5

It should appear something like the following

Off Time           Course      No.      Horse                   Bet
12:30 PM      NAVA      2      Minella Times      5
12:40 PM      ASCO      5      Belargus              2
12:55 PM      HAYD      5      The Character      5

Many thanks

Ian
0
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
My brain just froze. I would like the AND/OR formula for the following please
C1 MUST Contain "RED"
D1 can contain "APPLE" OR "PEAR"
Destination cell A1
Many thanks
Ian
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
Wanted to count values based on certain criteria.   For example per the attachment.

The count is on the "MAT_REC_BY_MONTH" tab and the data is on the "DATA" Tab.  I've added some values on Week 1 and week 2 to show what the values should be.

On "MAT_REC_BY_MONTH" tab:  Column D (REC), the count is based on the "DATA" Tab Column Z filtered on "blanks" on Column AB for Calendar WK 1 on column Y.
On "MAT_REC_BY_MONTH" tab:  Column E (REJ), the count is based on the "DATA" Tab Column Z filtered on "YES" on Column AB for Calendar WK 1 on column Y.

Wanted a formula to count for all the weeks listed on the "MAT_REC_BY_MONTH" tab.  See attachment.
C--Users-lfreund-Downloads-COUNT-EE.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
My goal: I select a set of rows and move that set of rows one column to the right. I think I can do this manually, but not efficiently. Attached is a short simulated spreadsheet of the one at work - it has nested loops and if statements. Even though it is short, its data structure doesn't jump out at you due to lack of indentation.
Nested-Macro-with-Merges.xlsm

The current huge table is very hard to read due to the heavy nesting of loops and if conditions. By indenting, I hope to make the spreadsheet easier to read.

The first column has a field name. This field may be a merge of 2 or more cells. Other columns associated with this field name may also have some merged cells of varying numbers. And that has caused me some problems when I try to indent.

Thanks in advance for your assistance. Now I wish I knew some VBA, so that I could give this a try.

Paul
0
Bootstrap 4: Exploring New Features
LVL 12
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Need help understanding vba in excel spreadsheet. Attached is a spreadsheet with an indent macro. I would like to learn enough vba so that I can try to modify it if need be.
 29130882d.xlsm
I would appreciate if the macros could be annotated with comments for the non-obvious lines. Here is what I find obvious:

Dim lngLastRow As Long
For lngRow = 1 To lngLastRow
Next
Exit For
End If
lngLoopEnd = lngLastRow
0
I'm getting an error on this line in a new console c# project:

using Excel = Microsoft.Office.Interop.Excel;

Severity      Code      Description      Project      File      Line      Suppression State
Error      CS0234      The type or namespace name 'Office' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)      excel1      E:\dotnet\excel1\excel1\Program.cs      2      Active

What do I need to be able to access excel spreadsheets from my console c# project.

Thanks in advance for any help.
0
I have a very large Excel table. I want to move some row regions one column to the right, but get an error when I cut and paste. See attached the spreadsheet.
Excel_Too_Many_Cells_Rev_A.xlsx

This is what I am given:


I would like to move all the rows between "Loop" (Row 1), and "End of Loop" (Row 11) to the right by one column. Here is my end goal:
This is what I would like the sheet to look like after I cut and paste
I try to cut the region A2:C7 (which includes B8), and paste it at B2, then I get the error message:
This operation will cause some merged cells to unmerge. Do you wish to continue?
If I continue, then I lose some text.

Since I have a very large number of rows, and have nested structures (e.g., nested loops, or if statements), to do this manually would take a very long time. Can you please advise how to accomplish this more efficiently.

And there are many nested conditions:

Loop on N1
...
If A33 = 1
...
Loop on N2
...
Loop on N3
...
End Loop on N3
...
End Loop on N2
end if A33 = 1
...
End repeat on N1

Thanks,
Paul
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
How can I create new columns based on specific cells and populate the data using Excel PowerQuery?  Table is not export in the best tabular format.  The attached file has the fields highlighted in yellow that I'm referencing.  Also, the date field, could Power Query create a column for month and year?
[embed=doc 1407617]
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
I am trying to link a table and graph from Excel into Word using Office Pro 2016 and these files are in the same local folder. I am copying the table from Excel and pasting using "Link & keep source formatting." Then I copy and paste the chart from Excel into Word using "Keep source formatting & link data."

With both Excel and Word documents open, if I update the data in the table it auto updates the chart in excel and the chart in Word. In Word, I have to right click on table, and select "Update Link."

This works great and is no problem, if it continued to work. Once the documents are closed and re-opened, they do not work. I verified documents are saved before closing. When I reopen either file, excel or word, the data will not update. There is no option to "update link" in Word.

I checked in Word under Options>Advanced>General that "Update Automatica Links At Open" is checked.
0
Hi,
I would like to speed up the following formula by adding the INDIRECT function if appropriate.
I would also like it to be changed from an array to a normal formula if possible.
refer question 'Speed up Calcs using INDIRECT function'.
Please see attached.
Thanks
index-formula.xlsx
0
Hi,
I would like help to amend a formula to speed up calculations with an INDIRECT function.
Please refer to attached spreadsheet.
Many Thanks
Ian
Indirect-Function.xlsx
0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi,

I would like to have the attached set of formulas modified in order to
increase the speed of calculation and to avoid unexpected shut down of worksheets.
The worksheet has just in excess of 100,000 rows.
I've identified that Column E is the culprit.
The ultimate object of the set of formulas is to establish z scores as shown in yellow col F.
Please refer to attached sheet.
Many thanks
Ian
modify-stddev-formula.xlsx
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 was able to convert data from one source by using the following formula.
PROPER(SUBSTITUTE(LEFT(IG2,IFERROR(FIND("(",IG2)-1,LEN(IG2))),"'",""))
from
LAURA'S BAIRN(GB)      
to
Lauras Bairn
So I tried the same formula on data from a different source and it produces something different.
from
Laura`s Bairn
to
Laura`S Bairn

Any idea how to modify it ? to return same result as in top example    ( Lauras Bairn )
Many thanks
Ian
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

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.