Go Premium for a chance to win a PS4. 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

in regards to this question
https://www.experts-exchange.com/questions/29067900/Excel-formula-referencing-multiple-columns-between-tables.html
obviously i have more data then i included in the example, as sheet1 is 90k plus enterys.
and i have a couple other large sheets referenced that are not directly part of the problem
so the information i am trying to retrieve exists on column 3 (C) of sheet 4, i have modified the formulae as follows
{=IFERROR(INDEX(Sheet4!A:O,MATCH(1,(Sheet4!A:A=B8)*(Sheet4!B:B=C8),0),3),"")}

Open in new window

and entered with ctrl + Shift + enter and i am getting no data.
should it matter that sheets 1-4 are from sql data connections? or is there something obvious im doing wrong
0
Important Lessons on Recovering from Petya
LVL 11
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.

I'm trying to enhance a calendar developed by some experts.

This is what I have so far. The Excel generates calendars based on value selected in A1.

SnapShot.png
But I got one issue, pls check formula of J18, the formula returns 3 for total working days, which exclude the Public Holidays and Off Days.

My problem comes in if I accidentally entered 25 Dec 17 into the worksheet: Off Days. Then the formula in J18 returns as 2, which is incorrect (because 25 Dec 17 was double-counted).

Can anyone advise what need to adjust?

Thank you.
Costs.xlsx
0
I have 100 or so identical spreadsheets in a directory. Identical layout but different entries in each spreadsheet. I need to format a number of cells to "shrink to fit." Is there a way to batch edit that many spreadsheets?

In the alternative, I created a macro to do a single spreadsheet. How do I save it in the directory so that anyone bringing up a spreadsheet in that directory can run that macro?
0
Hi all,

Grateful for advice on creating a formula in excel.

I am attaching worksheet for ease of use.

I have two sets of data in two different tabs.

I'm trying to do  a a comparison of amounts based on column A, i.e. check column A in both tabs and if the you find the same number compare the corresponding amounts (total them if need be) and see if they are different. If they are, give me the difference.

I dont mind if this is done in stages.

Thanks
Test.xls
0
hi experts, i would like to make a modification to the attached workbook.
The criteria are listed under the data in the worksheet of the attached workbook.
protect---ifstatement-4.xlsx
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
I have an excel pivot table that has subtotals
Subtotal is at the top

Label                   Count of upd_Apps   Sum of upd_Apps
upd_Apps             5                                       499
App1                      1                                        53
App2                      1                                        14
App3                      1                                        80
App4                      1                                       297
App5                      1                                         55
Label                   Count of upd_Apps1   Sum of upd_Apps1
upd_Apps_1                          3                                 565
WINAPP1                          1                                 45
WINAPP10                         1                                131
WINAPP11                          1                                389

Is there a way to hide the detail e.g. App1 or WINAPP1, and display just the subtotal lines?
If not, I will use sumif and countif functions instead of the pivot table.

Thanks
0
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
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

We are currently working with two Excel spreadsheets attempting to compare data from both spreadsheets in the “SerialNumber" field.  The information that we have in our first spreadsheet named “Scripts” is as follows:

Scripts

Record      Type      SerialNumber       
1      Monitor      G3D7F65D123L
2      Monitor      G4E5R22E432L

The information that we have in our 2nd spreadsheet called “Master” is as follows:

Master

Record      Serial Number      [indent]
Type       
       1      CN-0G3D7F-74261-65D-123L      Flat Panel Monitor
       2      CN-0G3D7F-76543-66O-0RBB      Flat Panel Monitor
                        

We would like to run a formula to change the Serial Number column data from the “Master”spreadsheet to have exactly the same data as the “SerialNumber” column from the “Scripts” spreadsheet, if there’s a matching number.  For example, if we remove the characters “CN-0-74261-” from record #1 in “Master”, we end up with the same data (G3D7F65D123L) as we have in “Scripts”.  Then we would like to perform a duplicate search and tag all the duplicates that appear in both spreadsheets and mark it as "Verified" in the “Master” spreadsheet.  We would also like to run a Macro to perform the duplicate search.

Can you assist?  We do need this as soon as possible.

Thank you.
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
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
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
Hi,
The attached spreadsheet lists contacts and the tags that apply to each contact.
However, I need to create a list of tags with the contacts who have that specific tag applied.
I used Pivot Tables to list each tag in its own column.
Now I need to signify which contacts have each tag by applying a YES or NO value to each cell depending on whether or not that cell contains the tag in the header column.
I tried using this formula:
=IF(ISNUMBER(SEARCH('Tags as Columns'!B3,B1)), "Yes", "No")
But it applies a NO value to all cells.
Please let me know what you recommend.
Thanks,
Josh
ForwardJump-Infusionsoft-0l357-tax-.xlsx
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
I found this app, which seems to do exactly what I need, but it seems much to pay 20 USD just for this feature:

https://zapier.com/zapbook/zaps/8764/save-google-sheets-rows-to-a-text-file-in-google-drive/

Isn't there any free way of doing this?

I will have a chat in a sheet in Google Spreadsheet, and after each chat I need to have this chat saved into a textfile and the chat on the sheet in Google Spreadsheet cleared after saved to the textfile. Instead of a lot of small textfiles, the best would be to have the chats for one year for example all saved to the same textfile.
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
[Webinar] Cloud Security
LVL 11
[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

User is receiving an error message "Microsoft Excel is waiting for another application to complete and OLE action.
The user was in an imbedded spread sheet in a work doc or trying to embed a spreadsheet in Word. They exit Word, the error message appears as a delayed action.  And it keeps popping up even though I do not have any excel spreadsheets open. Sometimes they can't get rid of it unless they reboot. This situation happens randomly. It does not happen all the time.

Anyone familiar with this error? Have a fix?

DLH
0
In a new SUMMARY worksheet, I want to be able to copy the names of every worksheet and specific cells from all other worksheets to it.

In the Summary worksheet, I want to enter the FROM and TO cell ranges to get from the SOURCE worksheets, and press a button or command eg. CTRL+SHIFT+R

So in the SUMMARY worksheet, I have:

Worksheet1Name
      Worksheet1Content

Worksheet2Name
      Worksheet2Content

Worksheet3Name
      Worksheet3Content

etc.
0
Hey EE, I'm currently a little over my head in my a project for my work, what I'm trying to do is make an excel spreadsheet where after inputting how many of each product we need to make to reduce excess prep and wastage, for example 200 Ham Salad Sandwiches, the spreadsheet would in turn say how much of each ingredient would need to be prepped, each of our products have a set portion based off grams, then work out how much of that ingredient would fit into our Prep Tubs and how many tubs we would require each day, I've only recently started using Excel after about 10 years so my knowledge is spotty at best, any assistance would be appreciated.
0
I need to figure out how to do check templates with Microsoft excel 2017 spreadsheets  for work can u help me ?Or quicken books  premium or accountants can u help me? I need to edit it he just told me can anyone help me ?
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

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.