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

Free Microsoft Excel 2013 Quick Reference

Adding Worksheet To Formula

Below are two formulas that I have are on one worksheet. I need the
remaining formula added in order for them to calculate from another worksheet
titled Overall.

=COUNTIF(--(MONTH(O31:O980)=MONTH(C3)))

=COUNTIF(--(MONTH(O31:O1001)=MONTH(C3)),--(ISNUMBER(SEARCH(A10,S31:S1001))))

Thanks.


Post your answer or comment

comments powered by Disqus
I tried the following tip from osgrid.com Excel tips w/o success:

Adding Text to FormulasTo show a formula result and text or number(s) in the same cell type a & (Ampersand) after the formula then your text/number(s).

Could you provide an example of doing this with a specific formula, e.g., =AE7+AF7&this is a test? Is this the correct interpretation? Why would you want to do this?

I'm running Excel 2000

Thanks

I'm trying to add a new worksheet with a formula. I want to add as many worksheets as I need without manually inserting a worksheet each time. What I'm looking to do is when a page is completed (data will vary from worksheet to worksheet) you check a box and it automatically creates a copy of the sheet that I was previously working on but blank. Once the automatic sheet has be created, all the data that will be computed is linked to a consolidation sheet.

I'm hoping that this will consolidate all the information I need without re-linking the formulas of the new worksheet each time to the consolidation sheet.

Is this possible? If so how?

Thanks!

I am trying to prevent a user from adding worksheets to a particular
workbook. The code I am using (see below) doesn't work; I don't get any
error messages. Any suggesions on what I am doing wrong?

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "New sheets may not be added. " & _
"Sheet will be deleted"
Sh.Delete
Application.DisplayAlerts = True
End Sub

Thanks...Paige

My goal is to calculate a sum from the same column on ALL worksheets. I
already know I can select multi sheets in an existing formula:

=Average(start:end!AA32)

I am always adding worksheets to specific books and I would like to not have
to update the formulas. I want a wildcard or 'select all' in the formula.

I know I could insert the new worksheet between the existing start and end
sheets, but the new sheet would always need to be at the end.

Thank you.

Hi

I have had some excellent help in gettin a new worksheet
to be added pulling data from an Active Worksheet and
formatting it into individual statements using a macro.

The new worksheet added has 290 names and addresses, with
each name and address having a space of 25 lines between
the next name and address.

What I need to be able to do now is to haver certain Cells
to have a formula added into the added worksheet.

For example, each cell in column "L" and in row 15 of each
statement might have the formula F23/3.054. There are
quite a few cells in each statement that need this so I
would like to automate it as the worksheet is added.

Is this possible.

Many thanks

Malcolm

I am trying to write a formula that will link the Job Name from 1 worksheet to another worksheet and so on in the same workbook and can't seem to get one to work. I've searched through the excel help with no results. Is this possible and if so how?

Sometimes when you write a formula it can be very handy to add some text to part of it so you can make easy reference to it later. For this we can use the little known about, or used, N Function E.G

=SUM($A$1:$A$10)+N("Adding Daves Expenses")

It will have no adverse affect on the formula because the N Function returns zero for text.

What N Returns

A number N Returns That number

A date, in one of the built-in date formats available in Microsoft Excel N Returns The serial number of that date

TRUE N Returns 1

FALSE N Returns 0

An error value, such as #DIV/0! N Returns The error value

Anything else N Returns 0

The Function itself is made available in Excel for compatability with other speadsheet programs.

How do I set up a formula to copy both value and format from one worksheet to
another?
When I use the = sign, it copy the value only. How do I get the format be
copied also?

Hello all,
I am trying to link multiple worksheets to a master sheet to summarise
certain details of each sheet.
Lets say that:
Sheet1
A1=Mark
B1=Pritchard
Sheet2
A1=David
B1=Howard
I would like the Master to show:
A1=Mark
A2=David
B1=Pritchard
B2=Howard
I know this can be done with =Sheet1!$A$1, Sheet2!$A$1 etc but there is
quite a bit of data that I need to summarise and worksheets are
constantly being added. So I was wondering if it is possible to make
Excel automatically increment the Sheet1!, Sheet2! etc part of the
formula as manually having to do this everytime a worksheet is added
does not appeal.
Tony

--
Reefaman
------------------------------------------------------------------------
Reefaman's Profile: http://www.excelforum.com/member.php...o&userid=31968
View this thread: http://www.excelforum.com/showthread...hreadid=517951

