When copy from one worksheet to other formulas not copied, only values

create an additional sheet in worksheet at the bottom of worksheet is a +
add a sheet put each merchandise code in each row going down the column worksheet.
in the next row put the name beside the matching code.
then when finished sort on both columns using the merc, code to sort ascending.

notice in the next picture the addition of $ in the row
this is done to allow you to copy down the formula and leave the reference array the same

look at bottom of spread sheet to see additional sheet being used called code to create the array for the look up. With more array rows just modify the formula to say =lookup(a2,codes!$a$1:$b$200)
lookup will give you a text answer where as vloopup will give a numerical value !

Correct a #N/A error Show All Hide All This error occurs when a value is not available to a function or formula.

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions. Missing data, and #N/A or NA() has been entered in its place
   Replace #N/A with new data.
   Note You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.
   Giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function
   Make sure that the lookup_value argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) is the correct type of value — for example, a value or a cell reference, but not a range reference. Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table
   By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_lookup argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE. The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the match_type argument. To find an exact match, set the match_type argument to 0.
   Using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula
   If the array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) has been entered into multiple cells, make sure that the ranges referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range 15 rows high (C1:C15) and the formula refers to a range 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15).
   Omitting one or more required arguments from a built-in or custom worksheet function
   Enter all arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function.
   Using a custom worksheet function that is not available
   Make sure that the workbook that contains the worksheet function is open and the function is working properly.
   Running a macro that enters a function that returns #N/A
   Make sure that the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function are correct and in the correct position.
   

Are you talking about different worksheets in the same file.
go to the other worksheet then go to the particular cell and press = on keyboard
then use mouse to select the other worksheet from the bottom of the spreadsheet .
Then click on the cell you wish to link and press enter key on keyboard.

https://support.office.com/en-us/article/Insert-or-delete-a-worksheet-19d3d21e-a3b3-4e13-a422-d1f43f1faaf2
https://www.microsoftpressstore.com/articles/article.aspx?p=2447199&seqNum=2
https://www.microsoftpressstore.com/articles/article.aspx?p=2447199&seqNum=2

You can also do this to spreadsheets with different file names by opening both spread sheets in different windows and switching between the windows.

you can also copy just the data by copy whole sheet (click on the square in top left hand and copy and then right click paste special on new worksheet - with values only rather than the original formulas from the spreadsheet

Hide All
Percentages are calculated by using the following equation:
amount/total = percentage
Where percentage is in decimal format.
What do you want to do?

Calculate the amount if you know the total and percentage For example, if you purchase a computer for $800 and there is an 8.9% sales tax, how much do you have to pay for the sales tax? In this example, you want to find 8.9% of 800.
Example The example may be easier to understand if you copy it to a blank worksheet.

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic. Note Do not select the row or column headers.
   Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1 2 A B Purchase price Sales tax (in decimal form) 800 0.089 Formula Description (Result) =A2*B2 Multiplies 800 by 0.089 to find the amount of sales tax to pay ($71.20) Note To convert a number in percentage format to a decimal, divide it by 100. For example, the sales tax in this example (8.9) divided by 100 is .089.
Calculate the percentage if you know the total and amount For example, if you score 42 points correctly out of 50, what is the percentage of correct answers?
Example The example may be easier to understand if you copy it to a blank worksheet.

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic. Note Do not select the row or column headers.
   Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Simple way Amount/Total*100

You can change this under the Excel, options menu. Select the advanced option and uncheck the option to "Show Formulas in cells instead of their calculated value", or you can jjst press Ctrl + `
This will either turn on or off the show formula option.

You did not respond to my claification request. Here is some code that might help, it copies a selection of cells going down until an emty cell is found and across until an emty cell is found. This is form an earlier version of office but should comeclose to woring in 2007.

Sub copyrange()
'
' copyrange Macro
' Macro written by Royal 11/22/2008.
'


'save the return values
wksname = ActiveSheet.Name
returncell = ActiveCell.Address
searchfor = ActiveCell.Value

'go to first worksheet and find entered value (note this is a value serach)
Worksheets(1).Activate
findfor = "A1"
On Error Resume Next

findfor = Cells.Find(What:=searchfor, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Address
If findfor = "A1" Then
erwks = ActiveSheet.Name
Sheets(wksname).Activate
er = MsgBox("Search item not found on Worksheet" + erwks, , "Search Error")
Exit Sub
Else
findfor.Activate
End If

Cells.FindNext(After:=ActiveCell).Activate

'save this address and start searching for copy area boundaries
begcell = ActiveCell.Address
begcl = ActiveCell.Cells.Column
begri = ActiveCell.Cells.Row
'search amaximum of 1000 rows and 676 columns
endri = begri + 1000
endcl = 26 * 26
maxrow = 0
maxcol = 0

For col = Cells.Column To endcl
If Cells(begri, col) = "" Then
maxcol = col
col = endcl
ri = endri
Else
ri = begri
End If
For ri = ri To (Cells.Row + 1000)
If Cells(ri, col) = "" Then
If ri > maxrow Then
maxrow = ri
End If
ri = endri
End If
Next ri
Next col

maxrow = maxrow - 1
maxcol = maxcol - 1

'copy the selected area
endcell = Cells(maxrow, maxcol).Address
crnge = begcell & ":" & endcell
Range(crnge).Select
Selection.Copy
'go back and paste it in
Sheets(wksname).Activate
Range(returncell).Select
' use this if yo want to paste formulas etc.
'ActiveSheet.Paste
' use this code if you want to paste values instead of formulas etc.
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(returncell).Select

It could have a virus or simply too much data in it or too much data linked to it. Try doing a copy of the whole spreadsheet, and then paste the data into a new spreadsheet. If it doesn't contain too many different formulas, try pasting only the values, and then replace the formulas manually. You might also try just deleting the links, if there are any. If this doesn't solve it, reply to this thread and let us know.

Hope this will FixYa!!!

Hi, I am not really sure if I get your exct meaning, so would I be correct in saying that you wish to copy all the data and structure from DCT INFO across to DCT. If this is correct so long as the dollar data is formatted in the first worksheet to currency then when it is pasted it will maintain its formating into the next sheet.

One question though...Do you wish to change the data on the DCT INFO sheet and wish it to be automatically changed in the DCT sheet?

If yes then you will have to write the following formula into the DCT cells:- =DCT INFO!A1 (where A1 is the first cell of data in sheet DCT INFO that you wish to duplicate.) You can then use auto fill by selecting the cell you just typed the formula in to and then placing the cursor onto the small black square in the righthand bottom of the cell and clicking the left mouse button and holding and dragging down to whereever your data ends. For example A30. Repeat this with the column B.
Let me know if tis is not entirelt what you require and I will try to help further.
Regards
SeaJade

You can refer to cells that are on other worksheets by perpending the name of the worksheet followed by an exclamation point (!) to the cell reference. In the following example, the AVERAGE worksheet function calculates the average value for the range C1:C10 on the worksheet named Marketing in the same workbook.

Refers to the worksheet named Marketing Refers to the range of cells between C1 and C10, inclusively

1. Click the cell in which you want to enter the formula.
2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , type = (equal sign).
3. Click the tab for the worksheet to be referenced.
4. Select the cell or range of cells to be referenced.