I have a row of cells that have last names in them, is there a way I can insert some data into all of the cells, this data will be the same for all names. Ex Before jones1 After (alias=jones1) then if possible export all of the names to a word or text file all in one line. Ex (alias=jones1)(alias=smith2)(alias=johnson3) Thanks for any help!!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Assuming last names are in A2:Z2 Put in A3 :...

What I want to do is the following. I have a worksheet in which I have created the following; Name-Column , select-Column. I now want to created a formula in a new sheet which check if the are "x" in the select column and copy the names of the name-columns to cells in that new sheet. (which is in the same workbook) is that possible? If so please let me know. Thanks in advance. To find a single "x" you would use VLOOKUP(). For all the "x"'s, see: http://office.microsoft.com/en-us/excel/HA012260381033.aspx -- Gary''s Student - gsnu20...

Text To column wont work because I have name and address info in a single cell in this format. Each cell has different data representing different addresses. Name St # St Name Phone Number City, State zip I want a formula or something to take first line and put in one column, the 2nd line in another column and the 3rd line spread across 3 columns. Although if you could just find a way to split each line into a column, that would work. Once again, Text to colun won't work, I have multiple cells with diffeent addresses, they are not all the same and there are no deli...

Hi, Someone gave me this formula to average the most recent five items in a column of numbers: =AVERAGE(OFFSET(B2,COUNTA(A:A)-5,0,5,1)) It works great unless the list has blank cells. In that case it does not work. 1. 6 2. 3. 7 4. 9 5. 6. 2 7. 8. 1 9. 23 10 4 How can I make it work when the columns have some blank cells? Thanks, Frank Try =IF(count(A1:A10)=0,",Average(A1:A10)) >-----Original Message----- > >Hi, > >Someone gave me this formula to average the most recent five items in >a column of numbers: > >=AVERAGE(OFFSET(B2,COUNTA(A...

Hello, I have multiple pivot tables on the same worksheet to allow for easy comparison of data. The source data is based on the week and expected to be refreshed daily. As the week progresses the size of the pivot tables will grow and overwrite data below it. I am looking for a workaround to this problem or if there is a way to set the pivot table to insert a new row below it if the size will increase and before data is updated. Thanks in advance, Anissa Hi, There is no feature of pivot tables to do what you wish. You pretty much have two options that I can think of: 1. Prepare e...

I want to calculate elasticity of expenditure (by households) in education with respect to increase in income. Pls help me. I have huge data set from household survey Yam This is one of those cases in which I kind of go out on a limb for the poster. Since its the 17th and nobody has come to your rescue yet, I think I can safely presume one or more of the following are true. One, this community is as baffled as I am about what Elasticity of Expenditure IS, in which case you probably want to provide us with a mathematical relationship to work from. Two, you may have implied a questio...

MS Excel VBA Pivot table link cell fetch records I have a table 'tbl_Final' in MS Access 2007 where the data is used and cached in a pivot table found in the 'Data' worksheet in MS Excel 2007. In this MS Excel 2007 workbook, I have various worksheets reports which links to the pivot table values found 'Data' worksheet. I linked formulae in one of the cells found the worksheet reports, looks like this: =GETPIVOTDATA("Amount",Data!$B$4,"Month", 2,"Year","2008","Scenario","Budget") Lets sa...

I have a spreadsheet with one column ("name") that contains names in the following formats occuring randomly down the list: Firstname Lastname Firstname & Firstname Lastname Firstname Lastname & Firstname Lastname I need to get them all into two new columns "firstname" and "lastname" and I'm having difficulties writing a formula to account for all 3 scenarios in one cell. (If I have to do it in multiple steps - so be it!) For the 1st example, the contents of the new "firstname" column should be Firstname, while the "lastname"...

I have an array formula which automatically summarizes all available values of a certain column, in ascending order. The summary is fixed to a maximum of 8 cells and in many cases it uses less than the available 8 columns. Is it possible to format the cells by showing a 0 when the result/cell is empty In other cells in the sheet the cells show empty instead of a zero by using the custom format of 0;-0;"" Now I want an empty cell to show 0 Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ----------------------------------------...

How do I draw a circle around a cell I want to highlight - I want to see the text within the cell and colour the circle red. I tried inserting a circle from the 'drawing toolbar' but I couldn't see the text in the cell. i have seen Excel worksheets with this feature and would like to emulate it. Thanks, Janev See Tools/Formula checking/Display Formula checking Toolbar/Circle unvalid values Regards, Stefi „Janev” ezt írta: > How do I draw a circle around a cell I want to highlight - I want to > see the text within the cell and colour the circle red. I tried > ...

This is a bit complicated. I will try to explain the best I can. I would simply like to know where to start looking to solve this problem. I need a way of tracking several 'types' of hours including calculating peak and off peak hours when scheduling employee's time sheets. these are the criterias. - off peak hours are between monday (9am) through wed (7pm) - peak hours are wed (7pm onward) - sunday (end of day) - if an employee works over 8 hours, it's only counted as 7.5 hours of pay (subtracting lunch/dinner) i need a way of entering times into excel so that these ho...

Can anyone help with this query? e.g. I want to work out how many people are working at any time in a 24 hour period. The number of people rostered on differs each day of the week. Monday Time No. rostered on 6.00 2 7.00 2 8.00 2 9.00 2 10.00 (and so on) Can I add additional staff number in my 'no. rostered on column' by just entering the number into the cell. Say, if there were an extra 2 staff starting their 8 hour shift at 9. am. is there a way of just typing in '2' into that cell and getting '4'? What I do at the moment ...

I'm using Excel 2007. I need a custom exponential decay function for a worksheet. The parameters are Y0, H, and X. It will return Y. The formula is Y = 1 + (Y0-1) * EXP((-LN(2)/H)*X) EXP is the standard exponential function (e^x). LN is the natural logarithm. I'd like to call the new function ExpWt(Y0,H,X). I just need it to be available to this worksheet (or workbook), but may want to move it to someplace where it can be used by any workbook. Thanks Pegs. Copy to Module (ALT + F11, Insert Module) and use as normal. e.g. =expwt(B3,b4,b5) Public Function ExpWt(ByRef YO, ByRef...

Hello, I posted this question earlier in the execl.worksheet.functions group, but did not get an answer. I apologize for the repost. I need some help referencing certain cells from one worksheet in another. Here is an example of what I am trying to accomplish: Worksheet 1: Item # Name Value 1 item1 100 2 item2 0 3 item3 0 4 item4 54 5 item5 0 6 item6 0 7 item7 70 I need to create a separate table in Worksheet2 that as only the NON ZERO values from the table above in Worksheet 1. S...

I am looking for a template that would graph price vs. time for corporate or municipal bond. This graph would show the amortization o a premium for bonds purchased above PAR and would show the accrual o original issue discount for OID bonds purchased below PAR. An optional call features or mandatory put features would be considered i the calculations. Chuckles12 -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/show...

Hey guys, I have 2 related cases that I dont understand how to get Excel formatting to work: 1. If I imported or copied rows of data (numerical) from another file (Word or Access) into Excel, the data is presented as raw numbers in each cell. Now I apply a formatting (i.e. I want comma separation for thousands, etc.) and it will not show up. However, if i then double-click inside a cell (as if to edit the contents directly), then hit enter the formatting I want shows up... but only that cell. I have hundreds of rows and I really need to force excel to display the formatting i want immediately...

Hi All, Can anyone tell me how I can hide multiple rows when a particular cell is zero? It has to go automaticly, so no buttons... When D37=0, hide rows 37-48 When D49=0, hide rows 49-58 When D59=0, hide rows 59-68 When D69=0, hide rows 69-78 When D79=0, hide rows 79-88 etcetera. How can help me with this macro?? Thanks in advanced. Best regards, BL Hi, What should happen if e.g. D38 = 0? Maybe this applied to the desired sheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a, rng As Range Set rng = Range("D37:D1000") For Each a In rng ...

I have a list of sales for Canadian and US Customers. I marked in the column besides the sales amount if it is CDN or US. How can I add the CDN or the US Sales only. Thanks for your help. Use SUMIF() See HELP for details -- Kind regards, Niek Otten "ED" <ED@discussions.microsoft.com> wrote in message news:7B61595A-17DD-4254-A46F-A8D26BF0D9AB@microsoft.com... >I have a list of sales for Canadian and US Customers. I marked in the >column > besides the sales amount if it is CDN or US. > > How can I add the CDN or the US Sales only. > > Thanks for y...

I have this formula that goes into E2 in the first line: =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),"")) which works if the text string PST+GST is found in D2. But I don't know how to branch out from there. There are 4 possible answers to go into D2 which will affect outcome of E2. They are, and I'll list them all including the PST+GST case above: if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13) if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06) if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06) [c...

:confused: Hi, Could anyone help me with this problem? Here is th table: Apple Banana Orange Pear 15 45 20 335 85 65 44 77 11 787 88 936 I want to either to be able to 1)use a function to return the name o the fruit containing the lowest level in each raw, so for first row i would return "Apple", sedond row would return "Orange", etc. Can an of you help me writing this function? 2) or if possible, i'd like t highlight the cell in each row that contains the lowest value, so fo first row, cell containing 15 will be highlighted. Can anybody help m with that? Thank...

