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

One user cannot open a saved excel xlsx file. Other users can. And the user can open other excel files. Wet to backup files before problem, still cannot open, other users can. Error popup box says Could not open 'G:\2018 accounting worksheets\dailycheckbook 2018.xlsx'  and in blue small type WAS THIS INFORMATION HELPFUL?    Repaired office 2010, File security .  to recap, 1 user cannot open 1 file, the spreadsheet only has 8 lines and 10 columns and 1 link to last years ending total. Other people can open the file and it works correctly, the user can open other spreadsheets. All users are using the same version excel. 2010. The Backup copy and 2 days prior get the same message.  deleted the link, and the problem continued. Rebooted, same issue.
0
Hire Technology Freelancers with Gigs
LVL 11
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Hi

I'm trying to create a formula that will look at two columns and then add up the 3rd column if both criteria are met.

Screenshot of Excel sheet
I've used SUMIF before which has always worked but I'm unable to get this working.

What I would like to do is for Excel to look at Column E for CFT1000, then if Column G matches FPUP01 it then adds all values in Column J together.

Thanks
0
Hi

I need excel formula or VBA.

I need extract between "MeContext=" and  ","  result should be in B1=CLWRFLSRBBU04124

and same need to be extract between "EFDD=" and "," result should be in C1=CLWRFLSRBB434411

A1=Frame=r,SubNetwork=Tam,MeContext=CLWRFLSRBBU04124,ME=1,EF=1,EFDD=CLWRFLSRBB434411,ER=8565
 B1 = CLWRFLSRBBU04124
C1 = CLWRFLSRBB434411
0
See Attached sheet.

I have placed conditional formatting on a date column to change colors as the date gets closer to today.

However I would like for the BLANK cells to remain unformatted. So if there is nothing in the cell or it is blank then it should not have any formatting on it - it should be white with black text.
HoneyDoList.xlsx
0
I have tried unsuccessfully to create a nested IF/AND formula whereby if two conditions are met, then the work order is 'On Time', otherwise it is 'Late'.

