How do I copy a worksheet without the link to the original workboo
I need to copy a worksheet that links to cells in another worksheet in the
workbook. This worksheet will be copied to 300 different excel files which
all have the same format but different data in the cells. Therefore, the
worksheet that is copied to the a specific workbook must link to the workbook
it is copied to and not the workbook it is copied from. How can this be done
with the least amount of effort? (Excel 2000)
I'd convert the formulas to text, copy|paste and convert the text back to
In the original worksheet
select the range to copy (the whole worksheet)
when i apply the MOD function in a number>100000000000 returns #NUM
Message posted from http://www.ExcelForum.com
this is one annoying problem of Excel's MOD implementation. It can't
handle large numbers
> when i apply the MOD function in a number>100000000000 returns #NUM!
> Message posted from http://www.ExcelForum.com/
Here's a good kb article:
XL: MOD() Function Returns #NUM! Error Value
Using Windows XP &...Copy down formula macro
I have this macro that doesn't work and I am not sure why:
LastRow = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row
Destination:=Sheets("Sheet1").Range("P3:Q" & LastRow)
Macro should check how many rows are in column D and then copy the formula
form P2:Q2 down to the last row.
In what way doesn't it work because it looks fine to me?
> I have this macro that...Excel 2000+XP copy/paste in new sheet drops 4 years from date
I have a customer who is using Excel XP. He is copy and
pasting a date with format 10/20/2003 from one sheet to
another. When he pastes onto the second sheet it drops 4
years off the date. I tried this on my system also, I am
running Excel 2000 and I get the same behavior. Is there
some kind of date setup that I need to look at. I am
thinking this may be somehow related to the 1900/1904 date
issue, am I correct?
One way to add those four years back is to find an empty cell, put 1462 into
Copy that cell.
Select your range that contains the dates. Edit|PasteSpecial|click ...Median If Function
I am working on a statistics report, and I need to find the median of a
percentage increase, decrease, and no change. Would these be the formulas I
need to use?
=MEDIAN(IF(C160:CI160,">0")) for increase
=MEDIAN(IF(C160:CI160,"<0")) for decrease
=MEDIAN(IF(C160:CI160,"0")) for no change
Thanks so much for your help!
etc., which is an array formula
(replace xxxx in the email address with gmail if mailing direct)
"allyrose79" <allyros...string function to wrap with designated delimiter
I need a string function that can wrap a string into an array of substrings.
The function should take a delimiter character and a max length parameter and
return the array of strings that minimizes the number of array elements, each of
which would terminate with that delimiter, except when the max length requires a
break in the middle of the string not at a delimiter, and allowing that the
final element might not terminate with a delimiter character.
For example given the delimiter '\', a max length of 30 and the string
'C:\Documents and Settings\Larry\My Documents\...conditional copy from sheet to sheet
Getting a NEW Sheet with data from one sheet with the CONDITION....
1 test 4 5 m
2 mladen 3 6 h
3 mladen 6 6 m
4 nikola 6 2 h
5 nikola 2 1 h
6 test 5 3 r
7 test 2 5 e
8 mladen 1 6 m
CONDITION = m
1 test 4 5 m
3 mladen 6 6 m
8 mladen 1 6 m
How can I do this?
mlradak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1495
View this thread: http://www.excelforum.com/s...Pasting a Word Table into Excel
I have a Word table that I need to paste into Excel. When I paste, the length
of the rows of the table are much larger than they were in the Word table. I
can double click on the row to make them the default size but I have to do
each one separately. Is there a way to make the rows go to the default size
all at once? I am using Excel 2002.
Thank you for any help.
If you hit ctrl-a (twice in xl2003), you select all the cells.
Now try double clicking on line between the row numbers on the row header (to
the far left).
Or just format|row|autofit
(with all the rows selec...Copying multiple rows from different spreadsheet based on a logic
Dear Friends, Need your help in the following :
Here is my requirement:
I have a worksheet which has columns 1,3,6,8. Which has few rows with a
unique values in column 1.
I get a spreadsheet everyday with columns 1,2,3,4,5,6,7,8,9,10.
I need to insert new rows with only columns 1,3,6,8.
I need a button using which I should be able to update. It would be
great if the macro gives me an option of selecting the file.
Thanks in advance.
You might try hiding the undesired columns>f5 visible cells only>copy/paste.
Or a macro.
email@example.com...custom function-code for percentage
I am new to VBA. However I have created a custom function using VBA and the
code is detailed below.
Public Function PLF(UnitsGenerated, PlantCapacity)
' This user defined function provides the PLF of the Plant in two decimals
PLF = (UnitsGenerated / PlantCapacity) * 8.76
PLF = Application.Round(PLF, 2)
The result of this custom function is shown in two digits. But I want a
percent symbol along with this result. Can anyone would suggest me the code
to be added for this for which I thank you in advance.
Format the cell as a percentage and change ...Totalling seperate cells from a HLOOKUP function
is it possible to sum the product of 2 or more cells, 1 cell being the result
a HLOOKUP function, without creating a third cell for individual results?
Here is my problem:
Row A contains a list of different locations
Row B contains wether the delivery has failed or completed
Row C contains the quantity of items on Invoice
Is there a formula I can use to have the total quantity from all of the
failed deliveries for that day?
As the failed delivery locations may vary from day to day, I cannot just
total those cells from each location
I have tried this formula, but it re...Problem with copying formula
I am entering the following formula into Cell L3....
When I copy this down to the cells below with the drag handle, the
formula is copied correctly (i.e =B5-E4, =B6-E5 etc..), but the result
is the same in every cell which is clearly wrong. I'm stumped.
jimbob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29107
View this thread: http://www.excelforum.com/showthread.php?threadid=488480
Set calculation to automatic under tools>calculation or p...Copy Email addresses to Outlook Express
I have a list of email addresses in an Excel column that I would like to
copy into Outlook Express. Is there a way to do this?
Backup your Windows Address Book before doing anything (*.WAB)
Same file (.WAB) is used by both Outlook Express and by Outlook.
You will need one extra column of data in your Excel file, because the WAB
when using Import of a CSV file requires an ending comma.
Export from your WAB so that you can get the format of the column headings then
you can import them back in with your new data or maybe just import the new
data in the corre...Copy Only Visible Cell
Is it posible to copy a block of cels with combined hidden collumn and
row to another sheet or cells without those hidden field to be copied ?
When i try to copy this kind of cells to another sheets/cells, excel
copy all cells including the hidden cells.
I just want to copy cels that is visible not including those hidden
cells, Is there a way around this ?
Thanks in Advance
Select the desired range.
Click Edit - Go To.
Click Special then select Visible Cells Only then OK.
Copy the selected range.
In article <firstname.lastname@example.org...pasting values from pivot table still linked
I have a fairly big table that provides values for a pivot table. I would
like to view the data from the pivot table and copy and paste this
information to a new tab as values, and retain the formatting. The goal is
to keep the spreadsheet from freezing - which it seems to do too much. I
understand that pivot tables share cached memory.
This copy, paste special, values and paste special "all using source theme"
method seemed to be working. However, I noticed tha the sheet was freezing
more and more and today, I noticed that when I updated the pivot table for
t...WNet... functions in windows 98?
My program that uses WNet... functions (WNetOpenEnum,
WNetEnumResource...) works only under windows 2000 and windows XP. Is
there a way to use these functions with win 98 and if not, than how else
can I enumerate network resources? Tnx in advance.
...Supplier Reorder Number in Item Properties blocks paste
Why would the field for supplier reorder number not accept a paste function?
Do you guy realize that many supplier item numbers are TEN DIGITS LONG????
So, I am supposed to enter 5000 items and all their respective item numbers
from my distributors and HAND KEY those numbers in?
Actually it does work, its just kinda stupid. Enter a single digit, erase
it, paste it. It may or may not display, but just click on OK.
"Mickie" <Mickie@discussions.microsoft.com> wrote in message
Why would ...copy a set of names(constants) from 1 workbook to another
I tried to figure out how to do this but I'm stumped!
I have a worksheeet where I have defined a set of names and values like
I do this by using "DefineName" and directly typing the name and the value
it should stand for. Essentially these are constants and don't change. I just
needed a convinient way to refer to them.
Now I am starting a new workbook where I'd like to be able to refer to these
values. Whats a neat way to do this? If possible I wanted to have them
redifined as constants and not as cell refernces(just as I had ...Excel Today() function
I need to create a formula that looks at a reference cell then returns
Today() if the cell is between given values. If the the cell is not between
given values then the date in the output cell doesn't change, is this
You can't do what you want with a formula. If you put a formula in a cell,
that previous value is gone.
(remove nothere from the email address if mailing direct)
"joel" <email@example.com> wrote in message
> I need to create ...how to replace funtion call in MFC app with my function code:- tzset() ->my_tzset()
I wanted to change tzset() & isindst() functionality, for this i
modified the vc src file crt/src/tzset.c and linked with my
application, chnages were made with cansole application without mfc,
but with mdi mfc application, orignal code was used for localtime &
other time function instead of modified tzfuntions.
Then I decided to replace original function by traping the function
call & calling modified function insted of orignal one but no success.
I would like to know which technique is used when someone modifies the
orignal vc code.
I know that if you "paste special" an Excel worksheet object into a Wor
doc, the pasted sheet will mirror the data that is input in the sourc
document - however, I was wondering if anyone knows if it is possibl
to link such documents whereby you create formulas that link across th
2 embedded worksheets i.e. as if you were working in Excel itself an
where you can create formulas across multiple sheets - (I have hear
something about a radio link but dont know how this works
martins's Profile: http:...Including Country in RW SelectAddrLine Function
Is it possible to include the Country field in the Report Writer
rw_SelectAddrLine function? We want the Country to be the last line but
still include two address lines. If so, what's the proper format?
Replace the last set of double quotes in the function with the country field
from the appropriate table. Do this for all of the calculated fields. You
will probably have to create a Line 4 (or 5) calculated field for the
address. Look closely at the existing fields and replicate the function
remembering to increment the line number in the parameter list.
"El...Why Access 2007 donot have the "Data Access Page" Function ? How I can do with 2007 for this issue ?
I notice that Access 2007 doNOT have the "Data Access Page" functionality.
1. Why ??? I think this function is important.
2. How I can make the web page efficiently if 2007 do Not have this
3. If I developed the "Data Access Pages" with Access 2003, how I can handle
these already existing pages when I use them in 2007 ?
Thank you for your prompt answer.
The Data Access Pages functionality has been deprecated in Office 2007; so
if you want to continue to develop your DAPs, you need to use Access 2003
instead of Access 2007. M...Adding Rows and copying cells
I'm a new Excel user. I'm trying to insert a row and copy a cell to the cell
directly below it. This happens at various locations through the sheet so I
need it to Paste directly below labeled cell.
CALC is the labeled Column and INROWB1 is the labeled Row
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
There has obvously got to be an better way than this... any ideas.
> There has obvously got to be an better...Excel Sum with Filter & Copy specific Data
Hello, I have two questions regarding Microsoft excel. If you could
help me I'd really appreciate it!
I have 2 worksheets. One has a list of ID numbers (unique - one of
each) and a column for totals (which is blank as of now)
ID #: Total:
The other has a list of ID numbers (many listed multiple times), has a
number entry which I need to be summed and a date (which I filter by
to get a range)
A B C
1 345 Jan 1
2 327 Feb...