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'd like an Excel formula to remove symbols and commas
examples
£5,620   to   5600
€47,800 to  47800
The only currencies are £ and €
Many thanks
Ian
0
CompTIA Cloud+
LVL 13
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Trying to get an AutoHotKey script for copying from Excel.

Seems like the answer is in this link.

https://autohotkey.com/board/topic/62759-remove-linebreak-after-copying-excel/

Where it resolved to just a single line of code in the end after about twenty iterations.

If you are just looking to trim the line feed off the clipboard content (like what you get after copying excel)

StringTrimRight, MyVar1, clipboard, 2

should store your string to MyVar1

However, don't know what the whole script should look like.

Sincerely,

OT
0
I would ideally like to be able to Sort and Edit my Google contacts, online, from the same page.

Is there anything that makes this possible?
0
In a Google spreadsheet, I have an area where people are doing manual data entry.
  • People are entering game results from a paper scoresheet
  • Each row in the data entry area shows the results for a single "question", entered into Columns B through K
  • For each cell, the valid values are a blank cell, 0, -5, 10, 15, and X (upper case only)
  • For each row, I need to get a count of how many cells have an invalid entry

I have a formula now which is returning the correct answer, but it's a brute force approach. Is there a more elegant way to do this?

=if(not(or(B2="",B2=0,B2=-5,B2=10,B2=15,exact(B2,"X"))),1,0)+if(not(or(C2="",C2=0,C2=-5,C2=10,C2=15,exact(C2,"X"))),1,0)+if(not(or(D2="",D2=0,D2=-5,D2=10,D2=15,exact(D2,"X"))),1,0)+if(not(or(E2="",E2=0,E2=-5,E2=10,E2=15,exact(E2,"X"))),1,0)+if(not(or(F2="",F2=0,F2=-5,F2=10,F2=15,exact(F2,"X"))),1,0)+if(not(or(G2="",G2=0,G2=-5,G2=10,G2=15,exact(G2,"X"))),1,0)+if(not(or(H2="",H2=0,H2=-5,H2=10,H2=15,exact(H2,"X"))),1,0)+if(not(or(I2="",I2=0,I2=-5,I2=10,I2=15,exact(I2,"X"))),1,0)+if(not(or(J2="",J2=0,J2=-5,J2=10,J2=15,exact(J2,"X"))),1,0)+if(not(or(K2="",K2=0,K2=-5,K2=10,K2=15,exact(K2,"X"))),1,0)

Open in new window

0
Can you please look at column Q and let me know why I'm getting FALSE.  I either want "research" or blank in the results
Multiple-Pernr-List--EE--4_2_2019.xlsx
0
Hello there,

I am attaching the created excel file (thanks to byundt).

I  am having the following issues.

1) Keno website have keno results uploaded on their website.
   I dowloaded the excel keno results file.    Initially  I was planning to type the 20 numbers. But I thought if  I could copy, import or extract it would be faster to get data

 I copied the 10 am draw into  manually created sheet 2 , but when I pasted the 10am draw  into row A3   between C3: V3,  one cell got blanked (cell L3)  and last number populated in W3  instead of V3.    what could be the reason. ?

snapshot of  cell blanked

2)  Can I extract or import the results from  keno results file directly into the manually created excel sheet.

3) I want to avoid typing the the results daily.   I would prefer to import or extract results .
how do I do it  if possible ?

4) I don't know how to attach the MATCH feature in Sheet 2, the Match feature in Sheet 1 is provided.  I don't know how to apply Match feature from Sheet 1 to Sheet 2.



Thanks in advance
manual-entered-CondFormatQ29141105.xlsm
keno-reuslts.xlsx
0
I had this question after viewing get data from different spreadsheets ( in a folder).

Hi I need to copy all files in a folder to create a summary file

Explanation: say i have 50 files i a folder, I need a copy all the sheet with sheet name as "over500payments2018" to one file ;

the heading row start at row number 13
over500payments2018.xlsx
0
Have text stored in Excel.

About 1,100 rows with 5 columns.

Need to search for a specific word, for example,

red

When I search it is finding examples of the word red, such as cells with

red plasma

But, it is ALSO finding cells with words like

credibility

and predominate

Tried using quotes, but then there were no instances of data such as

"red"

at all.  Seems like it was looking for the quotes, too.

Any way to reveal just the cells containing the word

red

alone?

Also tried searching with the space after.  Didn't work.

Sincerely,

OT
0
I initially had the code to do the following :
Compare the ranges between between sheet1 and sheet2 , when they have unique values and they are below the specified date ( March 25th , 2019 ) , the record should get deleted in sheet1 and when it above the specified date then the record should get copied below the last row of sheet1.
Option Explicit