I have about 450 values in a column that I want to ad TR to the end of it.
What formula would I use to accomplish this.

-thanks,

Oooh, that does sound painful. I have found that Find - Replace All is
the best workaround. (Find the original cell reference, e.g.,
Sheet1!$H$9, and replace [all] with the name.) You still have to
find/replace each name individually, but at least you only have to do
them once.

BBurrows Wrote:
> After many days of trying, I have finally given up and reverted to doing
> it
> all manually. I have all my data cells on different worksheets to my
> formula cells so it has meant naming each data cell then re-doing the
> formulas with the newly named data cells. With most of my formulas
> being
> at least 4-5 lines long, I am in my second week of manually converting.
> I am
> sorry to say I therefore still have no solution for anyone. If anyone
> has
> any suggestions, would all be greatly appreciated.
>
> Belinda
>
> "Mike" wrote:
> -
> Hi... I have been searching for an answer to this problem all day and
> there
> seem to be no real solution.
>
> I have a workbook with 8 worksheets that was given to me. I went
> through the
> entire workbook and named all the important cells and cell ranges with
> global
> names. Then I went to "apply" the names to cells with calculations
> where by
> the cell location would be replaced with the names.
>
> The APPLY function works just fine in replaceing cell locations with
> NAMES
> that were created on the same page. But it does not seem to work when
> trying
> to apply names to calculations that were created on a different
> worksheet
> then the named range itself was created.
>
> For example: on worksheet1 cell D7 contains =SUM(A7:C7) and is
> globally
> name MyTotal. On worksheet2, cell C4 contains =Sheet1!D7
>
> If I do Insert/Names/Apply/MyTotal on worksheet2, cell C4 I get the
> response:
> "Microsoft Excel cannot find any references to replace". And if I
> simply
> type =MyTotal in any cell on worksheet2, the proper value from
> worksheet1
> appears.
>
> Is there any way to replace these names other than manually?
>
> Thanks. Mike
>
>
>
> "Peo Sjoblom" wrote:
> -
> You have to do each sheet one by one, select the formula and do
> insertnameapply and select the name
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "BBurrows" BBurrows@discussions.microsoft.com wrote in message
> news:4EAB66AC-82FD-4E2E-9B6B-163985809C11@microsoft.com...-
> If you have already developed an excel spreadsheet with multiple
> worksheets,
> and formulas that refer to cells on these multiple worksheets, how
> do you
> change the cell references to names and make sure they are applied
> to each
> relevant worksheet and formula-
>
>
> --

--
jlefeaux

Hi,

I have a large workbook with one tab named "Data". Data in this sheet flows to formulas in other sheets. I have a separate worksheet also named Data that I want to import to the large workbook and have the data flow to the formulas. When I import the data sheet it appears as Data(2). I then tried deleting the original data sheet and renaming the imported sheet to "Data". Now I see that the formulas that reference the data sheet have changed from =Data! to =#ref!.

Any ideas?

Thanks

Charlie

Hello all,
I am trying to link multiple worksheets to a master sheet to summarise certain details of each sheet.
Lets say that:
Sheet1
A1=Mark
B1=Pritchard
Sheet2
A1=David
B1=Howard
I would like the Master to show:
A1=Mark
A2=David
B1=Pritchard
B2=Howard
I know this can be done with =Sheet1!$A$1, Sheet2!$A$1 etc but there is quite a bit of data that I need to summarise and worksheets are constantly being added. So I was wondering if it is possible to make Excel automatically increment the Sheet1!, Sheet2! etc part of the formula as manually having to do this everytime a worksheet is added does not appeal.
Tony

If you have already developed an excel spreadsheet with multiple worksheets,
and formulas that refer to cells on these multiple worksheets, how do you
change the cell references to names and make sure they are applied to each
relevant worksheet and formula

how can create a formula to add data from 1 worksheet to another?

Hi there,

I use this code to print specific database info on to a nice invoice. For example I have a database named "Sep" which is a list of orders. I choose an order by marking "x" beside it and the macro takes all the marked info and prints it into an invoice.

My question is this: How can I add more sheets. I want to use Sep, Dec, Jan, etc....

I've tried the following lines to no avail:

Set DataWks = Worksheets("Sep", "Oct", "Nov")

and...

Set DataWks = Worksheets("Sep")
Set DataWks = Worksheets("Oct")
Set DataWks = Worksheets("Nov")
but, neither of them work.

Here is my entire code (below): Thanks for helping! =)

