Reply:

Re: array formula to provide all values associated with a given value
I should mention that I'd like the output from the table below to look something like this: 1000020 Td;Tz;Ug;Zm;Zw 1000035 Ao;Mz etc. Thanks - Toby "tschaeffer" <tschaeffer@fews.net> wrote in message news:... > From an Access Query. I've got a list with the unique ID's for reports, > and the next field indicates what Language they were written in. > > This was from a Many - Many relationship in Excel, and many of the reports > were written in many different languages. > > ReportID CountryID >...

Formula
On a graph I have the following trend line formula. y=6E+06e to the power of -5.1056x. If x=2 what would be the formula that I put into excel. Thanks Hugh - Looks like the Exponential type of trendline. Here's a short answer: You'll need more precision to get reliable results. So first select the textbox on the chart, and press the Increase Decimal button numerous times until 15 digits are displayed. Then, on a worksheet, enter the 15-digit values, like this: =612345.123456789*EXP(-5.10512345678901*2) Here's some longer answers: The Exponential type of trendline use...

Formula help......Please? #2
Thanks very much for your replies the both worked great! Thanks again Steve : -- swmasso ----------------------------------------------------------------------- swmasson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=838 View this thread: http://www.excelforum.com/showthread.php?threadid=27080 ...

counting cells with a formula
hi, I want to count cells with a specific value. I don't know how i can put this in a formula, can anybody help me with this problem. regards, ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ solo_razor wrote: > hi, > > I want to count cells with a specific value. I don't know how i can > put this in a formula, can anybody help me with this problem. > > regards, > > > > ------------------------------------------------ > ~~ Mes...

copy formula
In Sheet a, i have these info Employee Number hiring date 122555 apr 5, 2010 152666 apr 4, 2010 123554 apr 4, 2010 451225 apr 5, 2010 In Sheet b, i need this info (that pulls from Sheet a) Employee Number hiring date 122555 apr 5, 2010 451225 apr 5, 2010 How can I do a formula in Sheet b that will pull all the data from sheet a for a specific date e.g. apr 5, 2010. VLOOKUP http://www.ozgrid.com/Excel/excel-vlookup-formula.htm Or INDEX/MATCH http://www.ozgrid.com/Exc...

Formula with time
If I have 0:49 minutes in a cell, the number of hours John worked, and adjacent is $158, the amount John sold; how would I construct a formula to find out how much John would have sold on an hourly basis? Let's do it with the numbers you have: =3D 158 / 49 * 60 to give $193.47 per hour. Now, put 158 in A1, 0:49:0 in B1, and this formula in C1: =3DA1/B1/24 The reason for /24 rather than *60 is due to how Excel handles times - as fractions of a 24-hour day. Hope this helps. Pete On Jan 16, 1:24=A0pm, JP <John...@msn.com> wrote: > If I have 0:49 minutes in a cell, the number...

How do i select different cells to be use in a formula.
Hi I need to know how to select different cells to be used in a formula, but i don't know what is the separator that i need to use to this, see the example bellow Cells that i need to include on my formula: A1 C5 BH32 Thanks. Hi you didn't mention what formula but here's a starting point: =AVERAGE(A1,C5,BH32) or depending on your regional settings =AVERAGE(A1;C5;BH32) hope this helps Cheers JulieD "Manuel" <Manuel@discussions.microsoft.com> wrote in message news:A855A562-725C-4B10-B111-A88F7A767122@microsoft.com... > Hi > > I need to know ho...

formula for selecting all cells
I'm trying to create a macro that will select all my cells from the beginning (top left) to the last one (bottom right corner) and then put a border around them. Any help is appreciated. one way: Dim vSides As Variant Dim i As Long vSides = Array(xlEdgeTop, xlEdgeBottom, xlEdgeLeft, xlEdgeRight) With ActiveSheet.UsedRange.Borders For i = 0 To UBound(vSides) With .Item(vSides(i)) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End W...

Defining an Activecell
Each month I add new data to an existing sheet. I then want to run a macro to insert a new column and a formula. Trouble is this data increases rows each month and I need to be able to identify the final row each time so I can copy the formula down. ie in Jan the data finished at row 100. In Feb it finishes at row 200. I need the macro to be able to know to copy the formula down to row 200 in Feb for example. Hi Ant You can use this function fir finding the last row with data on thye worksheet Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:=...

