Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

We offer a recycling service and all our customers receive their pick up dates by email once they sign up for a contract.
The data is saved in a worksheet ("RecyclingTax").

I have been asked to display the current week's pickup in a separate worksheet. Obviously, this is a task for the advanced filter.

I do have difficulties making it work. Maybe it is because of the way I put in the criterias?
What is the correct procedure to make it work?

screengrab.PNG
The only reference to a customer in the "RecyclingTaxi" sheet is the customer ID.
I would have to use a VLOOKUP or similar to get the customer's address as well.  
Is it possible to put the filtered data into a table/listobject and then make use of the VLOOKUP?

screengrab2.PNG
Thanks for helping me make the advanced filter work.
sample.xlsm
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

If a cell contains the word "Yes" (with a few other words in it), we want it it to be "Counted". What would the "countif" be?

We actually will be using the formula for excel and for Google Docs. Is that possible?
0
I have a spreadsheet that has different width and sizes columns and  , when I copy and paste the Data into a new spreadsheet the pasted contents does not come as the original, some information shows ########## even though the previous row shows the right numbers.
Thanks a million!
0
I have an excel file that the user would need to fill
on column A, he can enter either D or C (Debit or credit).
Based on what he entered, I want the cells of that whole row to be formatted differently
(background, font,and bold).

How to this ? Excel 2013

Thanks
0
I have an excel workbook with many rows in a tab called data...  I would like to have another tab called terminated that would only show rows from the data tab where column K = terminated

how is this done with a formula or lookup etc...
0
Hello,
I have an Excel worksheet that I use to track when users are due to take one of their training courses. Currently I am manually inputting the date to expire, but what I would like to have is when I put the date in one cell it will output in another cell that it is due to expire a moth from the due date. Does this seem like something that can be done in Excel?
0
Hi All,

I have this spreadsheet (attached).

I have Mastersheet which contain all the location and the product quantities, now I have to split them in a separate sheet which will be taken by a counter person and key in numbers, now I want the some of A product to automatically calculated and put in the column counted_qty on the master sheet if it possible? can someone help, please.

I have asked this question before and an expert resolved it but when I copy it now it doesn't work. formula is below: (Ignore the last sheet which is MANUAL).