Option Base 0
Sub PrintUsingDatabase()
    Dim FormWks As Worksheet
    Dim DataWks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    Dim iCtr As Long
    Dim myAddresses As Variant
    Dim lOrders As Long
    Dim myInputColumns As Variant

    Set FormWks = Worksheets("FORM")
    Set DataWks = Worksheets("Sep")

    myAddresses = Array("A14", "C8", "D8", "C9", "B12", "F12",
"F14", "F17", "F19", "F20")
    myInputColumns = Array(15, 1, 2, 5, 6, 12, 14, 13, 17, 18)

    With DataWks
        'first row of data to last row of data in column B
        Set myRng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
    End With

    For Each myCell In myRng.Cells
        With myCell
            If IsEmpty(.Offset(0, -1)) Then
                'if the row is not marked, do nothing
            Else
                .Offset(0, -1).ClearContents 'clear mark for the next time
                For iCtr = LBound(myAddresses) To UBound(myAddresses)
                    FormWks.Range(myAddresses(iCtr)).Value _
                        = myCell.Cells(1, myInputColumns(iCtr)).Value
                Next iCtr
                Application.Calculate 'just in case
                 'after testing, change to Preview to False to Print
                FormWks.PrintOut Preview:=True
                lOrders = lOrders + 1
            End If
        End With
    Next myCell
    
    MsgBox lOrders & " orders were printed."

End Sub


I have several excel 2003 files with identical structure of worksheets. On
one, I added a new worksheet with formulas that refer to cells in other
worksheets in the file. The formulas look like "=(Scores_Sgl!F2) * 1.5". I
want to copy the new worksheet - with the formulas - into other Excel files
but when I do a copy/paste I get the values, not the formulas?

How can I accomplish that?

Hello all, I am trying to get my head around adding data to a worksheet
via a userform. I want to add to lists in sheet 3 in the next available
row but I keep getting a application-defined or object defined error.
Debug points me to the 2nd line of the macro. (Set LastRow).
Can any one help? Thanks
Kezza

Private Sub CommandButton6_Click()
Dim LastRow As Object

Set LastRow = Sheet3.Range("a65536").End(x1Up)

LastRow.Offset(1, 0).Value = TextBox6.Text
LastRow.Offset(1, 1).Value = TextBox7.Text
LastRow.Offset(1, 2).Value = TextBox8.Text

MsgBox "One record written to Sheet3"
response = MsgBox("Do you want to enter another record?", vbYesNo)

If response = vbYes Then
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""

TextBox6.SetFocus

Else
Unload Me
End If
End Sub

Greetings - I am new to this Forum and I'm pretty sure my question is a simple one, but it's driving me insane.

My worksheet has several columns with totals at the bottom. My problem is, whenever I add a row to the top, it is not included in my @sum column total. Is there a way I can set up my worksheet so any row added to the top are automatically included in the column total at the bottom?

Thank you for your help!

Hi,

I have a question concerning excel, and it concerns the following:

Ik have a large number of worsksheets, containing a table with the same type of information on different paintings. Meaning: for example in worksheet 1, there is in cell A1: "Titel", followed by cell B1: "Sunflowers". Followed by: "Painter" in cell A2, and "Van Gogh" in cell B2. This continues untill cell's A8-B8. Furthermore there is a similar line of information in cell's C2/D2 untill C8/B8 in the same lay-out (for example price of the painting, date of last exhibition, place of auction etc.)
Thus, the table contains 8 rows and 4 collumns. Every worksheet contains the same information, but on different paintings. Yet, it is all in the same lay-out and each cell (in different worksheets) contains the same information (f.e. cell B1 in all worksheets contains the title of the painting).

Now comes my problem. I want to have all information in the same worksheet, and in the following way: Row 1 contains a description of the data (Painter, Title painting, Date, Size, Genre etc.) and then there is in every row this information for every painting in every worksheet (f.e. row 2 for the Nightwatch by Rembrandt, row 3 for the Sunflower by van Gogh etc.)

My problem is that I have to many paintings and worksheets to manually place the formula in every cell. Can anyone help me out? What should I do?

A short summary: I need to replicate a formula with one changing variable: the worksheet to which the formula is linked, all the rest of the formula stays the same.

Thanks for your time and effort!

Regards,

Can anyone help me modify the code below, when I run it - it only copies the first row as a header onto the other worksheets; whereas, the first three rows I need to be copied onto the new worksheets that it creates. I've been fiddling with it for a couple of hours and still cannot figure it out - would really appreciate an excel wiz's help.

