Search Results

Search found 4437 results on 178 pages for 'excel udf'.

Page 98/178 | < Previous Page | 94 95 96 97 98 99 100 101 102 103 104 105 | Next Page >

Change cell formatting without VBA. Custom formatting?

- by Sux2Lose

I have a dropdown field with two values in a form I am creating. The expected data in cell D15 changes depending on the selection. If Option A is selected then a dollar amount is expected. If option B is selected that a percentage is expected. I would like the cell to be formatted as 'accounting' w/ zero decimals for option A and 'percentage' with zero decimals if option B is selected. I do not want to use VBA, if possible. I'm hoping there is a custom formatting solution.

Read the article
How to search unique dynamic data in a sheet and then copy relevent row in diffrent sheet?

- by Hemant

I am getting data from internet (DataFrom Web) In sheet1. Then I disperse that data in to three sheets based on three unique text. Like a,b and c. Rows are copied to sheet a,b and c sheets depending on text (a,b,c) they have. All the rows have one unique text (like url) by which they can be searched. I have added static data corresponding to the row. The problem is when ever internet data is changed (row addition/substitution or randomized). My static data loses its connection with the original row for which it was written. I want to search the data based on one unique key and put it to its original place where it used to be with static data.

Read the article
Pivot Table from data with merged cells

- by Graeme

I have a energy spreadsheet for multiple sites. the first row has month and year. the next row has columns for date invoice received, KW hours and cost. So there are three columns for each month. I have merged the month cell across the three columns. When i create a pivot table the date kw/h and costs are labled date1, date2, etc. Can I link the months headings to the subheadings to get meaningful headings in the pivot table????

Read the article
Combine multiple rows into one

- by Jim

I am trying to combine multiple rows of data into one. Column A contains the value on which the groupings will be based -- rows whose Column A values match will be combined into one row. My range extends from column A through X so I need a matching row of data to start in column Y. Example: +--------------+ ¦ 1001 ¦ A ¦ C ¦ ¦ 1001 ¦ B ¦ D ¦ ¦ 1002 ¦ A ¦ E ¦ ¦ 1002 ¦ B ¦ F ¦ ¦ 1002 ¦ C ¦ G ¦ +--------------+ Desired Result: +------------------------------+ ¦ 1001 ¦ A ¦ C ¦ B ¦ D ¦ ¦ ¦ ¦ 1002 ¦ A ¦ E ¦ B ¦ F ¦ C ¦ G ¦ +------------------------------+ The VBA code I am currently using is not taking the entire contents of the matched row. It is only taking the data in the 2nd column and moving it up. VBA Code: Sub Mergeitems() Dim cl As Range Dim rw As Range Set rw = ActiveCell Do While rw <> "" ' for each row in data set ' find first empty cell on row Set cl = rw.Offset(0, 1) Do While cl <> "" Set cl = cl.Offset(0, 1) Loop ' if next row needs to be processed... Do While rw = rw.Offset(1, 0) cl = rw.Offset(1, 1) ' move the data Set cl = cl.Offset(0, 1) ' update pointer to next blank cell rw.Offset(1, 0).EntireRow.Delete xlShiftUp ' delete old data Loop ' next row Set rw = rw.Offset(1, 0) Loop End Sub

Read the article
Vlookup using wildcards in indexed column

- by Dm3k1

I know how to use a wildcard with Vlookup on the reference value, but what about on the matched column index? I know you can do for instance VLOOKUP("*Hello*",A4:G4,2,FALSE) However, what if you wanted to match a cell that is "Hello", with another one that is "Why, Hello there!" (so the opposite i suppose) My data is set in a way where a macro is going to ask if A4 in workbook 1 matches C2:C25000 in workbook 2 to return the corresponding value in D back to workbook 1. The thought is that when A4 in workbook 1 says Its DHS Here, that I could input a value such as DHS in column C in workbook 2 and have it say its a match. Is this possible?

Read the article
Calculating the number of occasions with a set period sumproduct function

- by user158056