Unit_Timeliness:
IF UDS![Unit]="DC", AND (UDS![Date_WO_Completed] > Schedule![DC_Cut_Off], THEN "Late" OR
IF UDS![Unit]="IFP", AND (UDS![Date_WO_Completed] > Schedule![IFP_Cut_Off], THEN "Late" OR
IF UDS![Unit]="PA", AND (UDS![Date_WO_Completed] > Schedule[PA_Cut_Off], THEN "Late"
ELSE "On Time"
If error, then leave cell BLANK

The following formula I came up with is not working properly, in that all the results are indicating that the work orders were completed 'Late', which is not the case:

=IF(AND(OR([@Unit]="DC",[@[Date_WO_Completed]]-Schedule.xlsx!AIM_Schedule[DC_Cut_Off]>"0",[@Unit]="PA",[@[Date_WO_Completed]]-Schedule.xlsx!AIM_Schedule[PA_Cut_Off]>"0",[@Unit]="IFP",([@[Date_WO_Completed]]-Schedule.xlsx!AIM_Schedule[IFP_Cut_Off]>"0"))),"Late","On Time")

Details of attached files:
UDS.xlsx
Column name: [Unit]
Contains one of the following 3 variables, which are acronyms for 3 different departments:
DC
PA
IFP

Column name: [Date_WO_Completed]
Contains date that work order was completed by the department

Schedule.xlsx
Column names:
[DC_Cut_Off]*
[IFP_Cut_Off]*
[PA_Cut_Off]*
*Contains 'Cut-off' date for each department against which the work order's completion date is measured against to determine if they delivered the work order 'On Time' or 'Late'.

Files as …
0
Hi,
I would like a formula to find max value and display adjacent cell contents
example
 Date                        Value
20170230        20
20170417        50
20180323        15
20180930        25

max value  50   display 20170417

Many thanks and wish you all a Happy New Year

Ian
0
Hi Experts,

Need to be find out Max value in D column based on Column C in sheet "Weekly" from the Data sheet.  I got correct value in D3, but not in H3 and also remaing weeks not getting data too (D4 to D5 & F4 to F5). Please help me.
0
Maintaining a legacy application written in VB6.

Several files are read in, modified, and written out. It is advisable for the user to preserve the original files, “just in case”. Currently, the user is required to manually copy each “Name.txt” to “Name_bak.txt”. Then, when the application is run, Name_bak.txt is read in, and the modified file is written out as Name.txt.

It would be convenient to automate this. I could read Name.txt in, immediately write it out as Name_bak.txt, then proceed with the modifications, creating a new Name.txt.

Question: Is there a way in VB6 to do this file archiving (renaming) directly? Some of the files are excel spreadsheets, and they are complicated to read in and write out, cell by cell.
0
Hi All,

I have this spreadsheet (sample 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.

Thank you.

Regards,

A
0
A customer is trying to copy and paste spreadsheets and other tables consisting of cells and columns inside of Microsoft Word, but a problem that we've run into is that the formatting is incorrect.  Some of the tables will clip off the page and it's unclear on how to resize them or the page to make them visible.  I've tried setting Word to paste without formatting by default but we'd like to know if there are resources online or other tips you can share.  

Is there a way to resize all the pages?  I don't think he's going to print them, but was wondering if there's a different way to format the tables to make them coherent in a standard letter format.  They're not wide to the point they would clip off the page.  Maybe scaling them would be appropriate but I'm not sure how to do that.


Thanks!
0
Get your problem seen by more experts
LVL 11
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

This happened for this user 6 months ago. Never did figure out the cause. It just started working again. The problem is back. The spreadsheets are on the server. Windows 10 Professional Workstation. Office 2013. Excel just locks up. He has four spreadsheets open.

Any suggestions? Where does Excel keep its temp files?
0
On the DWG tab I have part numbers on column A.  What I wanted is to match part numbers from the BOM tab and pull in cabinet data from column B and list those in the DWG tab under columns B:E.

See attached and example on the DWG Tab.
C--Users-lfreund-Desktop-EE-Index.xlsx
0
I need to see a few different ways to display the attached spreadsheet.

It contains a crude estimator of the cost for a merchant to accept credit cards, for various levels of monthly transactions and for three different transaction fee rates. I have a column for each of 3%, 4% and 5%.

Those are the costs for 36 months, hence the three year costs.

What kinds of ways can I display this information? I need it to be clear for a person that is not too "graph savvy." My hope is it can help them see what their cost may be, since they know roughly the amount of total charges each month, and likely have a rough idea of the actual transaction fee rate.

With those two data points, I want them to see the three year cost of those transaction fees.


ThreeYearCostsEstimates.xlsx
0
I am trying to modify an existing form found on the internet which allows the export of queries selected in a listbox into an existing Excel .xlsx spreadsheet. It utilized a popup form which asked for the file pathway. I need it to use a default pathway in a table...

On the form is a listbox, named lst_Export_Queries, with the rowsource as:
SELECT [MsysObjects].[Name], Mid([Name],5) AS QueryName FROM MsysObjects WHERE (((Left([Name],4))="qry_")) ORDER BY [MsysObjects].[Name];

Open in new window

multiselect = True,

and a bound textbox named "txt_Default Path", the field is named "Default_Pathway_File", and is based on t_Defaults (Defaults_ID,PK, and Default_Pathway_File" text)

I would like the code to use whatever is shown in the default file pathway as the area to export the spreadsheets into. This drive pathway exists as does the Excel Form.

The pathway to the Excel file to import into is: "C:\Excel DE to Import 2\Book1.xlsx"
Name of the Excel File is: Book1.xlsx

There is a command button named "Command)" and the code is listed below.

I keep getting a Run-time Error 3027 Cannot Update. Database or Object is Read Only.

When debugging this is highlighted:

DoCmd.TransferSpreadsheet transferType:=acExport, _
								  spreadsheetType:=acSpreadsheetTypeExcel9, _
								  tableName:=Me.lst_Export_Queries.ItemData(varItem), _
								  FileName:=strFile
	Next

Open in new window


And this is the on click event Command Button code:

Option Compare Database

Open in new window

0
I was given a 2013 excel spreadsheet with different kinds of equipment listed. I would like to create different work sheets in the same work book to separate the different types of equipment. I have three different types of equipment. Is there a way to select certain rows and move them to different worksheets without doing it manually?
0
I need some assistance with removing duplicate values from an OFFSET function/combo.

Please see attached XLS for details:

The dynamic dropDown (F1) is based on column B (which uses the OFFSET function below):
See Name Manager:  "=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)"

When entering new values (e.g., H) in column B, the combo in F1 will include the new value.

However, as of now, duplicates (e.g., "A") will be included in the drop-down list.

My question:  How can I modify the offset function/combo to **exclude** duplicate values?
Combo-Box----Automatically-Add-New-.xlsx
0
How can I create a dropdown list using images instead of text.  On the attachment on cells D5:D7 I have three symbols.  So I would like to create a dropdown with those images and selecting them to enter into a cell using something like data validation list....something like that.
C--Users-lfreund-Desktop-PRIORITY-L.xlsx
0
I have an old Access application that exports .xls spreadsheets.  A recent Windows update breaks this ability.  I have been told the offending updates are KB4041681 and KB4041678 for Win7 and KB4041676  for Win10 and they can be uninstalled to fix this problem.  I have a Win7 machine and I can't find these KB's in the update history.  How can I determine the problem update and uninstall it?
0
I am after some guidance on how to best finalise a mechanism to to update a master spreadsheet from a subset spreadsheet. Following is an overview of what I have done.

1. I have a master spreadsheet where orders are entered.
2.. With the help of an EE expert, I have worked out how to copy a small subset of the data to a second spreadsheet that can then be updated (date handled, photo URL, etc) using an AppSheet form.
3. I have used a series of Index/Match lookups to interrogate the subset spreadsheet and display the updated data on the master sheet

However, from time to time, I need to clean out the subset spreadsheet without losing the data. The only way I can think of is to write some VBA that will read the lookup values in the master sheet and paste them back as fixed text on the master sheet before I clean out the subset. I am quite capable of writing the code but wondered if there is a better way?

To make this work I need to save the subset spreadsheet in a completely different workbook in Google Drive (or another cloud synced folder) so that it can be edited by the AppSheet application, That means that there will be times when the master spreadsheet will try to READ data in the subset spreadsheet at much the same time as AppSheet tries to EDIT data in the same subset spreadsheet. Does anyone know if this will cause lock or save conflicts, or is it relatively safe because the lookup is only READing the data, not trying to EDIT it?
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.

I need to combine the datedif & If function in way in which if years = 0, then calculate months & Days, if year & month = 0 then calculate days if all is false then calculate years, months & days
0
Hello Experts,

I am looking for a VBA script to check column A_Material Name = column B_Manufacturer + column C_Model.

1.   If column A is NOT = column B + column C, please highlight these rows in yellow.
2.   If column A is  = column B + column C, please prompt a message " All data in column A_Material Name are cleared"
 
Attached is the sample.

Please help.  Thank You in Advance !
Sample-Mesa_October_26_2017.xlsx
0
Hi,
I would like help to build a formula to find max zero's in a column within a series (ID range)
Please refer to attached sheet
many thanks
Ian
Max_zeros.xlsx
0
Hi,

I currently have a macro in a spreadsheet that copies and pastes one worksheet into a new workbook on its own and the saves this as a macro enabled document (large version attached - I have deleted all contents). This is usually around 390kb in size. Please note: I do need it to be xlsm.

Today the macro was tested out on another machine and the small version (attached) was produced instead. All the data pulled through correctly and this would certainly be sufficient for what is required .... this version was on around 30kb!!

I'm trying to find out what is different between the two spreadsheets, but at the moment I can't find out what is.  When I do find out, I would love to add into the original macro the info that is needed to save the file as the smaller version, rather than the larger version. At the moment the part of the macro i have that saves this is:

        Application.CutCopyMode = False
        strFileName = .Sheets(1).Range("FO9").Value

        .SaveAs wbA.Path & Application.PathSeparator & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
        .Close SaveChanges:=False
    End With

    wbA.Sheets("NCS - Copy Values Skip Blanks").Visible = False

Open in new window


Any help to figure out why one filesize is so different would be really appreciated!
Version--Large-22-11-2017--Data-Cap.xlsm
Version--Small-22-11-2017--Data-Cap.xlsm
0
Hello Sir,
I'm trying to write a simple formula to check whether either 1 or 2 table are blank and, if so, leave the resulting to display the value of non-blank table.
I want the resulting table to only display a value if all or either 1 table are filled in.

Capture.JPG
These are formula I tried.
=IFERROR(C5,OR(H5,AND(M5))) if c blank then shows blank
=IFERROR(C6,AND(H6,OR(M6))) if c blank then shows blank
=IFERROR(M7,OR(H7,OR(C7))) if m blank then shows blank
=IFERROR((C5,H5,M5),IFERROR((H5,M5),IFERROR((C5,M5),(C5))))

Capture2.JPG
Plz help..
0
is there any free tool to audit spreadsheets?
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.