Option
Explicit

Sub ParseItems()
'Author:    Jerry Beaucaire
'Date:      11/11/2009
'Summary:   Based on selected column, data is filtered to individual sheets
'           Creates sheets and sorts sheets alphabetically in workbook
'           6/10/2010 - added check to abort if only one value in vCol
'           7/22/2010 - added ability to parse numeric values consistently
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String

Application.ScreenUpdating = False

'Column to evaluate from, column A = 1, B = 2, etc.
   vCol = 5
 
'Sheet with data in it
   Set ws = Sheets("Data")

'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
    vTitles = ("A1:O1")
   
'Spot bottom row of data
   LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row

'Get a temporary list of unique values from column A
      ws.Columns(vCol).SpecialCells(xlConstants).AdvancedFilter _
        Action:=xlFilterCopy, copytorange:=ws.Range("EE1"), unique:=True

'Sort the temporary list
    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE3"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
    MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE1:EE" _
        & Rows.Count).SpecialCells(xlCellTypeConstants))

'clear temporary worksheet list
    ws.Range("EE:EE").Clear

'Turn on the autofilter, one column only is all that is needed
    ws.Range(vTitles).AutoFilter

'Loop through list one value at a time
'The array includes the title cell, so we start at the second value in the array
'In case values are numerical, we convert them to text with ""
    For Itm = 2 To UBound(MyArr)
        ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm) & ""
    
        If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then    'create sheet if needed
            Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = MyArr(Itm) & ""
        Else                                                      'clear sheet if it exists
            Sheets(MyArr(Itm) & "").Move after:=Sheets(Sheets.Count)
            Sheets(MyArr(Itm) & "").Cells.Clear
        End If
    
        ws.Range("A" & Range(vTitles).Resize(1, 1) _
            .Row & ":A" & LR).EntireRow.Copy Sheets(MyArr(Itm) & "").Range("A1")
        
        ws.Range(vTitles).AutoFilter Field:=vCol
        MyCount = MyCount + Sheets(MyArr(Itm) & "") _
            .Range("A" & Rows.Count).End(xlUp).Row - 1
        Sheets(MyArr(Itm) & "").Columns.AutoFit
    Next Itm
    
'Cleanup
    ws.AutoFilterMode = False
    MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " _
                & MyCount & vbLf & "Hope they match!!"

Application.ScreenUpdating = True
End Sub


Hi Everyone

Thanks to many of you on this forum, I have managed to get my Excel Sheet up and running and pretty much the way I want it.

The problem I have now is, that I am testing it to ensure that all data fields are correct etc, that when I go to cut and paste from one worksheet to another, my formulas go haywire.

Basic Scenario:

All data entry is done on 'Current Transactions' worksheet, and when the transaction is completed, the data row should theoretically be cut from this sheet and pasted into the "Completed Transactions" worksheet.

The problem is, that if I do this, the formulas in the 'Formulas' sheet, all go to a #REF! or #VALUE! error.

I'm hoping that there is an easy way around this, or perhaps some VBA Code that exists that I could get my hands on to prevent this from happening.

I guess a couple of things to note are that I don't consider myself to be a real experienced user of excel, a lot of the formulas here have been provided from the internet, or haver been built via trial and error on my own.

Also, I really don't have much experience or knowledge of how to work with VBA Code either. The code I do have in this Workbook, again, has come from the internet.

The only reason I say this is that if you are going to post replies, I'd really appreciate if you could you use laymen's terms wherever possible, as I am still only feeling my way through Excel and its awesome power.

Thanks in advance!!

Can you create a formula to pull from one worksheet to another, both within
the same file?

I have a worksheet to track customers purchases
the layout looks like this:

***** A ******* B ******** C ******* D ******* E
1 * Total ************************* Prod1 ** Prod2
2 * Spent ** Name * Account * $30.00 * $20.00
3 *********** Cust1 *** 12345 ****** 0 ******* 4
4 *********** Cust2 **** 7678 ****** 1 ******* 8
5 *********** Cust3 **** 2345 ****** 0 ******* 1
6 *********** Cust4 **** 22489 ***** 3 ****** 12

Basically I need to simplify this formula to allow for a Large amount of products
as well as add new products in the future.

A3 formula =(D2*D3)+(E2*E3)+(F2*F3)+(G2*G3)

I would then like to copy the formula down column A for all customers..
Any ideas? or do I have my worksheet layed out completely wrong?


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