=SUMPRODUCT((F16:F274=("A")) *(F17:F275<>("A"))) +SUMPRODUCT((F16:F274=("AH")) *(F17:F275<>("AH"))) +SUMPRODUCT((F16:F274=("AU")) *(F17:F275<>("AU"))) +SUMPRODUCT((F16:F274=("AHU"))*(F17:F275<>("AHU"))) I am using the above formula to add the number of occasions sickness occurs using the following as a key. It works fine until you get say an A and a AH in the same sickness period. Instead of reporting just one occasion off it reports two. Is there a way I can separate this? Absence A Absence 1/2 AH Absence Unpaid AU Absence 1/2 Unpaid AHU

Read the article
Transposing a set of records

- by Flethuseo

I need to transpose records from a table, but by groups like shown in the example below. I need to do this for many records identified by a row number at the beginning of the table. Need to transpose From: 1 LastModified$Time 2011-05-10 12:57:03.0 1 Count1 0 1 Count2 58 1 LastModified$UserName mbeardsell 2 LastModified$Time 2010-03-31 21:45:53.0 2 Count1 0 2 Count2 0 2 LastModified$UserName MBeardsell 3 LastModified$Time 2006-12-13 12:55:47.0 3 Count1 1 3 Count2 0 3 LastModified$UserName markhall to: LastModified$Time Count1 Count2 LastModified$UserName 1 2011-05-10 12:57:03.0 0 58 mbeardsell 2 2010-03-31 21:45:53.0 0 0 MBeardsell 3 2006-12-13 12:55:47.0 1 0 markhall

Read the article
Apart from visually looking - is there a way of calculating when a trendline will cross another seri

- by Adrian

That is to get a exact answer rather than a visual reading.

Read the article
cut text from each line in a txt file

- by bboyreason

i have a text file where each line looks like this: <img border=0 width=555 height=555 src=http://websitelinkimagelinkhere> each line is like that for like 1500 lines, i want to sort of 'grep' (i dont think that will work because it returns the whole line) each line for 'http://websiteimagelinkhere' output file should have newlines or tabs after each image link, like the original file. or if someone only knows a way to do this with each element being in a cell of the same column that would be okay too.

Read the article
Macro - maintain paste link if new row is added to master spreadsheet

- by Ross McLaughlin

I have a master spreadsheet that has a portion of data (say columns a to e) that paste links to a report. Each row paste links to its own report. If I add a new row to the master spreadsheet I now have the wrong data linking into my reports. I know if I have the reports open when a change is made to the master it will update the reports. However, with the number of reports I will soon have this will no longer be practical. Is there a macro or formula that can be added to maintain the correct data link. I have no real knowledge on such matters and as much information as possible would be greatly appreciated. Many thanks in advance.

Read the article
How to change color of a cell in one sheet if its value increases or decreases from another cell value in a different sheet

- by Sean Coffey

I am putting together a workbook with 52 sheets for the weeks in the year. In this workbook there are multiple columns for percentages on each sheet. I want to be able to change the color of each cell based on whethernit increases (green) or decreases (red) from the same cell in the previous sheet. I have the formula i.e. =IF(ISERROR(B4/D4),"0", B4/D4) to calculate my percentages but I don't know how to put the reference in for this change. Any help?

Read the article
Conditional formatting & vlookup

- by zorama

Please help me with the formula: Main Sheet is Sheet2 B COLUMN I want to look up sheet1 A & B columns with Sheet2 A & B columns from 1 workbook that if sheet2 A are same/equal as Sheet1 A column, also if Sheet2 B column are same/equal as Sheet1 B column , how will I highlight the Sheet2 B column that if Sheet1 A & B + Sheet2 A & B are exactly equal . EXAMPLE: SHEET 1 SHEET 2 SHEET 2 Result A B A B A B CODE NO CODE NO CODE NO A 12 B 205 B 205 (highlight to red) B 105 B 20 B 20 (highlight to red) A 45 B 100 B 100 A 56 A 56 A 56 (highlight to red) A 78 B 25 B 25 A 100 A 12 A 12 (highlight to red) B 77 A 45 A 45 (highlight to red) B 108 A 20000 A 20000 B 20 B 205

Read the article
Needing to concatenate between cells that change daily. I want to be able to automate this vs manual

- by Harold

