Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other
data. I want to save the workbook as another name with all the worksheets
keeping the values only (no links or formulas). Is there a quick way to do
this for everysheet without having to special paste every sheet in the
workbook. So can I save everysheets data values at workbook level.
See this page for a code example
Regards Ron de Bruin
"lex63" <email@example.com> wrote in message news:ED708...Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus,
First of all, let me thank you in advance. I find it exemplary that
you all can devote time to helping others who are having issues with
their work. Hopefully one day I can be at a mentor level, and help
Hope you can help! I have an issue where I don't know how to write the
formula that I need without going over on the nesting. The current
formula that I have is as follows:
Data",IF(V7="Yes",&qu...How to link with an Access DB
Hi, I have an Acces DB with many tables.
I need to choose the name of a customer in a cell of Excel.
For example, in acces I have this tables:
I need to choose the last name from a drop down menu in a spreadsheet and
then in other cell I need to put all the data regarding the last name that
I hope to be exaustive, and sorry for my english. :-)
...How do I set up a daily average of unit sales formula
More info required.
(remove nothere from the email address if mailing direct)
"jim m" <jim firstname.lastname@example.org> wrote in message
...Worksheet Auto update
I need to find a way to automate a process.
Is there a way to automatically replace the content of a worksheet with the
content of another one?
Every morning I get a sales report in excel for the previous day sales. I
save it in a folder and then I do a pivot table on this sheet to determine
sales by product category for example.
The following day, I open the previous day file, replace the sales report
with the new one and then refresh my pivot table.
Is there a way to have my sales report update anytime I get a new sales
To be more clear
I have a workbook with two tabs: Pivot...Table link documentation
I am having trouble trying to locate A/P check data that has project related
costs. I found the check data but it is does not indicate the projects, I
found the project data but can not determine thye logical link between the
two tables, I may be using the wrong tables the tables I am using are PM80500
and PA31102. Is there any documentation of how all the tables in the system
are logically link. I am trying to write reports in MS Access, but there
are 1500+ tables in GP (version 10)
In an effort to find the correct table you can do a number of things (believe
me I do)....How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the
current form field. This bit of code is attached to the GotFocus
event of all fields and the Enter event of all subforms:
gxCurrentField = Me.ActiveControl.Name
However it doesn't work properly when changing records in a subform.
My parent form contains two subforms in a many-to-many relationship.
The above variable usually ends up containing the name of the first
field in the second subform when switching records in the first
How to correctly code this? Or is there some native variable I'm not
aware of? I...Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this:
is there a way to adjust the formula so that an empty cell in G21 doesn't
give the #VALUE! in subsequent cells in column H?
Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for
any and all empty cells in A2 to A9. It no longer matters if any of the
cells are empty, the formula correctly gives the correct addition of A1
plust a sum of everything between A2 to A10 without any #VALUE! results.
Was hoping to have the formula above als...Outlook 2003
In Outlook 2003,
#1 Is there a way to refresh the unread folder so that read
messages no longer apear? Right now I have to click closed the unread
folder and click it again
#2 Is there a way to create a toolbar button that goes directly to
...Help With Margin Formula
I need help with a margin forumla (calculated from retail). Say I have a
cost of $10.00, and I need the formula to calculate a 40% margin from
retail. So the retail should end up at $16.67. Not sure how to get from
$10.00 to $16.66, I just know the cost and the margin I need to make.
"JR" <email@example.com> wrote in message
> I need help with a margin forumla (calculated from retail). Say I have a
> cost of $10.00, and I need the formul...Creating a chart based on the data in an embedded worksheet
I have a worksheet with several embedded worksheets. I would like to
create a chart based on the data of one of the embedded worksheets
without putting the chart in the embedded worksheet. I have tried
unsuccessfully to do this. I just wondered if anyone knew how to do
You're embedding worksheets within worksheets? Why? Why not just insert
the worksheets in line with the main worksheet? To open or edit the
embedded worksheet, the parent Excel has to open another instance of
Excel, and the chart on the outside of this other instance will never be
able to acce...Opening Multiple Web Links in a Column
I am very new to using web links in excel. A task I do very often is
open a list of different websites that are in various columns in an
excel spreadsheet. I am quite sure I am doing it the slowest way
possible, so I need some help please. Basically I have been clicking
on one link at a time. When I do this, the first website opens and
excel automatically minimizes, then I have to go re maximize excel and
then click the next web link and the same thing happens, etc... very
time consuming. I am wondering if there is a way, either through Excel
or whatever means necessary, to open all...Question for Bob Phillips re Splitting Names from Cells
You gave the answers below for splitting names from cells:
Using these formulas on this example John A Doe results in John A an
Doe, is it possible to split it to show John / A / Doe in 3 separat
cells, I know I could use the formulas again on the John A result t
split them but I'd like to do it in 1 go
If possible could...Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values
in a range.
Example: Column A may have five cells that are "4", five cells that are
"7", five cells that are "9". Of the fifteen cells that contain data, there
are only 3 different values. I'd like to use a formula that will count the
number of different values in column A, in this case the result is "3".
=SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...Formula to display nearest following Thursday in mm/dd/yyyy format
I have been reading and trying different suggestions here to no avail. What
I need is a formula to calculate the nearest following thursday, and display
it in mm/dd/yyyy format.
To be clear, I have a column of varying dates. I need a formula to return
the next thursday for each of those dates.
To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in
cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010,
05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday.
Thank you for your help!
T...Find tab in worksheet
I have a workbook with many tabs & many users and would like to create a 'Go
to / find' function that finds a particular tab when opening workbook, so
that user will enter tab in text box and will then go directly to tab
Try any one of these macros..
You can try out the below macro. If you are new to macros..
--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected mac...Chart Linking
How can a cell in one workbook be linked to data in another workbook.
try the following:
- open both workbooks
- in the target workbook enter the equatrion sign '='
- now select with your mouse the source cell in the other workbook and
"Kenneth" <firstname.lastname@example.org> schrieb im Newsbeitrag
> How can a cell in one workbook be linked to data in another workbook.
Or copy the source cell, select the target cell, choose Paste Special
from the Ed...Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro
puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840
View this thread: http://www.excelforum.com/showthread.php?threadid=38572
Sure is! Use a named range as described here:
"punee...Seperate jobs for multiple worksheets
Separate jobs are sent to printers when I select multiple worksheets... If I
print to PDF, multiple files are created. How can I avoid this?
...Change Domain Name on outgoing Emails
Our company just purchased another company with their own Exchange Server and
AD infrastrure. We want all users in this new facility to have Email
addresses with our Domain such as email@example.com instead of their current
Domain firstname.lastname@example.org. Until I migrate resources from their Forest into
our Forest I have created contacts to forward all Emails from the abc Domain
to the 123 Domain. When users reply or send Emails from the 123 Domain it
still has their email@example.com Email address which will cause confusion
with our customers and suppliers. How do I force their Emails to us...Find Highest Score In List Formula
I'm looking to return the highest score for a users with multiple
scores in a list of other users with multiple scores.
Say the data is like:
and we want the max score for frank:
=MAX(IF(A1:A36="frank",B1:B36,""...Worksheet Protection #5
I built a spreadsheet for my brother, e-mailed it to him and 2 of th
worksheets in the workbook do not have the protection I put on them.
When I attempt to enter data on one of the protected sheets, I canno
enter data whereas my brother can. I am using Excel 2002 and believ
my brother is using 2000 or 2002.
Also, I have one worksheet (same workbook as before) that has levels o
row and column grouping. Is there a way to have grouping on
worksheet and then protect part of the worksheet and still have the +
(grouping) option work.
Thanks for the assistance and Happy New Year to al...Simple Formula
I have a formula, bt4/37 (bt4 = 6) and it returns 5. However, my calculator
and an Access database returns 16. Can someone tell me why Excel returns 5?
Either I'm reading this post incorrectly or you have a broken calculator and
are gettting results out of excel & access that are equally incorrect.
6/37= 0.162 recurring
So if we assume that your result of 16 is a typo and you meant .16 there
must be something your not telling us about the formula your using in Excel.
How is the 6 derived in BT4 ?
What is the format of BT4 ?
Post the pr...Link Outlook 2003 to Exchange 5.5 over VPN
How do you do it?? I have a VPN, I can ping the ip of the
mail server (NT4) but cannot see it when I test from
within Outlook 2003 exchange account
Put the IP address and server name of the Exchange server in your local
computer's hosts file and see if you can then ping by name - if you can, you
ought to be able to access the mailbox. That's just one quick way to do
it....but it ought to work.
> How do you do it?? I have a VPN, I can ping the ip of the
> mail server (NT4) but cannot see it when I test from
> within Outlook 2003 e...Combining Several Worksheet into one
I have over 30 excel worksheets that are:
1. Password protected
2. The sheet is also password protected
3. Each worksheet contains only one tab call "All" and this tab is in
the same format and contains the same column in every sheet.
4. It's located in the same folder
I need to write a macro that will open all these workbook in this
folder and combine the data into one new sheet with only one tab called
"All". I am able to write the code to open all the workbook but am
having a difficult time figuring out how to copy only the cells with
data into the new workbook...