Free Microsoft Excel 2013 Quick Reference

copy a formula that does not change the column or row number?

how can I copy a formula that does not change the column or row number?
I want to use the same formula with set column and row but when I copy from
cell to cell it goes to the next column or row

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1


how can I copy a formula that does not change the column or row number?
I want to use the same formula with set column and row but when I copy from
cell to cell it goes to the next column or row

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200606/1

I'm just trying to COPY/PASTE a formula that will use the same range of cells
but I'm having problem finding out how ( I know it changes "relativately"
accordinly to the rows and columns). I want it to stay UNCHANGE! Tx. Excel
2003/ Windows XP.
Also Q2 in the same formula ( COUNTIF+COUNTIF+COUNTIF+...) is there any way
that I could change the criteria just ONCE and it will change in the whole
formula.
Ex.
=COUNTIF(SHEET1!L1:L300,"SUNDAY")+COUNTIF(SHEET2!L 1:L300,"SUNDAY")+etc...
I'like to COPY/PASTE always in L
and change just one time SUNDAY for MONDAY(after pasting it ) resulting in
MONDAY in all the countif ( about 12 ). Tx

I have a spreadsheet that I use the vlookup command. However, I have 23
lines per payroll check per employee - I am getting the data I want. However
it has 23,000 lines. The formula is on all lines but the column Index #
chaning to pull the correct information. Is there a way to copy this forumla
but have it change the column Index # automatically, instead if me manually
change each column when necessary. Example below - hopefully this will clear
up any confusion.