I use concatenate to pull data together from different cells in my spreadsheet. Since my data changes daily, I want the formula to also change daily without having to manually input the new cell in the concatenate formula. I am looking for a way to do this but not sure how. Can anyone out there help me out please!? I appreciate the assistance in advance! Maybe this will help to explain what I need. I have a row of data from D4:AH4 that I insert daily based on the new day. When I use the concatenate and us the following formula: =CONCATENATE(TEXT('Raw Data'!B4,"m/d")," ",TEXT('Raw Data'!C4,"")," ",TEXT('Raw Data'!E4,"0.0%"))... E4 being the cell that changes daily where next day would be F4, G4, etc... All other parts of the formula will stay the same. I hope this helps! Thanks! :)

Read the article
Writing Macros to find a specific cell and paste the value from a control cell into it

- by G-Edinburgh

I am having some issues writing a Macro to do the following. I have a very long list of rooms with two columns one containing the room number i.e. B-CL102 and the other containing a varying integer.I am looking to create a new column that will contain another different integer for each of the rooms. Is there any way to write a Macro so that I can use two control cells at the top of the sheet, type the room number into one and the integer matching that room into another, then run the Macro and it will automatically populate the correct cell. Then I can change the two values in the control cells and run the Macro again and so on. Thanks for your help, I have a very minimal amount of experience with Macros essentially just the basics. Thanks G

Read the article
Do I need a helper column, or can I do this with a formula?

- by dwwilson66

