Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Copying data into a different sheet Results

Hi Guys,

I am new to this forum and I hope I had posted in the correct section. I appreciate if someone would render me some help regarding my excel application. I need help in 2 areas:

1) I had an Excel Sheet 1 with cell A1 containing 4 number eg. 1234. And In sheet 2 in the same workbook I have a column of 23 datas (they are 4 numbers data (5678,4532,...) in each cell from A1,A2,A3 till A23).

- I need an application to compare sheet1 cell A1 data (1234) with the data in sheet 2. If 1234 or 1234 permutation (eg 3421) is found, display the result (3421) in sheet 1 cell B1.
-If no permutation of 1234 is found, search the 23 datas again for results matching any 3digits (for eg if the list contains 2435 (3 digit the same,1 different then this should also be displayed in the next available row, in cell C1 of sheet 1 and so on)) ( eg if lists have 9423 then 9423 should also be displayed on the next available cell, D1,..)

2) Secondly, I would need help to copy some external data from websites eg www.abc.com/3011. I would need to create a textbox (for user to type the web address) and 2 buttons (1 for copying the data in the website listed on the chatbox and another to copy the next data) The web address (eg www.abc.com/3011) is to be keyed into the text box and when i click on button 1, the data from the web www.abc.com/3011 will be copied into the excel sheet and then when i click on the 2nd button, www.abc.com/3012 (previously is /3011 and when i click on button 2 will copy /3012 and click again will copy /3013, always incremental of 1 when i click button 2)will be copied into my excel program.

Any kind soul please help. Thank you for reading!

I am a psychologist and I am creating a spread sheet which takes datas from two different sets about 20 cells each. What I want to do is copy those cells that have text entered into them and use the edit - copy function to paste that data into another program but I do not want to include all the blank cells that are between the two sets which is always changing depending on the situation.

example:

mbr was not home
mbr is si
mbr is depressed
mbr is unable to contract for saftey

(Here I will have about 10-15 empty cells then it continues )

oca called police to assist mbr
oca notified officer jones
oca authed IPMH for 3 days

Am I being to ambitious?
Thanks
Jim

Hi, wasn't sure whether to post here or the Programming section as I'm fairly new to Excel.

I have a spreadsheet (Master Risk Diary) which I have started to create. As I enter information in the spreadsheet I would like this information to copy to another workbook (Rick Risk Diary) multiple times depending on certain criteria.

For example if there is a entry in Column A called Rick then information on that row needs to be copied to a different workbook depending on the name located at C:dataRisk DiaryRick Risk Diary (and if Ian it goes to Ian Risk Diary etc) all Workbooks are located in the same folder (Risk Diary).

but I also need the information to go to a specific worksheet within the Rick Risk Diary Workbook.

For example whatever date is in cells B4 to H4 all that row's data needs to be copied to each worksheets month.

So to clarify if in Column A it says Rick and in B4 it says 01/01/2006, C4 says 02/06/2006, D4 says 04/08/2006 and E4 says 05/11/2006 etc then that entire rows information get's copied to Rick's Risk Diary and then copied to sheets Jan, June, August and November (or whatever month it says).

If it says Ian it goes to a Wb called Ian Risk Diary etc...

I hope the above makes sense I'm inputing all the data into one big spreadheet and would like it to auto copy to diff sheets in diff workbooks (unless there's a better way ).

I have copied between worksheets before but never between workbooks depending on criteria and I'm just tieing myself up in knots at the moment.

If anyone can help or point me in the right direction that'd be great.

Thanks

Kevin

I have sheet in which i have some customer details for telecalling for different telecallers. Now i want send these customer details as per telecaller .
for example i allocated 2 customers (63, 16) to Smith for telecalling. i want these customer details copy into new sheet and send new sheet to smith as attachment. i want this operation for ever telcaller. or if i want to send main sheet to all telecallers in a single mail.

sample data is attach.

Hello Everyone!