=VLOOKUP(A2,'D:leblancPAYROLL[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,8,0)
=VLOOKUP(A2,'D:leblancPAYROLL[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,9,0)
=VLOOKUP(A2,'D:leblancPAYROLL[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,10,0)

=VLOOKUP(A2,'D:leblancPAYROLL[paytst50-mod.xls]paytst50'!$A$2:$Y$1522,11,0)

Using HyperLink Function in a Dropdown List does not give the Hyperlink functionality

I am using a DropDown which contains a list of Account nos in the Col B
from Cell B2 till B8 as shown..

Row 1 Col B
Row 2 <- Blank
Row 3 Acct1
Row 4 Acct2
Row 5 Acct3
Row 6 Acct4
Row 7 Acct5
Row 8 Acct6

Now this List is used in a defines Name as AcctNos..
 cell B3 = 
IF(ISERROR(HYPERLINK("#"&CELL("address",INDIRECT("'"&$J3&"'!$A$1")),$J3)),$J3,HYPERLINK("#"&CELL("address",INDIRECT("'"&$J3&"'!$A$1")),$J3))
In the Cloumn J the same data is stored simply..

Now, I want to get the Hyperlink functionality in the Dropdown a the MainFile as these values are Individual Acct nos and are Individual Sheets

So how DO i get this working..In the dropdown nothing works..A VBA solution with explanation would also be welcome if this is impossible with Formulas..

Warm Regards
e4excel

Hello,

I need help with linking numbers that do not exactly match each other. In the Excel Sheet 1 I have to match "produktivitet" (column A) with Colum A Sheet 2 and I want Colum B in Sheet 2 to returne its value in Sheet one Colum B.

Put it simple: Cell A in Sheet 1 does not match any of the cell A in Sheet 2. So I cant use Vlookup. I need a function that can returne fortjeneste based on a aproximatly the number that is in column A in sheet one.

Eks.

Sheet 1
A:
0,792719919110212

Sheet 2:
A:

0,8

This means that I cant not use v-lookup because cell in sheet one does not contain exactlyu the same number as Sheet 2. So I need a formula that does not need the exact number to make it return the wanted value.

See the attached sheet for more understanding.

Thanks.

I wish to make a formula that adds dollars in 1 column if a date range is
satisfied in a second column. This is the formula I came up with but it
doesn't return the correct answer. Can any one help me with a correction?
--
D. W. Cameron

For example I have a data set that will grow over time say 13 rows, I want to write a formula that will only use the last 3 rows with data entered and get the Median of those rows.

I know that the Median Formula for the entire 13 rows would be =MEDIAN(A3:A16)

Anyone know a way to make a Median formula only look at the last 3 in any given time without manually updating the formula's range?

Thank you,

excelproof

how do i change the column headings from numbers to letters in excel?

I'm trying to write a macro to hide a column or row based on a specific cell
in the column or row. How do I do it?

I have just changed to excel 2007 - cant find the r1c1 tab to change the
column heading from numbers to letters - can anyone help

Hi Folks,
can you able to hide the column or row using password?
Any help will be appreciated.
thx

How can view differences between two columns or rows number are not neighbors?
...and to see this different values in another color font or autofilling ??
Thanks anticipate..

I have a workbook that I have over 50 worksheets in. One for each employee that lists their hours during each payperiod and calculates their total hours worked. I would like to compile this into a summary worksheet that has each employee in a list and have only their total hours listed next to their name. Each employee sheet is formatted exactly the same with their name in cell A1 and the sum of their total hours worked in cell C40. To create a formula that automattically transfers this information into the summary sheet, I would have to put "='Doe, John'!A1" in the employee column (A) and "='Doe, John'!C40" in the Total Hours Worked column (B). Is there anyway that I could simply copy the formula from the first cell in the column to the cells beneth and have excel automatically change the sheet name to the next sheet in the workbook. It would take forever to edit each cell. If I have to do that, I might as well just go in and type all the information myself from each worksheet. PLEASE HELP!!!!

I have the following code that fires whenever a change occurs in column AC.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
      
        If Target.Count > 1 Then Exit Sub
      
        Set rng = Range("AC:AC")
       
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        If LCase(Target) > 0 Then
                
            
        If Target.Offset(0, -9) = "Puck" Then
        With Range("ah65536").End(xlUp)
        .Offset(1) = Target
        End With
        Else
        
        If Target.Offset(0, -9) = "Tube" Then
        With Range("ai65536").End(xlUp)
        .Offset(1) = Target
        End With
        Else
        
        If Target.Offset(0, -9) = "Anchor" Then
        With Range("aj65536").End(xlUp)
        .Offset(1) = Target
        End With
        Else
        
        If Target.Offset(0, -9) = "QC" Then
        With Range("ak65536").End(xlUp)
        .Offset(1) = Target
        End With
            
     End If
     End If
     End If
     End If
     End If
     End Sub
What it does is takes data from column AC and places it in the appropriate column based on the text in column T. The problem I am having is that the values in column AC are calculated with a formula, which does not initiate the change to fire the code. If I manually enter the value in AC it works great. Please let me know what can be done to correct this problem.

Thanks

I have been using an excel spreadsheet that has 1 cell that shows in the
print preview but does not print on hard copy. All the other cells seem to
be fine. The cell does not contain a formula nor does it contain the result
of a formula in other cells. I have reformatted the cell, the row, the column
and have even deleted the item and reentered it, but it still does not print.
Any assistance would be appreciated.
Thank you

I'm not sure if this is possible, but does anyone know of a way I can use a formula that does not calculate any other cells that already have a formula in them.

The reason being, I have to work with a specific type of imported spreadsheet from a .dat file, and the best way to filter out the information I do not want, is to exclude everything that has a formula. I'm hoping to use some sort of IF statement and exclude all formula driven cells.

Any tips or solutions would be greatly appreciated. Thanks!

I need a formula that has many functions. The formula will be in cell g2. I
want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is
less than 50,000 then the number in a1 should show in g2. If the number in e2
is greater than 50,000 then I need a formula that takes the number in e2
subtracts the number in d1, then divides that number by 1,000. Then that
number is multiplied the number in c1 and then add to this number the number
in a1. Basically the number in a1 is 166.35 and does not change. The number
in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not
change. The number in e2 is the only one that changes and this is the usage
number. The customer receives the 50,000 gallons for 166.35 and for every
1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is
blank or 0 then I need the number in g2 to be 0.
If this is too confusing please let me know and I will try to simplify it.
Thanks for any help.

--
John

This site looks awesome. It's unbelievable that so many people are willing to take time to help others.

Not sure if this is the right forum but I need to write a formula that does simple Math by adding 1 to a cell based on certain criteria then stop once that criteria is met a certain number of times which is denoted in one of my cells.

If F10 is > than 0 then G11 = F11 + 1 if True or = F11 if False
If G10 is > than 0 then H11 = G11 +1 if True or = G11 if False
If H10 is > than 0 then I11 =H11 + 1 if True or = H11 if False
If I10 is > than 0 then J11 = I11 +1 if True or = I11 if False

This Formula continues in a series all the way through W10

Let's say D11 has the numeral 4, I need the above formula to stop adding +1 after the 4th time the formula comes back true:

I am not real verse with formulas and I need to get a formula that if I
change one date that all the other dates following would change by one.

How would I do this?

Is there a way to copy a formula to multiple worksheets so that Excel
understands that you don't want to use the first worksheet formula, but the
formula on the preceding worksheet?

Example:

52 sheets (one for each week)
Cell A1 Worksheet 1 = Jan 7 (first week ending date)
Cell A1 Worksheet 2 = =+Sheet1!A1+7, giving Jan 14
If I copy Cell A1 Worksheet 2 to Cell A1 Worksheet 3, I get Jan 14; I want
Jan 21

I want Excel to consider that I'm copying a formula that references a cell
on the previous sheet, so the result should reference the previous sheet, not
the first sheet

Anyone know how to make it work?

I have a linked cell to an insheet dropdown that loses its connection. The
listbox has "$AK$5" as its linked cell in in its .LinkedCell property.

The list of choices is about 35 names. No matter what I select in the
listbox, it does not change the linked cell.

Anybody else encounter this? What can I do about it?

I have a cell containing:

="Number of combinations = "&COUNTA(C2:C1500)*COUNTA(D2:D1500)

This number easily exceeds 1 million which makes it diffcult to read with all the digits in one long row, so I would like to format it with thousand-separators.
I could make the calculation in another cell e.g. C25 and then write:

="Number of combinations = "&C25

and then format C25 to thousandseparators, but that does not change the appearance in the cell where I combine it with the text.

Is it possible to change the number format in such a case?

NSV

Thanks to Patrick Molloy for responding to a previous post, my version of
Excel 2002 with SP3 is responding differently than his version of the same.
Maybe someone can try this and experience the same error and help me correct
a setting, property, option or whatever it takes to solve this error.
Try this example and see if you get the same error:
Open a new workbook and enter the lower case words "ned, jed, bed, ted" in
fields A3:A6.
Open the Visual Basic Editor and add a UserForm. On the UserForm place two
comboboxes. Enter the following code:

Private Sub ComboBox1_Change()
Sheets("Sheet1").Range("$A$2") = ComboBox1.Value
End Sub

Private Sub UserForm_initialize()
ComboBox1.ControlSource = "Sheet1!$A$1"
ComboBox1.RowSource = "Sheet1!$A$3:$A$6"
End Sub

Run the UserForm and select "ned" in ComboBox1 and tab to the other control.
Go back to ComboBox1 and highlight only the letter "n" in "ned" and change
it to the letter "j". Tab to the other control. In my Excel 2002 SP 3, the
value of the ComboBox does not change. If I select the single letter "j" that
appears in the combobox and change it to "d" the value will now change.
In summary, using this example, if I select only the first letter of the
word in the combobox and change only that first letter to one of the other
first letters of the words in the RowSource, the value of the combobox will
only change on every other change attempt. If I change only the first letter
of the word in the combobox to a letter that does not appear in the
RowSource, "r" forinstance, the combo box value will change on the first try.

Strange but true ... any help would be great.
paul d.

Hi Everybody,

I'm new here and I'm glad I found this place. I work with excel a lot and I have run into something that I am unsure about. If you could help that would be awesome.

I have a few thousand power readings from different days organized but I
need them split up now in days in seperate spreadsheets. They are laid out
now so that the dates go accross the top and the circuits are along the
side.

I need a formula that will switch to the next column. Like for example:

In the second screen shot It is referencing cell E79 in Strip Summary. And in the third for 11/13/06 it is referecing D79. I need a formula that I can copy and paste into new sheets that will account for that letter change so that I can make a seperate spreadsheet for each date listed in the column on the first scn shot.

http://picsorban.com/upload/power scnshot.jpg

http://picsorban.com/upload/power scnshot 2.jpg

http://picsorban.com/upload/power scnshot 3.jpg

I wasnt sure what to search for so i'm sorry if this was recently posted.