Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

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 need a way to process an entire spreadsheet where a code sample has been pasted and could be anywhere in the sheet.

Reading from left to right, and top to bottom, I need to produce a report in the next tab named "count" that contains each term used, and puts the number of times it was used to the right.  

Additionally, it should leave out any terms that follow a comment such as those the follow the marks // till the end of that row

or, are between the comment marks /*  */ on subsequent rows.

It just counts terms within any block of code.  That's it.  The code does not have to be a specific type.

The sheet I am working with is here https://docs.google.com/spreadsheets/d/14h2EmokFw6ovIWcn3hMzVCzL71gC76h9o7hQVgwWhBM/edit#gid=0

Assistance is greatly appreciated.
0
Important Lessons on Recovering from Petya
LVL 10
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Hi all,

I have a "database theory" type question for spreadsheets.

Let's say we're dealing with a commodity that has something like 3 variables. For example, a car:

1. Make
2. Model
3. Color

So for example, a Chevy Malibu could come in different colors like: white, red, black, grey, etc.

What's the most efficient way to store the data in a spreadsheet so that queries and data extractions are efficient?

Thanks in advance.
0
Hi All,

I have calculation below :

Item Amounts          :  2.000
Discount 5 %            :     100
Amount Before Tax :  1.900
Tax 10%                     :     190
Total Amount           :  2.090

For given Total, Tax (% or amount) and Discount (% or Amount). How could I get Item Amounts and Amount Before Tax?

Thank you.
0
How do I Remove a hyperlink from an email address in Excel 2016 cell (A3 for example) - procedure below actually creates an email document using email address. What I want is Text Only - e,g. jimsmail@gmail.com