formula value input
Newbie in troubles... following VBA works with keyboard input only, but not if Target.Colum = 3 contains a formula .... HowTo ? Thanks for your help Sub Worksheet_Change(ByVal Target As Range) If Target.Row > 2 And Target.Column = 3 Then x = Cells(Rows.Count, "d").End(xlUp).Row + 1 Range("D" & x) = Range("C" & Target.Row) End If End Su -- Message posted from http://www.ExcelForum.com Hi if you ned to monitor the change of a formula use the worksheet_calculate event -- Regards Frank Kabel Frankfurt, Germany > Newbie in troubles... > &...

Formulas #28
I want to learn how to do formulas so that I always get an updated balance each time I make an entry. Is there an "easy" way to do this for someone is "computer illiterate"? Example: Balance: $55,690.00 Column K 5 Less 3,213.54 Column J 6 New Balance Column L7 Less 2,894.75 New Balance =KG-J6 ? "btr" wrote: > I want to learn how to do formulas so that I always get an updated balance > each time I make an entry. Is there an "easy" way t...

display formulas
Is there a way to print your spreadsheet so that your formulas appear in the cells, rather than the answers Hi Dianne, > Is there a way to print your spreadsheet so that your formulas appear in the > cells, rather than the answers > Tools, options, view tab, check "Formula�s" Keyboard shortcut to toggle this: control-shift' (above your tab key) Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com Dianne, in addition to Jan's answer, if you want to creates a list of all formulas and their current values have a look here http://j-walk.com/ss/excel/tips/tip3...

Hiding Formulas in cells
Hi, can any one please help me ? I have a cell which can either be an input cell or if no input i entered by the user it will use the formulae already entered in th cell. i.e. if the users inputs a figure it will over write the formul - which is OK as other cells will make a calculation based on thi particular cells data. My problem is I don't want the user to know there is already a formul in the cell but at the same time I don't want to disable the formul bar Does anyone know of a neat way to hide Formula Many thanks Ro -- Message posted from http://www.ExcelForum.com Robe...

Activecell problem #2
I have a really basic question I am trying to print a certain area which starts at a certain cell which may differ every week. I have the code to get to the bottom right of the area I want to print but then I want to select the area, from the active cell to A3: 'go to the bottom cell in colum L Range("L100").End(xlUp).Select ???????? what goes in here?????? 'Print the selected area ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Any help appreciated. remove your select line and add range(Range("A3"),Range("L100").End(xlUp)).Select -...

Formula display errors
I recently posted a query regarding a spreadsheet which displayed "#N/A" in error from a reference to a worksheet in another spreadsheet - but only when the source spreadsheet wasn't open. Suffice to say the formula is a straight =(a cell in another worksheet in another spreadsheet) which works OK in other cells. The formats in the source worksheet were copied down but the error persisted in only the one cell. Formats in both the source and target cells were copied down again resulting in the error persisting in only the one cell. I found that the problem resolved by d...

When entering formula it does not show the value but the formula
We received a file and are trying to sum columns but when the person enters the formula the formula stays visible but not the actual value. I've never run across this before. You've probably got the cell formatted as text. Format as General (or Number) & re-enter the formula. -- David Biddulph "SBongiov" <SBongiov@discussions.microsoft.com> wrote in message news:85781AA1-FE61-4351-A221-A01B63172585@microsoft.com... > We received a file and are trying to sum columns but when the person > enters > the formula the formula stays visible but ...

Protecting my formulas
Is there a way that I can protect cells where i have my formulas so other users CAN'T SEE THEM in the formula bar or in any other way? Thanks, Marko Hi Marco, 1. Select the whole sheet 2. Go to menu Format>Cells... tab Protection, uncheck Locked, press OK 3. Select cells/ranges to protect 4. Go to menu Format>Cells... tab Protection, check Locked and Hidden, press OK 5. Go to menu Tools>Protect Sheet... and follow the instructions... Regards, KL "marko" <marko19@gmail.com> wrote in message news:1134986224.434038.101220@g44g2000cwa.googlegroups.com... > I...