I've been tasked with assigning some data validation to a worksheet, and I need the following things to happen:Phone number cells need to accept only 10-digit numerical entries, and the boss wants the cell to turn red if an invalid number is entered.Email cells need to turn red if the entry doesn't end in ".com".
I can do all of that (I think), but the problem is that this sheet is designed specifically to have data copied into it. If the data is copied form another sheet, pasting special values will keep the conditional formatting/data validation in place, but unfortunately I can't count on end users to use paste special values

So, is there a way to "lock" the conditional formatting of a cell even if another cell with different formatting is copy/pasted into it?

Thanks!
RA

I'm using 64-bit Excel2010 and want to utlilize Powerpivot to facilitate aggregation of invoices reported in rows from different business units in my company. To date, it's been an exercise in manually setting up the columns and data in each of the speadsheets into a common format and then copy/pasting everything into a single spreadsheet in order to perform a pivot table analysis.(Yes, I have a sense that I'm probably doing things the long way.)

I also have a sense that I might be able to streamline the process with Powerpivot; however, Powerpivot relationships have this "many-to-one" requirement, which my sheets don't have. Rather than relate different fields like "order code" and "order type", I instead want to aggregate revenue data from different sheets; these sheets have some congruent fields, but also have non-overalpping fields and different layouts. I guess one way to look at it is that I'm trying to stack a bunch of boxes of diffferent sizes, but line up one edge of all the boxes so that I can measure the total height of the stack (i.e. height = total revenue). Am I trying to create "junction tables" here and therefore am unable to employ Powerpivot for this?

Thanks in advance for any reply, including links to related training or posts.

I am trying to complete an exercise that involves copying a table of data across from a pdf (I only have Adobe Reader). There are no "Special Paste" options so it pastes in multiple rows but a single column. To deal with this I use the "Text to Columns" button using spaces and commas as delimiters.

This all works fine, but then a strange formatting had been applied to the dates (which were originally in mm/dd format) such that the dates for the first 12 days of the month are displayed the wrong way round. Once it gets to the 13th and realises there is no 13th month, it displays the date correctly...but leaves the previous cells displayed incorrectly, eg:

04-Oct
04-Nov
04-Dec
Apr-13
Apr-14
Apr-15

After the text to column conversion has been completed, there seems to be no way of undoing this formatting, changing the formatting to general gives:

40820
40851
40881
41365
41730
42095

Which is nonsense as compared to the original data in the pdf:

4/10
4/11
4/12
4/13
4/14
4/15

This problem can be solved by changing the settings during the text to column conversion and selecting MDY as the date format, but this is where I ran into my main problem.

Deleting the old data, and copying the original data again from the pdf (even into a fresh sheet), Excel automatically performs the text to column conversion. I find this weird as it is obviously linking to the document and trying to help you based on how you formatted the data before...but I find it a bit disturbing privacy-wise. There is no indication that the formatting has been applied and it cannot be reversed. The irritating thing is that it appears to apply the default settings when it converts the data, meaning the dates are screwed up again.

The same thing happens when I paste data from the same document (but say a different month's figures) that I haven't pasted before. It doesn't matter whether I paste it as Unicode or Text (which are the only two options I have). The only way to make excel forget is to open up a new workbook for each month.

Any ideas what is going on?

I have a list of data that i need to pull some information from and summarize. The list has many rows of data, i have attached a portion of it (sheet 2). The summary sheet pulls different cost information from the list and sums it. There are 3 different costs i would need to sum, internal labor cost, parts cost, and external labor cost. Each unit has a worksheet that would show each total cost per month (sheet 1 in example). Any help with the formulas for this would be greatly appreciated. I am currently putting the list into a pivot table and manually copying the amounts for each unit into a sheet for each cost type that feeds the summary sheet(example is on sheet 3)

Thanks!!

I really need help. I am on a mac and I dont have excel. I helped someone do a project for me and it was wrong, but the project is due now. I have all the information needed now but I don't have excel to use it. I need to perform a t-test and make a bar graph.

Basically I have two columns: one is Frog RSIL and the other is Pig RSIL. The Frog has 4 quantities: 0.318, 0.45, 0.264, 0.89. The Pig has four quantities: 7.27, 10.95, 7.49, 9.08. I need someone to get on excel, take that information and perform t-tests from that for both, get the mean of each and make a bar graph out of the result.

Here's a copy paste of what my instructions were to someone else, though most of Step 1 can be skipped over because it's already been done. These are the full instructions if you need them.

-------------

Small intestine lengths of frog and pig 1-4: 1 - f: 35 mm p: 2180 mm, 2 - f: 45 mm p: 3613 mm, 3 - f: 29 mm p: 2510 mm, 4 - f: 125 mm p: 2770 mm

Snout to vent lengths of frog and pig 1-4: 1 - f: 110 mm p: 300 mm, 2 - f: 100 mm p: 330 mm, 3 - f: 110 mm p: 335 mm, 4 - f: 140 mm p: 305 mm

Relative small intestine length of frog: 1 - 0.318, 2 - 0.45, 3 - 0.264, 4 - 0.89

Relative small intestine length of pig: 1 - 7.27, 2 - 10.95, 3 - 7.49, 4 - 9.08

-

Data Analysis and Summary Statistics

Use Excel to generate summary statistics and perform statistical tests. Procedures based on Excel 2003. After entering the data into Excel, you generate 2 sets of summary statistics, one for each frog and pig relative small intestine length.

*Since frog RSIL is already calculated, start at step I. - A-H show you how to calculate it.

1. Open Microsoft Excel and Enable Statistics
A. Start -> Programs -> Microsoft Office Excel.
B. Tools -> Add-ins. Place a check mark ext to Analysis ToolPak then click OK. It may be necessary to install this feature first.

2. Data Entry and Summary Statistics
A. Select the cell A1. Type the words "Frog RSIL" in this cell. In B1 type "Pig RSIL".
B. Click on column A. Select Format -> Column -> Autofit Selection. Repeat for column B.
C. Enter class data.
D. Tools -> Data Analysis. From the list, choose Descriptive Statistics and select OK.
E. Define your data range for each set of statistics. Click the button to the right of the Input Range box. Now in spreadsheet, click on A2 (first frog measurement) and drag the mouse to the bottom of the Frog RSIL column. Click the button to the right of the Input Range box again.
F. Click the open circle to the left of the Output Range then click the button to the right of this box. Next, click on an empty cell in your spreadsheet away from your existing data. D1 is a good place to start. Click the button to the right of the Output Range box again.
O. To run the descriptive statistics, put a checkmark next to Summary Statistics and select OK.
H. Next click Format -> Column -> Autofit Selection. Your descriptive statistics for "Frog RSIL" (And later, J) should have the following data: Mean/Standard Error/Median/Mode/Standard Deviation/Sample Variance/Kurtosis/Skewness/Range/Minimum/Maximum/Sum/Count.
I. Repeat process seperately for remaining column, "Pig RSIL".

3. Data Analysis - t-Test: Comparing the Means of Two Samples

A. Tools -> Data Analysis -> t-Test: Two-Sample Assumin Unequal Variances. Click Ok.
B. Define two samples you want to compare. Click the button to the right of Variable 1 range. Now, in the spreadsheet, Click on cell A2 (the first frog measurement) and drag the mouse to the bottom of the Frog RSIL column. Click the button to the right of the Variable 1 Range box again. Click the button to the right of Variable 2 Range. Now, in the spreadsheet, click on cell B2 (the first pig measurement) and drag the mouse to the bottom of the Pig RSIL column. Clic the button to the right of Variable 2 Range box again.
C. Click the circle to the left of Output Range then click the button to the right of the Output Range box. Click in an appropriate empty cell of your spreadsheet for example A21. Click the button to the right of the Output Range box again. Click OK.
D. Format -> Column -> Autofit Selection. Your output should have data for both the frog and pig in Mean/Variance/Observations with only frog input for Hypothesized Mean Difference/df/t Stat/P(T<=t) one-tail/t Critical one-tail/P(T<=t) two-tail/t Critical two-tail (*reading from the example statistics shown on page).
E. Block and print the output for the t-test. *Email

This statistical output will be used to answer questions at the end of the exercise.

4. Data Analysis: Generating a Bar Graph

A. Scientists typically use graphs of some type to help visualize results. These graphs are referred to as 'Figures' in scientific papers. In this exercise we will display the mean and standard error of each of our analyses in two separate (1 and 2). These figures will include labels on the X and Y axes as well as summary statements regarding your results.
B. You will create a figure for the Frog RSIL versus Pig RSIL analysis. 2 columns: 'Frog RSIL' and 'Pig RSIL'. 2 rows: 'Mean' and 'S.E' (standard error).
C. Use summary statistics generated earlier to complete this table.
D. Click on Frog RSIL and drag down and to the right to include the mean for Pig RSIL.
E. Click Chart Wizard or Insert -> Chart. Set the Chart Type to Column and Chart sub-type to the upper left choice. Click next.
F. This will take you to Step 2 of the Chart Wizard. You are now able to preview your graph. After previewing, click next.
G. In Step 3, you will add a Title and axis labels. underneath the word 'Title', give your graph an appropriate title. Remember to site the corresponding p-value generated in your t-test. The X-axis should be labeled 'Diet' and the Y-axis 'Relative Small Intestine Length'.
H. Click on Gridlines and uncheck Value (Y) axis 'Major gridlines'. Click on Legend and uncheck 'Show Legend'. Click on next to go to step 4.
I. Click on the circle to the left of 'As new sheet'. name this 'Figure 1'. Now click Finish.
J. Click in the grey area of your chart. Format -> Selected Plot Area -> None for both Border and Area. Click OK.
K. Now you need o add the Standard Error brackets. Click in the 'bars' of your graph. Format -> Selected Data Series. Click on Y Error Bars. Under 'Display' click 'Both'. Next click the small circle to the left of Custom. Click the button to the right of the + box. Next, click the Sheet 1 tab at the bottom of the spreadsheet. Click on the Frog RSIL S.E., click the button to the right of the box again.
L. In the same window, click the button to the right of the - box. Click the Sheet 1 tab again and drag from Frog RSIL S.E. to Pig RSIL S.E. just as before. Click the button to the right of the - box again then click OK.
M. Print this figure. *Email

I am using Excel 2003, and here is the problem. I have an Excel sheet that
contains financials. Different departments need different sections of the
data, so I copy and paste out only those sections they need and paste it into
a new Excel sheet. The colums are broken up into dates, and formated to show
date-year (Dec-05). For some reason, when I copy and paste sections, the date
will change, for example Dec-05 will change to Dec-01. I have to go thru and
manually correct all the dates. Can anyone tell me what is causing this?

The original question is at the bottom, but I was still struggling with it so
this is more information in the hopes that someone else can help me. Thanks!

I'm having trouble with the formula....

If my worksheets are:

Apr. 3
May 5
March 13

and I want cell B14 from every worksheet to all appear in a new worksheet.
What would my formula look like???

Thanks for your time and assistance!!

"Gord Dibben" wrote:

> Tia
>
> You refer to "tables" in a workbook.
>
> Do you mean "worksheets" in a workbook?
>
> If the latter, how are they named?
>
> If Sheet1, Sheet2 etc. enter this formula in A1 of a sheet.
>
> =INDIRECT("Sheet" & (ROW() & "!B5")
>
> Drag/copy down column B.
>
> If sheets have unique names, enter the sheet names in a column and use this
> formula.
>
> =INDIRECT(A1 & "!B5")
>
> Assume sheet names were in A1:A10 you would copy down 10 cells.
>
> Your ranges may differ, so adjust to suit.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 2 Jun 2005 15:25:38 -0700, Tia <Tia@discussions.microsoft.com> wrote:
>
> >If I have a workbook that has multiple tables, is it possible to select one
> >cell and copy that same cell from all the tables and paste into either a new
> >table or a new workbook???
> >
> >
> >I want the data from a specific cell (i.e.B5) from each table copied into
> >one table. Is there a way to do this???
> >
> >Thanks for any help!
>
>

Expand AllCollapse All

I have on one sheet of my workbook a table of some 1000 spelling words.
The words are divided into arbitrary categories (tests, if you will).

I would like to extract a portion of those words into a table in another
sheet.

The "test #" will be a variable (duh!) and the number of entries have that
"test #" will differ for different "tests."

Thanks, in advance.

Okies,

Thanks for that, BUT I put that rather badly about the original post of copy/paste. I already know how to do that.

The sheets I am copying from and then pasting to...it's a matter of the original sheet (called Worksheet) has data that the user enters that I need to copy to the new area down the column. The second sheet (called productivity) has a formula ie =Worksheet!C156 so that when anything is entered into Worksheet!C156 that it automatically enters it into the relevant cell in the Productivity sheet.

The problem I face is that I have to either manually type the formulas into 6 different columns in over 400 rows long, in the Productivity sheet, what I'm after is a faster way of entering the formula when the original data is ACROSS the cells in the Worksheets.

Can this be done?

Hi, I'am interested in creating Macros for some job tasks of mine. I went
through the Excel course of "Working with Loops" in Macros and have attained
some basic information on VBA. But since I'am not an expert in it, I want to
create Excel macros, using the Option " RECORD MACRO" in excel. My problem is
that, I want the MACRO to (a) Copy data from 2 files into a new file. (b)
Analyze this data with calculations (c) Build a graph.......basically the
summary sheet and graph in a new sheet of the new file. I want to know,
whether, can we do such a macro using "RECORD" feature? But I did try. I was
stuck when I had to open 2 different files........I want the MAcro to prompt
MssageBox as " Open the Last Month File" and.....then " Open the current
month file" and so on..............But this is somehow not possible without
VBA scripting........but I have learnt how to insert a Message Box, using
"MsgBox" command. But this box should have a space to browse and select the
files......which I'am not aware of how to do it........Can you kindly help me
out by suggesting me on the above, point by point ...of how I should be able
to do it? And also if possible suggest me the link in MS Ofice Online to a
course on Macros that will help me gain knowledge on the above ?

We have recently upgraded to 2007. We have a CSV file that we have to copy into a saved excel document. We used to right click on the CSV file's tab and say "copy" to "x" excel document and it would work fine. Now that we converted to 2007, we try this step and get an error message "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook".

Why would office 2007 be any different in this process? It is just opens room for error for us to have to copy and paste into the document.

Help?

Is there a quick way to duplicate sheet references without out changing cell location?

For example, I have Sheet 1, with 175 cells of data that is linked to a "summary sheet" using the =/enter option. These cells are not incremental, there are skipped cells on sheet one that is not linked.

OK so I have 14 other sheets with identical format and I need to put this identical "link" per sheet into the summary sheet. If I take the summary sheet with the first sheet manually linked up and try to drag/copy/paste type of duplication it moves the cell reference instead of sheet reference.

Is there any way to mass move/copy sheet reference only? (other than manualy =/enter method)

So row 1 references sheet one all the way across
row 2 references identical cells but from sheet 2 all the way across etc.

What I had tried to do was copy all of row one, into row two but it changes cell reference incrementally and there is no way I can see to just change sheet reference.

Thanks in advance. I hope I made my request/problem clear.

Maybe this is easier to explain,
ON the summary sheet I want each row to reference the same cells all the way across but from different sheets, so row 1 references cells sheet1 row 2 references same cells on sheet2

Hi,

I am creating a workbook that has different sheets for Q1, Q2, Q3, Q4 etc. What I would like to do is automatically copy any data that is input on these sheets into another tab that will hold all the cumulative data for the entire year - these are all in the same workbook and the column headings are the same in all of them

As data is input over the year it will all go on the Cumulative spreadsheet aswell.

I can do basic macros but nothing too complicated so a step process would be great!

I hope this makes sense!

this is very complicated, but I'm sure there's a simple way to do it. I have 7 identically layed-out sheets (tho each contains different numerical data), each of which feeds its data to a compilation sheet in the same workbook. each of the 7 "data" sheets has named cells, and the same cell on each sheet has the same name (albeit with the sheet name preceding it when referenced). I created one sheet as a master, and then copied it 7 times; excel automatically updated the cell names to reflect the sheet name. each set of same-name cells are then added together and the sum is fed into a cell on the complication sheet. if the sum is zero, the cell appears blank. here's the formula I use for one of the cells on the compilation sheet:

=(IF((IF('Trek 1'!sun_d="Headquarters", 'Trek 1'!trek_total, 0)+IF('Trek 2'!sun_d="Headquarters", 'Trek 2'!trek_total, 0)+IF('Trek 3'!sun_d="Headquarters", 'Trek 3'!trek_total, 0)+IF('Trek 4'!sun_d="Headquarters", 'Trek 4'!trek_total, 0)+IF('Trek 5'!sun_d="Headquarters", 'Trek 5'!trek_total, 0)+IF('Trek 6'!sun_d="Headquarters", 'Trek 6'!trek_total, 0)+IF('Trek 7'!sun_d="Headquarters", 'Trek 7'!trek_total, 0))=0,"",(IF('Trek 1'!sun_d="Headquarters", 'Trek 1'!trek_total, 0)+IF('Trek 2'!sun_d="Headquarters", 'Trek 2'!trek_total, 0)+IF('Trek 3'!sun_d="Headquarters", 'Trek 3'!trek_total, 0)+IF('Trek 4'!sun_d="Headquarters", 'Trek 4'!trek_total, 0)+IF('Trek 5'!sun_d="Headquarters", 'Trek 5'!trek_total, 0)+IF('Trek 6'!sun_d="Headquarters", 'Trek 6'!trek_total, 0)+IF('Trek 7'!sun_d="Headquarters", 'Trek 7'!trek_total, 0))))

it's important that the cell appear blank if the sum=zero. this formula works perfectly on the compilation sheet.

the problem is that I have over 150 of these "sum" cells on the compilation sheet, so editing this formula for each sum cell would take days. my feeling is that there must be a way to re-do the master data sheet and rename each named cell using a dynamic name instead of a static name.

basically, I'd like to find a way to use the same formula in each sum cell on the compilation but have the formula's cell position on the compilation sheet dictate the names of the named worksheet cells. I hope that makes sense!

Hello guru's! I've searched and searched, and I think I must be choosing poor search terms as I have been unable to find a solution to my current barrier. PLEASE excuse me if I do a poor job of using the correct Excel terms below...

I have a simple workbook with many different sheets. The first sheet is a summary of all of the following sheets. The remaining sheets are daily data entry sheets. My individual sheets are named: Summary, 1, 2, 3, etc.

In the summary sheet, I have formulas pulling information from each of the sheets for the purpose of making totals. For example: ='1'!H3 will pull the data from cell H3 in daily sheet 1.

I am trying to copy and paste these formulas to avoid having to rewrite a thousand formulas, but when I copy the formula above and paste it into the cell below it the result is: ='1'!H4. What I WANT the result to be is: ='2'!H3. In other words, when I paste the formula, I want it to pull the data from cell H3 in daily sheet 2.

I suspect the answer involves the indirect function, or the $, but I'm stumped as to how to get that result....

Any help would be GRATEFULLY appreciated.

Cheers

eman

Ok, here is the situation...any help is sincerely appreciated. I tried to search on this problem, but I had no luck. Sorry if this is a bit long. I just want to be thorough.

I have a spreadsheet of my product catalog from my supplier. The spreadsheet will be used to import my catalog into my website. There are 5340 items. There is a column for a unique reference number, part number, my price, retail price, weight, etc. Everything but the column with the web image file name. Call this spreadsheet 1.

For some reason, probably the fact that each customer gets different pricing and does not have access to all the products, the web image file name is in a seperate spreadsheet, very similar to the first one, with the key exception that my price is not contained on it.

So, I need to insert the column with the web image file name into spreadsheet 1 based on matching the reference number between sheets 1 & 2. In other words, copy the cell containing the image file name from reference number 1001 on spreadsheet 2, and paste it into a blank cell next to reference number 1001 on spreadsheet 1, and so on...yet ignoring the non-matching reference numbers.

Here's where it gets interesting: spreadsheet 2 has 54 more items than spreadsheet 1, and not right in a row, they are completely jumbled throughout. There are numbers on 1 that are not on 2 and vice versa. I need image files for the 5340 items on sheet 1, I can discard the extra 54 items.

Please let me know if there is anything I can do to clarify this further.

Thanks
Greg


No luck finding an answer? You could always try Google.