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

Hi

We use excel 2013  and Please see the attached spread sheet and within this is spreadsheet we have sheet 5 and sheet 6.
On sheet 5 there are 800 users with SURNAME and FORENAMES all the coloured colum have data and I have removed it.

On sheet 6 there is there are 750 users and i would like to copy all the corresponding data( as per their Surname and Forenames) from sheet 6 to 5
Please post me if there is any formula to do or do i need to manually copy the correponding user data from sheet 6 to 5.

Thanks  and any help will be great
Data.xlsx
0
[Webinar] Database Backup and Recovery
LVL 11
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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
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
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
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 two Excel spreadsheets that are basically identical to each other except that one is a more recent revision that the other.

What software/methods can I use to determine exactly what the differences are between the two Excel 2016 spreadsheets?
0
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
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
Free Tool: Site Down Detector
LVL 11
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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
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 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
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

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.