I am using this formula =IF((LEFT($B26,2)="<p"),0,IF($B26="",0,IF($F26<>"",0,(FIND("""../",$B26))))) To parse data similar to the following. <nobr>    contractor information</nobr><br>       <a href="../City_Electrical_Inspectors.htm"><b> City Electrical Inspectors</b></a><br> <nobr>    <a href="../City_Electrical_Inspectors.htm"><b>inspection</b></a></nobr><br> My problem comes in cases such as the first line, in which the line is not a new paragraph nor a link, and my FIND returns an error of #VALUE! Id like to create an IF test to scan the line for the existence of the pattern in my FIND statement before processing that statement. I figured that looking for an error condition may be the way to go. However, the only way I can envision this is as a self-referential formula, similart to the following pseudocode. IF(ISERROR($L26)=TRUE,$L26=0,L$26=the-result-of-the-formula-above) Can this be done with a formula or do I need to use a new helper column? Thanks.

Read the article
Unique string values in range

- by Dean Smith

I have some spreadsheets where there are large number of cells that have essentially been used for free text. There is a finite set of values for this free text and most, if not all repeat. eg. A B C D 1 Monkey Gorilla Cat Dog 2 Dog Cat Gorilla Gorilla 3 Dog Dog Dog Cat There are probably 50 or so different cell values spread over multiple sheets and hundreds of rows and columns. I need to analyse this data and count occurancies, which is not a problem other than getting a list of unique values to start with and this has been driving me up the wall. What is the best way to produce this list. So from the above we would have Monkey Dog Cat Gorilla In order of preferred solutions, as this will need to be done monthly. Dynamic formula based VB Script Other ( Advanced filtering or other manual steps )

Read the article
How to check if cell contains a value of another cell

- by Antonio Oliveira

Here is the scenario: On column A each cell has random text within it (names, address, account numbers, etc). Column A extends down to over 40 thousand rows. On column B I have a list of (account) numbers. Column B extends down to about 5 thousand rows. I need to filter column A to show only the rows that contain the account numbers that are part of column B. Neither column A nor B are in any specific order. I think he above scenario speaks for itself, but I can provide a quick example if needed (don't want to over complicate it).

Read the article
How to get cells to default to zero or calculate additonal fees, based on selection from a drop-down list

- by User300479

I am building a Pay Rate Calculator worksheet with a Flat/Base pay rate & numerous Overtime pay rates. I would like to be able to have the "Overtime" pay rate cells to change depending on my selection from my drop-down list. My list selections are "Flat Rate" and "Compounding". 1) If I select "Flat Rate" how can I make all the "Overtime" cell rates and totals default to zero or calculate to zero, to show the user there is no overtime rates to be applied to this job and to use the one rate to pay? 2)And if I select "Compounding" the Overtime rate cells are updated to add/include additional fees, to show the user Overtime rates apply to the job and penalties have automatically been calculated on top for them. Please explain like I'm a 2 year old - learning as I go. Many thanks :)

Read the article
IF Statement using dates for a budget template

- by Leah Allen

I am working on a budget and want to automatically account for increases in rent in the correct month, I would also like to account for dates tenants move in or out. I may also sometimes have a tenant in a space all year with no changes to rent. Below is an example of my budget with all three scenarios. SQFT BaseRentperSQft BaseRentIncrease DateofIncrease CommencementDate TermDate Jan-Decbymonth 10,000 $15.00 $15.25 05/01/2013 11/30/2013 10,000 $15.00 04/01/2013 10,000 $15.00 I would like to build a formula to accomplish all criteria. Thanks in advance, I can only write simple IF statements, this one is out of my league.

Read the article
If a cell contains a Symbol, then paste a Description into another cell

- by Lola

I'm working on rolling-up a series of charts. I'd like an easy way to summarize by category. The original looks like the sample below but by week for the entire year. I want to know all of the AD 1's, etc. I will need in CA (for each state) by Publication. so the end result would be CA AD 1 PUBLICATION 1 CA AD 2 PUBLICATION 1 CA AD 3 PUBLICATION 1 PUBLICATION 2 PUBLICATION 3 A B C D E 1 PUBLICATION1 CA TX NM AZ 2 AD 1 · 3 AD 2 · · · · 4 AD 3 · · · 5 PUBLICATION2 CA TX NM AZ 6 AD 1 7 AD 2 · · · 8 AD 3 · · · 9 PUBLICATION3 CA TX NM AZ 10 AD 1 11 AD 2 · · · 12 AD 3 · · · Thanks so much!

Read the article
How is starting and ending row number of a Range obtained?

- by Robert Kerr

Given a user-selected Range, what is the simplest way to determine the starting row number, and ending row number? Range.Address returns a string containing any number of possible formats. There has to be something simpler. Desired: Dim oRange As Range Dim startRow As Integer Dim endRow As Integer oRange = Range("A1:X50") startRow = oRange.Address.StartRow endRow = oRange.Address.EndRow of course, those properties do not exist. I want to do the same to return column letters.

Read the article
Can you convert an address to a zip code in a spreadsheet?

- by moe37x3

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this? I'm dealing with US addresses, but answers pertaining to other countries are interesting, too. UPDATE: I guess I'm mostly hoping that there's a way to do this in Google Spreadsheets. I realize that you need to access a vast ZIP code database to do this, but it seems to me that such a database is already inside Google Maps. If I put an address in there without ZIP code, I get back an address with ZIP code. If Maps can do that lookup, maybe there's a way to make it happen in Spreadsheets, too.

Read the article
Formula to search for a value in a range of cells

- by Sacha

I have a spreadsheet with several cells in one column. Each cell has identical conditional formatting: In 1 cell value equal to ='Y' In 2 cell value equal to ='N' In 3 cell value equal to ='?' What I would like to achieve is, if all 7 cells contain "Y" as the option then a separate cell will display "Yes", but if any of the cells contain "N" as the selection then the separate cell will display "NO". How can I achieve this?

Read the article
How to make variable range of cells?

- by Ertai

In A column I have a set of numbers (over 1 000). I want to get average of ten of them (a1:a10) and wrtite into next column (B). Now I want to get next ten numbers and get average of them (a11:a20). And so on... How to get this if in C1 i would have number which is range (i.e 10 = a1:a10/a11:a20 ; i.e 25 a1:a25/a26:a50) of the cells? When I change C1 value I want to column B to update automaticaly? Is this possible?

Read the article
Find rows that match multiple (two) criteria

- by Scott Sanders

I am trying to get a list of items that match on two criteria. DATE PRODUCT CHANNEL DEC 13 Cat Chow DRUG NOV 13 Pupperoni DRUG DEC 13 Meow Mix DRUG DEC 13 Jerky Treats DRUG This is the data from A1 to C5. I want all the product sold on DEC 13 and in the DRUG channel. This is the formula that I have now, which is only bringing back the first example. INDEX($B$2:$B$5, MATCH(1, ("DRUG"=$C$2:$C$5)*("DEC 13"=$A$2:$A$5), 0) )

Read the article

< Previous Page | 94 95 96 97 98 99 100 101 102 103 104 105 | Next Page >