=IF(COUNTIF($C$2:C2,C2)=1,SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!"&"$C:$C"),$C2,INDIRECT("'"&SheetNames&"'!"&"$E:$E"))),"")

Thank you.

Regards,
StockTake28122017-WF.xlsx
0
Hello,

This post is a follow-up to two previous threads located here:

Extract certain items from one list to create another list in Excel (closed 2017-10-28)

Understanding a few parts of an array-entered formula in Excel (closed 2017-11-21)

Briefly, the solution to those threads consists of an Excel formula which selectively extracts certain items from a source list and displays the extracted items in a new list while maintaining the correct order of the extracted items. Following is the solution formula and a screenshot from the attached file:

=IFERROR(INDEX(B$1:B$13,AGGREGATE(15,6,ROW(C$4:C$13)/(C$4:C$13=F$2),ROWS($4:4))),"")

Open in new window

2017-12-10b.png
Now I'm wondering how to modify the above formula to allow for a date column in the source list (which may or may not be in order chronologically) and then display the results in a list which is ordered by date as shown here (eg Abigail was listed first in the previous result lists but is now listed last because her associated date is later than the others):

2017-12-10c.png
As shown in the next screenshot, the attached file contains solutions from the two previous threads (AutoFilter 1 and AGGREGATE() 1) and accompanying notes (which are not directly related to the current thread) as well as a new sheet tab, DisplayByDate from which the previous screenshot was taken.

2017-12-10d.png
Thanks

AutoFilter_array-formula-evaluation.xlsm
0
While trying to find out how VLOOKUP with conditional formatting.  I'm using a random picker that highlights the random name and would like to pull the highlighted name to another sheet/cell, etc.
While reading the post with the same Title I've done a work around for similar by formatting the destination with if not empty or what ever condition that will highlight the destination when in place.
As a rookie, I'm still trying to learn how to do the task above.
Thank you in advance,
D, Smith
0
I want to fill blank cells with above value cells, I am using excel 2016 and and i tried highlighting range of cells and went to GOTO>Special>Blanks and when I do that I am getting no cells were found.

How else can I fill Blank Cells With Value Above In Excel?
0
Become an Android App Developer
LVL 11
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Which is the best solution/setup for synchronizing Excel och Google Sheets, restrict viewing of specific cells and automatic sharing upon change in specific cells/columns/rows (and automatic e-mail notification)?

So far, I've found these possible solutions:

1. For synching (Sync Sheet): http://funbutlearn.com/2013/12/introducing-sync-sheet-sync-ms-excel.html
1. For synching (Sheetgo): https://www.sheetgo.com/import-automatically-data-excel/
1. For synching (G Suite): https://gsuite.google.com/intl/en/
2. For sharing portions of an Excel sheet: http://funbutlearn.com/2017/09/share-portion-sheet-google-spreadsheet.html
3. For automatic sharing and e-mail notification upon change: https://productforums.google.com/forum/#!topic/docs/QyMJDt2ld28

Then there is also Zapier, but I'm not sure where in above it fits in: https://zapier.com/zapbook/google-sheets/

Or would I really not need any other software, just to use MS Excel and Google Sheets?
0
Have a client who needs their internal group to be able to have multiple people in the same Excel file at the same time.   Currently these files are hosted on an 2011 SBS server.   Files are for a call center environment with multiple agents getting updates on accounts via the Excel Spreadsheets (Think Excel Client Dashboard)
Tried the eFolder / Anchor solution

PROS:

Synced Excel files in the cloud
Allowed multiple users to access the same excel file in the cloud

CONS:

Speed of opening online Excel File (Slow / Poor)
Excel Filtering Issue (If one user filtered the spreadsheet, it filtered the spreadsheet for all the other online users)

Need another solution that can keep local copies on the server, allow for multiple people to access the file at the same time, make changes to the file at the same time, and doesn't go into having to create an Access Database, SQL Database, or use Sharepoint if possible.

Thanks in advance for your help / suggestions.
0
Hello,

I do IT for a health screening company.  Basically a data review person will download a list of people who participated in a health screening event.  This file lists all the people that attended and their personal information.  This file name changes.

The second file is almost identical but is generated by our health screeners from our own system.  This file name also changes.

We do a comparison between these two files and I'm trying to create a macro that will work no matter what the file names are.  I'm not a programmer at all, macros are easy, but above that I just don't have that skill set.

So the two files would be open Book1.xls and Book2.xls
In both file we're working with Last Name, First Name, Unique ID columns, but there are many more columns present.

Book1.xls
Add column to right of Unique ID (say Column "J" for this example)
Concatenate Last Name, First Name, Unique ID Columns

Book2.xls
Add two columns to the right of Unique ID (say Columns "I" and "J" for this example)
In first column, Concatenate Last Name, First Name, Unique ID Columns (same as in Book1)
In second column, Vlookup on first cell to the left which is the concatenated value, then highlight Column "J" in Book1.xls, then add comma 1, then add false.

=VLOOKUP(E2,[Book2.xlsx]Sheet1!$J:$J,1,FALSE)

It's just comparing the columns for accuracy.
I can make macro work, but then of course the file names change and that's the end of the story.

Just trying to make …
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
I have two columns (A and B) on a sheet in Google Sheets (and my local copy) that when changes are made in any of these, an e-mail notification should automatically be sent to person A.

Then I have two other columns (Y and Z) on the same sheet that when changes are made in any of these, an e-mail notification should automatically be sent to person H.

Is this possible?
0
Hi,

I have an excel document that has number rows and columns and it is presented in a standardized format. Each cell has a very specific formula and tends to be either hard coded and wrong or missed and wrong.

I am curious if it possible to prevent users from adding and removing rows and columns and then add a dropdown button to add rows and another button to add columns

Each of these buttons would be placed at the end of a range.

Hopefully, these buttons would, when click, copy the formulas from the columns/rows within the set range and append a row/column at the end of the range
0
I'm running win xp,How do I Recover Excel Docs infected by (fEstasAzulCorrupta)? without deleting them.Very important spreadsheets.
0
Hi everyone,

I need help on a worksheet I am working on in Google Spreadsheets.

I attached the screenshot of the document I am working on.

2017-08-03_15h16_45.png
1, 2 and 3 have been imported from another Spreadsheet via the Import Range function.

In 4, I need to average 1, 2 and 3, but it's not working because the formula is not reading the value, but the import range formula.

Any help would be appreciated.

Thanks!
0
I have a database containing data for about 20 quality measures for a about 80 physicians.  I have a workbook built that lists the providers names, email addresses, clinic, etc.  I have pivoted the database to create spreadsheets for each provider for one measure.  (I will duplicate this for each measure).  What I can do is run a macro I've created to automatically email each physician their associated worksheet, based on the their email which I've placed on their individual worksheets.  This sends them a read only version of excel as an attachment to their email.  What I would like to do is have my macro embed a snapshot of the worksheet into the body of the email, instead of sending it as an attachment, but I'm not quite sure how to do that.  I'm not very excel savoy and am happy I have gotten this far.  Below is the macro I am currently using and I need to know how to edit this to get what I'm after.  I appreciate any help!  Thank you!


Sub Mail_Every_Worksheet()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    TempFilePath = Environ$("temp") & "\"

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2016
        FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    …
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

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.