Hi everyone, I have a 3 column spreadsheet. Amount Frequency Weekly$ (Calculated) 1200 F 600 i.e. divides it by 2 if F 100 W 5200 i.e. times it by weeks in year 5200 Y 100 ie divides it by weeks in year As you can see, I want the Weekly$ amt to be calculated based on whether the Frequency is W (Weekly), F (Fortnightly) or Y (Yearly). any ideas as to what to do ? cheers, Adam Assuming your data is in columns A and B, one way: =A1*INDEX({0.5,100,0.01},1,MATCH(B1,{...

whenever I create a contact information the name appearing the fileas box as"last name to first", is there a permanent way to keep the file as is exactly as full name? Dear, Whynot - In OL XP and 2003 Go to Tools -Email Accounts-click on View or change existing directories or address book(next), click on change, Select show names by File As. (Please respond to this question Was this post helpful to you?) Hope this helps you more Regards APK "Alexbj" wrote: > whenever I create a contact information the name appearing the fileas box > as"last name to f...

I am wanting to open a form within a form based on the value input on a field on the main form. How do I accomplish this? I am a newbie to Access 2007. Thank you!!! Don't have 2007, but in 2003 you could change which form a subform control was displaying with <subform>.SourceObject = "XXXX" "Deb" wrote: > I am wanting to open a form within a form based on the value input on a field > on the main form. How do I accomplish this? I am a newbie to Access 2007. > > Thank you!!! ...

I need to find a way to get total for a particular name. I work in a vineyard and we want to track how many of each vine we have. we have our rows and blocks of vines in a spread sheet. I now have to sort by name then count how many of each vine we have. I also assign the vines a color based on when they will be harvested. Can excel do the above type of calculation based on sum or total of a particular color Thanks so much for any help Ron redwards(at)ap.net Hi Ron, Try something like this modified to suit your ranges. Does not count Conditional Formatting colors. Dave McRitchie has a...

I have a worksheet with a persons name in a single cell. I need to sort these and I forget how to separate them to another set of cells to get the last name. Since some names have 2, 3, 4 or more separate item (ex: Thomas G. Roberts, CPA) I can figure it out. Any help appreciated? Tommy Boy wrote: > I have a worksheet with a persons name in a single cell. I need to > sort these and I forget how to separate them to another set of cells > to get the last name. Since some names have 2, 3, 4 or more separate > item (ex: Thomas G. Roberts, CPA) I can figure it out. > > An...