Sub Operation()
    Dim Sheet1 As Excel.Worksheet
    Set Sheet1 = ThisWorkbook.Sheets("Sheet1")
    
    Dim Sheet2 As Excel.Worksheet
    Set Sheet2 = ThisWorkbook.Sheets("Sheet2")
    
    Dim SpdDate As Date
    SpdDate = DateSerial(2019, 3, 25)    '// DateValue("March 25,2019")
    
    DeleteOldTasks Sheet1, SpdDate, Sheet2
    InsertNewTasks Sheet2, Sheet1
End Sub

Private Sub DeleteOldTasks(ByRef Target As Excel.Worksheet, ByVal dt As Date, ByRef Source As Excel.Worksheet)
    Dim LastRow As Long
    LastRow = Target.Cells(Target.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = LastRow To 2 Step -1
        Dim rng As Excel.Range
        Set rng = Target.Range("B" & i & ":C" & i)

        If (rng.Cells(2) < dt) Then
            If Not (TaskDateExist(Source, rng)) Then
                rng.Cells(2).EntireRow.Delete
            End If
        End If
    Next
End Sub

Private Function TaskDateExist(ByRef Source As Excel.Worksheet, ByRef rng As Excel.Range)
    Dim LastRow As Long
    LastRow = Source.Cells(Source.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To LastRow
        Dim 

Open in new window

0
TVBOARD.xlsxhow do i edit 2 excel worksheets that also has a power point as one of the worksheets. i need these combined because we are needing the right side of the embedded spreadsheet in power point to scroll. i am needing to edit both the excel spreadsheets in order to change the embedded power point worksheet.
0
Python 3 Fundamentals
LVL 13
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Greetings experts,

I am trying to automate renaming tasks on several tabs. Attached here is an example file with a replica of some tabs that I am working with. For some of my bigger workbooks, there will be over 100 sheets.
example.xlsm

For the 1st tab, the "Allocation" tab, I need to rename it as “Master_CellD28Value” which means that if Cell D28’s value is A123 - FIFO, the tab should be renamed to “Master_A123 - FIFO”.

For the 6th and 7th tab, the "ESD Trf Qty" and "EVNL Trf Qty" tabs, I need it to be renamed like this: The part before “ Trf Qty”_Cell C28’s value. For example, if EVNL Trf Qty tab’s cell C28 value is A123 - LIFO then the tab should be renamed to “EVNL_A123 - LIFO”

The tabs which are named "By Ctrn-EIN", "By Ctrn-EMSB", "By Ctrn-ETH", "By Ctrn-EPC" and "By Ctry-IDC", these need to be renamed to “CellE25Value_CellC28Value”. If Cell E25 Value’s is Canada and Cell C28’s Value is B987 -123 then the tab should be renamed to “Canada_B987 - 123”

As an error proofing method, the last tab, the subset list should be left alone.

I am also planning to embed this script in a command button for a userform so I hope that someone can help to advise me on an “On Error” part of the entire code.

I know that this is a very long and hard request so any help is much appreciated :)
0
I have a column of products and their cost.  
I have another column of names of people.  
Am wondering if there a way that I can calculate how much “cost” a person is based on what products are attributed to them?  
So a chicken is $5, a cup is $2.  
Sally is signed up for a chicken and a cup so their total is $7.    
Would I have a column of names and products? Or vice verse, and a separate vlookup or something based on another sheet that has a corresponding amount per each product/item?
I’m sure I’m overthinking this or under explaining but I am awful with Excel.
0
I have an excel spreadsheet where i need to be able to calculate a total if name =persons name and date is between 2 dates.

I have tried =SUMIFS('2018'!$C3:$C2000,"=shell",'2018'!A3:A2000,>=StartDate,'2018'!A3:A2000,<=EndDate,'2018'!D3:D2000)

StartDate and EndDate are named cells on the worksheet.

I have been trying to figure this out but to no avail. I would like to attach a file but unfortunately it has real data in it so that is not possible.

any help would be greatly appreciated.
0
Hi,

I have a ton of spreadsheets I need to hyperlink and wondering if there is a way to do this without manually updating each hyperlink.  For example, on the 'Test List' xls, column B will hyperlink to each respective workbook.  I created the 'Test A' xls as an example of where the hyperlink will route to.  Is there any way do this without manually updating each hyperlink?  The actual xls will have over 200 workbooks I need to hyperlink to.
Test-List.xlsx
Test-A.xlsx
0
I have a formula in the sheet below that shows a value but needs to show a percentage.

Unfortunately, the % icon does not do what I want it to.  I simply want the % to be represented to the right side of the number so that the decimal placement is retained.

https://docs.google.com/spreadsheets/d/13xoGB01HD6ojaAwyUtOYO0HqGL9lf_osDvF6WcOI4Ds/edit?usp=sharing

The current formula in cell N3 is below and assistance is greatly appreciated.

=((L3-L2)/L2)*100

Open in new window

0
Team,

I have spreadsheet that contains lots of entries in one row which is updated as ;

For example.
Name     Access
xyx          domain\ambc;domain\bbbcb

I need to convert it to the below format. I mean i want it in a separate row.

Name  Access
xyx        domain\ambc
              domain\bbcb
Please let me know if it possible in excel.

THanks
krish
1
I have a spreadsheet that uses conditional formatting to check if a date field in another tab has a great value than 2000 (I am trying to determine if the date field has anything in it at all.  2000 is 6/22/1905, which is certainly early enough so I won't hit it in a current billing spreadsheet).  If the date is greater than 2000, it turns the summary cell green with bold green text, if not it is supposed to leave it blank.  My problem is that the field is either green with the green text of the date or 0.  I would like it to display nothing if the formula in the cell returns a date less than 2000.

Formula in the summary tab:   =IF(<tabname>!$C$3>2000,CONCATENATE(MONTH(<tabname>!C3),"/",DAY(<tabname>!C3)),<tabname>!$C$3)
Conditional formatting: if the value in the cell is greater than 2000, fill green, text is dark green and bold

If I use this formula:
=IF(<tabname>!$E3>0.01,"PAID","")
conditional formatting: if the value in the field equals PAID, fill green, the text is dark green and bold

And apply the conditional formatting, it works fine (those cells without "PAID" in them are blank)

I have attached a picture of what a section of the summary page looks like.  Any help would be appreciated.
Summary page with part of the problem highlighted
Here is a copy of the spreadsheet (macro enabled):
Pay-Bills-copy.xlsm
0
I need the cells in yellow to automatically populate using formulas.  Note that I must stay within Google Spreadsheets for this.
https://docs.google.com/spreadsheets/d/164JRaQDrsiwXlDr8sJWed8RqoPu59YLG_a2-BgYfZaM/edit#gid=0

Assistance is greatly appreciated.
0
Excel -  Is there a way to filter for STRIKE THROUGH text??
0
Angular Fundamentals
LVL 13
Angular Fundamentals

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

Rather than use a COUNTIF formula for a value, is there a formula that will count the number of times a column has red fill in it?  I'd like to put this formula as the header of each of my columns so I can know how many red cells are filled in.  Thanks.
0
Hello,

I am creating an Excel workbook to track orders and various aspects of those orders. For example, I have an Active sheet that will list all currently active orders. I have a field for tracking information. However, often there are multiple tracking numbers for 1 order. In those cases I am maintaining a separate tracking sheet. With that in mind, here is what I'm trying to accomplish.

Column A has the order number. Column B would have the Freight Company, Column c has the tracking number and (Currently) Column has the link created by a lookup of column B combined with the tracking number in C.

However, if there are multiple tracking numbers I would like to have an entry in Column Be that says "See Tracking" and the link in Column D would create a link to the location of the information on the tracking sheet based on a lookup of the order number in Column A and finding the corresponding order on the Tracking sheet.

Thanks for any help!
0
Hi,
I would like an amendment to a PROPER function formula to remove an apostrophe
Formula is placed in A2
Please see attached.

cell A4 is correct output

Many thanks

Ian
Proper-function.xlsx
0
Hello all.

I am generating a spreadsheet with multiple sheets and would like to understand how to manage the merged cells on specific sheets.

Here is how I create the sheets (its dynamic based on the size of the array):
Int32 sheetCount = countSheetsFromArray;
                UInt32 sheetIDCount = 1;

                for (uint i = 0; i < sheetCount; i++)
                {
                    Sheet sheet1 = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetIDCount, Name = "ABC " + (i + 1) };
                    sheets.Append(sheet1);

                    sheetIDCount ++;

                    Sheet sheet2 = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetIDCount, Name = "DEF " + (i + 1) };
                    sheets.Append(sheet2);

                    sheetIDCount ++;
                }

Open in new window


I have Mergedcell class that I call a method but it seems to apply the same format on all the sheets.
var rplMergeCells = new RPLMergeCells();
                       worksheetPart.Worksheet.InsertAfter(rplMergeCells.MergeCell(), worksheetPart.Worksheet.Elements<SheetData>().First());

Open in new window


Any input wold be helpful.

Thanks.
Jed-
0
Excel - I am working with a start date and Intervals based on hours.

I need to be able to identify the next due date for a list of items.

Example:
Those that should happen weekly, every 2 weeks, monthly, yearly, quarterly, etc.

I can't figure out how to change the next due without changing the original start date.

Please see attached file.
DueDates2.xlsx
0
Hi,
I would like to find why a vlookup formula is returning wrong values.
Please refer attached cells A15:A17
Many thanks
Ian
Vlookupformula.xlsx
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.