Formula Syntax
I know how to test if a field is EQUAL to something, how do I test to see if a field contains something? So if I have 3 tasks with Descriptions "HAT" and "TOP HAT" and "HAT GREEN" How do make an Iif statement true by searching for anything with HAT in it? Hi, Experiment with the Instr function. This worked for me: IIf(InStr(1,[Text2],"Hat"),Yes,No) -- Rod Gill Microsoft MVP for Project - http://www.project-systems.co.nz Author of the only book on Project VBA, see: http://www.projectvbabook.com "koolkat" <koolk...

what formula? #2
quite a day for my excel skills.... here is one I can't figure out. If a condition is met in a cell, how can I get that whole row to transfer over to a new sheet? In other words the data in a row is automatically transfered to the next sheet if a certain entry is made into a cell in that row. is there an "IF" formula for that?, or are we getting into scary VB territory! D Hi Dave! A formula or function can only return a value to the cell it is in. A formula can�t change the Excel environment. See: 170787 XL: Custom Functions Can't Change Microsoft Excel Environment http...

VBA? Activecell formatting
Looking for a simple VBA macro for formatting three cells starting wit the active cell. Want to use ctrl-d to select activecell, then next two cells to th right and then fill all three cells with color red -- Message posted from http://www.ExcelForum.com Just a single line of code will do it: Range(ActiveCell, ActiveCell.Offset(0, 2)).Interior.Color = vbRed HTH, Nikos "click4mrh >" <<click4mrh.1bmwdk@excelforum-nospam.com> wrote in message news:click4mrh.1bmwdk@excelforum-nospam.com... > Looking for a simple VBA macro for formatting three cells starting with >...

help with formula for last input in column
I would appreciate if someone can help me with this In first column I will type date in some free format 1.01.2012. 3.01.2012 10.01.12. 11.01.11 blank blank blank .... in one cell at the end of table I would like to have the formula that will show me or will copy last input (date, string or cell value) in column or defined range (for instance: A5:A35) is it possible to use formula in one cell for this in Excel 2007 thanks in advance DMag I forgot to write: I solved that on this way, but would like some shorter formula =IF(A49<>0;A49;IF(A48<>0;A48;IF(A47<>0;A47;IF...

Text in formula?
What a pain in the neck to come back home after being away and not being able to access the newsgroups via my newsreader (http:// www.gmayor.com/MSNews.htm). Anyway ... <g> I have this formula in cell A16: ="Don't take today: " &+A15+1 I got it from googling for text and formulas in same cell. I've tried formatting cell as general and text but nothing comes out right. The result in A16 should say: Don't take today: Tue.Sep.14.2010 but instead it says: Don't take today: 40435 Can anyone advise how to fix this? Thanks! :oD On Mon, 30 Aug 2010 ...

VLookup results in #n/a
The results of a Vlookup will be #N/A in cells that do not find a match. I saw someone write a forumula that would sum the column while overlooking the #N/A cells but I don't remember how it was written. Any help will be greatly appreciated. Thank you You could have a formula like: =if(isna(lookup(a,range,1)),0,lookup(a,range,1)) It basically states that if you would get a #N/A result, then show 0, which would not affect a SUM. Andr� "Jean" <jepperson@zebco.com> wrote in message news:096801c34ad7$77c9e510$3501280a@phx.gbl... > The results of a Vlookup w...

Adding on to a formula across rows and columns
I need to add a second section of a formula onto an existing formul across rows and columns. The problems are: -Not all cells currently contain formulas (some are blank) -Not all the formulas are the same (there are at least 2 different formulas among all the existing formulas that I want to add that secon formula or section to)...and the different formulas are somewha interspersed. -Some of the existing formulas contain relative references (I want t change them to absolute references) -If I use relative references to somehow copy a portion of a formul across all remaining rows and columns,...

Linking to files with a formula
In Excel 2003 what is the trick to linking to a closed work sheet using a formula? I'd like to put a list of file names in column A then use a formula to ling to cells from those books. Straight link works ='C:\My Docs\[File_1.xls]Sheet1'!X1 Formula does not ='C:\My Docs\[&A4&]Sheet1'!X1 Indirect will not work because I have too many books. Grateful for any help on this one! Thanks! xjvs Hi JVS Maybe you build your formulas with a macro I use getopenfilename here but it is also possible to loop through the column with file names http://www.rondebruin.nl/s...