[from https://support.office.com/en-us/article/Hyperlinks-in-worksheets-edb15706-517b-4ecf-81c6-84068ff8677e?ui=en-US&rs=en-US&ad=US#__toc305068126]

If email addresses of contacts become clickable hyperlinks, you can deactivate them as you would for any other hyperlink.

On a worksheet, click the cell where you want to remove the hyperlink.

Tip     You can also select an object, such as a picture or an element in a chart that you want to use to represent the hyperlink.

Right click, and then select Remove Hyperlink.
0
What is the best & easiest way of expanding all Excel 2016 columns within an Excel spreadsheet to the proper width so that everything within these Excel columns will be visible?

I've seen other people do this with only a few keystrokes and mouse clicks.
0
Hello ,

I have a list of countries and a list of cities per countries.

I want to have in Excel 2 dropdown where the user will choose 1-the Country
and once he chooses the country, in another cell, he will have the list of cities in THAT specific country.

Any help ?

thanks
Sample.xlsx
0
Hello,

The below vbscript is a basic script I found on the web. I would like to use it to look up the entered value in a file or ideally a database and return adjacent value. See notes below. It works as is by entering literal values. I'm ok with just a file lookup, but if can do with SQL database too I'd love to see both ways. Thanks!


' Input Box with a Title
a=InputBox("Enter a Number","Enter Value")

‘User enters a value
If a = "1" Then
‘Value goes in Field2
field2 = a

‘ want this value to lookup  field2 value (a) in a spreadsheet or text file and return value from adjacent column ‘and place in field3

field3 = "One"

Else
msgbox "Enter a value for Field3"

End If

Example spreadsheet (if user enters “1”, return “One”):
Value One         Value Two
      1                       One
      2                       Two

Example text file (if user enters “1”, return “One”):
1,One
2,Two
0
Hello - we have a handful of new excel spreadsheets that were created for our company by another consultant company to be used by our sales force to use for running pricing scenarios and finding sales volume figures.  the spreadsheets are very large - about 10MB.  When a user is local to the domain network here and they open one of these spreadsheets, they work fine.  But when a user who is remote to the network opens one of these spreadsheets on their laptop, everything becomes very slow, until they close the spreadsheet.  Is this an AD / VPN thing?  or what?

Thanks,
Damian
0
Hi,

What would be the formula that can add, say, column A, that has marked "X" (or not empty), up and display somewhere?

example

Col A
X

X
x
X
x
                      Total: 5

thanks
0
Hi,
I want to sum the total hours from every sheets in my workbook referring to a certain code (code are in red in Parameter sheet). I'm nut sure SumIf the the right formula because you have to determine a certain zone.

Typical zone :
example Sheet E007n zone F14;I34 (same for every sheet)

Sheets can have different name.
Regards,
Maxime
Test.xls
0
Free Tool: Port Scanner
LVL 10
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

I have a P&L that i want to add a formula to. Need a sum in the YTD column depending on what month we are in. The only trick is that after every month's information i have an additional column for %s. So i would need to add column c + column E + column G when we are in march but would like to be able to just change the number of months to add based on where we are in the year.

This is what I am trying currently but formula pulls #REF!. I have a number in F3 depending on the month we are in. So if i want sum of jan through sept, F3 will be 9.

=SUM(F3:INDEX((I7,K7,M7,O7,Q7,S7,U7,W7,Y7,AA7,AC7,AE7),F3))
0
I need to show total hours for each 'Planning Plant'
By Month
and
By ActType

And  I need to group the ActType  into 3 categories.

PRDIEM   /   PSCROT  +  PSNEOT   /   PSNE1  +  PSPR1  +  PSPR2

I can't seem to figure our how to group the ActType???
PivotTable.xlsx
0
I have an xlsx with tow tabs

wins
sites

in column L of wins I would like to pull across column D from sites if column A sites equals column A wins

if there is no match would like column L of wins to remain blank

Thanks
0
How can I filter multiple values in an Excel pivot table? Normal settings only allow one.  Need to filter computer names to separate out staff laptops for upgrades.  Thanks,
0
how to calculate number of months that falls in 2003 between two dates

lets say that my start date is in A1 and end date is in B1 and it look slike this

31/12/2000  31/03/2003

in C1 i want the formula to return 3
0
is there an easy way in excel to identify cells of data in a specific column that either have a leading blank space(s) before any text, or trailing blank space(s) after the text?

I am also interested if for zip codes (UK postcodes), that the format should be split into 2 segments, separated by a single space, but wanted to flag up any instances where their are 2 spaces per segment - so any pointers on formulas for that would be excellent. this could also be in address fields, in between the "segments", e.g. 123  ee road, should be flagged as there are 2 spaces between 123 and ee as opposed to single.
0
Hello,

How would you set up a PivotTable to tell you how many times a particular value is present in an Excel column?

For example, suppose rows 1 thru 1000 in column A each contains a single letter of the alphabet as shown here:

2017-10-09a_EE.png
To determine how many times each letter occurs, I would typically list the letters of the alphabet in a separate column then use a COUNTIF() function in an adjacent column in this manner:

2017-10-09b_EE.png
However, I suspect that there is a way to determine the same information using a PivotTable. If so, what are the steps to obtain the desired result?

Thanks
0
Hi Experts,

I need help on following requirement to build the excel vba formula

I have two sheets
sheet one having following columns

ITEM_NAME       QTY
A                           22
B                            60
C                           55

SHEET 2 ND HAVING FOLLOWING COLUMNS

PROD_ID         QTY
A                       12
B                       60
C                       55
D                     90

I NEED TO ADD ONE MORE COLUMN IN THE FIRST SHEET AND CHECK IF PRODUCT MATCH THEN CHECK THE QUANTITY MATCH,
IF MATCHED "YES" ELSE "NO"

FINAL RESULT LIKE

ITEM_NAME      QTY      RESULT
A                          22          NO
B                          60          YES
C                           55          NO
0
I have an Excel Workbook (attached). Each Tab is named (after an initial Memo and Setup Tab) with MO-YR PAGE X. After the Memo and Setup Worksheet, each Worksheet also displays Page X of Y, etc.

For some reason, when you created another page, the new Tab is not named correctly AND Page X of Y becomes Page !Value of Y.
JE-WB-Master--3-pages--ABC-EE-Sampl.xlsm
0
Free Tool: SSL Checker
LVL 10
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

"I need an Excel formula that will find the number of times that each row within a selected range is duplicated and represent that number in the next free column.  I can fill down if required"

Assistance is greatly appreciated.
0
Hi Experts,
I want the cursor to land in the upper split window of a worksheet when it arrives from another worksheet just in case the user has left the cursor in the lower split previously.  What is the code for this please.
Thanks,
Berry
0
Hi there, I am trying to create a formula that searches for a duplicate numbers across multiple sheets and enters text based on the sheet is was found on.

EG:
There is a Master sheet that contains serial numbers, called, well "Master":

I want to search sheet "Joe", "Jim" & "Bob" for the numbers in "Master" column A and if a duplicate is found (in sheets Joe, Jim or Bob), enter text in column B with the name of the corresponding sheet.
For example if sheet Joe column A contains 1234, Master B2 will say Joe, if nothing is duplicated on any sheet enter Not Yet Assigned.

This formula pulls the information I need from one sheet =IF(COUNTIF(JOE!A:A,MASTER!A:A)=1,"Joe","Not Yet Assigned"), but I've tried several combinations to try to get it to search multiple sheets to no avail. I did get one to work but it entered text for every false value found as well
0
I am looking for help to understand the Trend and Forecast functions in Excel.  What is the best approach to calculate across rows to determine the forecast for the next year.

example:  
SamplesTrends.xlsx

I am new to these two types of formulas.

Thanks,

K
0
I have a form where I use a pick list to fill in an acct. number.
I would like the pick list to show the descriptions but then put the acct. number in the cell.

Example:

Pick list show descriptions - user selects 'Tools' from the drop down put the acct. number ' 10003456' is placed in the cell.
0
HI,

I am trying to figure out how to write the XIRR formula when the data (Amount & Dates) to calculate XIRR for a particular stock is not in a continuous series.

Please refer to the attached excel sheet.

I want to calculate the XIRR at Col J13, J14, J15 & J16 for different stocks in Col A  having their respective dates & Amounts in Col C & Col I.

The challenge is XIRR needs the values of amount & dates in a continuous series, whereas my data contains the amounts and dates for other stocks too... so how do I make XIRR pick up the respective values of Amount & Dates for a particular stock only??

Any ideas & suggestions will be highly appreciated.

Thanks and regards,

Rahul
Question.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.