Search Results

Search found 7708 results on 309 pages for 'excel 2007'.

Page 162/309 | < Previous Page | 158 159 160 161 162 163 164 165 166 167 168 169  | Next Page >

• 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

• 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>&nbsp;&nbsp;&nbsp;&nbsp;contractor information</nobr><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="../City_Electrical_Inspectors.htm"><b> City Electrical Inspectors</b></a><br> <nobr>&nbsp;&nbsp;&nbsp;&nbsp;<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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• i just deleted a worksheet that was critical, what do i do now?

  - by alexluvsdanielle

  i also saved it is there any way to recover it?

  Read the article

• Automation for filling in sets of numbers in each row

  - by Brad

  I need to populate the same number 10 times in a row, then the next number up on the next row. starting at 0, ending at 1000 for example: 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 .... 1000 1000 1000 1000 1000 and to 1000 I need to print out these numbers to cut up and put each row of numbers in each envelope to be sold for a Chinese auction at a benefit. How do I do this dynamically without entering in all of the rows by hand?

  Read the article

• Windows 7 computer cannot delete files from windows 2003 domain share

  - by Dionisis

  Computer running windows 7 pro connected to windows 2003 domain. User creates an xls file and he is trying to delete it. He deletes the file successfully and when he presses F5 to refresh the folder the file re-appears like it was never deleted.

  Read the article

• VBA code to hide or unhide rows based on a cell value

  - by I AM L

  Heres my code, but its not really doing anything, I dont see anything wrong with it: Private Sub PG1(ByVal Target As Range) If .Range("E50").Value = "Passed" Then Rows("51").EntireRow.Hidden = True End If ElseIf Range("E50").Value = "Failed" Then Rows("51").EntireRow.Hidden = True End If End Sub My intention is that when that specific cell in the previous row is set to "Passed" from the dropdown, then the below row would appear, if its a 'Failed" then it'll be hidden instead.

  Read the article

• 24 hour day - having trouble with 20:30pm to 01:00am

  - by Tom

  I'm having problems figuring hours worked, with a 24 hour day formula that accounts for swing and graveyard this is a 4 column layout - example below (I only need to figure totals hours worked minus Lunch) example: in 20:30 out (lunch) 01:00 in(lunch) 01:30 end of shift 06:30 Answer should be: should be 4.5+5 = 9.5 hours worked However I'm having trouble with 20:30pm to 01:00am value thank you in advance!

  Read the article

• IF function that refers to another cell if true

  - by geoconfusion

  Can someone please help? I am using the IF function to find cells within certain ranges, but want the cell to contain the value if it falls within that specific range. for example: =IF (AA3 is between 150 and 400 then AD3 is equal to AA3 and if not leave blank) my current formula below does not work: =IF(AND(AA3150, AA3<400), AD3=AA3, "" ) where AD3 is the cell I am working in... any suggestions?

  Read the article

• Tools for reformatting a spreadsheet into PDF?

  - by Todd

  I'm looking for an easy way to produce nicely formatted PDF documents using a subset of data available in a spreadsheet. Ideally, I would be able to produce one or more PDF documents for each row int e spreadsheet. Each row contains scientific data about a sample. There are numbers, text and images in the Spreadsheet that need to be reformatted in a nice way in a PDF. Ideally, the solution would be a piece of software that a non-technical but highly intelligent scientist could operate.

  Read the article

• Highlight column when a row is clicked, depending on condition

  - by Fredrik

  We have a large matrix with lists of servers on the rows and persons as columns. Then we mark the column/row with an X if the person has access to the server. Pretty basic. But as the matrix grows, it becomes more difficult to quickly find the right person with access. So I'd like some way to make it easier to use In the example above I have clicked on the row "Resource B" and would like all the columns where there is an "X" (User 1, User 2) to be highlighted somehow. Then if I click the row for "Resource C", "User 1" should be highlighted.

  Read the article

• How to change row color based on a single cell's value?

  - by flyfisher

  I have a spreadsheet where I have cell within a row that will contain specific text via data validation. So for instance a cell could contain the text "Due in 7 Days", "Past Due", or "Closed". I want the row color to change depending on the text that appears in the cell. So if the text "Past Due" appears in the cell I want that entire row to turn red, if "Due In 7 Days" appears the row should turn yellow, and if "Closed" the row would turn gray. How can I do that?

  Read the article

• How can I populate other columns after user selects choice in drop-down?

  - by user360332

  I have a spreadsheet that is essentially going to be a form for our user to print out and hand to the necessary individuals. What the user needs to be able to do is select a customer number from a drop down, choose his number, have 2 other (so a total of 3) columns populate. I need the rest of the worksheet to stay in place. I have tried just about everything. How can I solve this with the simplest solution possible?

  Read the article

• Formula to format a cell to subtract one decimal place from another cell and have the calculated results displayed

  - by user242618

  I have a value in one cell that has four decimal places, in the cell below, I have the metric conversion formula. I want the cell below, with the metric conversion formula to display one less decimal place than the cell above. For example, if the English measurement is .#### (4 decimal places), I need the conversion cell to display .### (3 decimal places) and if the English measurement is .### (3 decimal places), I need the conversion cell to display .### ( decimal places), and so on. How can I do this?

  Read the article

• Multiple if functions

  - by user2948699

  I have problem I'm hoping someone could help me with. I have five different text values in 5 cells. I am trying to combine these values into one cell with a comma in between each. However the trick is that if there is no value in (H6) then it must place the word "and" between the cell (F6) and (G6). If there is a value in (H6) then place the word "and" between (G6) and (H6). In the same statement I must also include If there is not value in (G6) then it must place the word "and" between the cell E6 and F6. Please see image attached. I am trying to get the highlighted statements into one cell. So multiple IF statements into one cell. Anyone? =IF(G8=0,(D8)&", "&(E8)&" and "&(F8),(D8)&", "&(E8)&", "&(F8)&" and "&(G8)=IF(H8=0,(D8)&", "&(E8)&", "&(F8)&" and "&(G8),(D8)&", "&(E8)&", "&(F8)&", "&(G8)&" and "&(H8))) I cant figure out the code. Many thanks. Alex Edit: The original image can be found here if size of the inlined is too small.

  Read the article

• Collapsing rows of duplicate dates in a column leaving one row with a subtotal?

  - by Will

  I have several thousand rows of date, time, and values in columns. Each row is contains a date, time for that date, and a value for that time period (hour) So each 24 rows has the same date with each having the next hour of the day. I'd like to collapse or group the 24 rows leaving the last row with a subtotal of the value (column D) to the right in column F. While this can obviously be done manually, several years of data would take a while and there ought to be a way to do this other wise?

  Read the article

• Getting a null reference exception exporting a slightly complex rdlc report to excel

  - by George Handlin

  Have an issue in exporting a slightly complex report to excel from an rdlc. Exporting a simple tabular report works fine, but getting a null reference exception with a more complex one. As is usually the case, it works on the server in the development environment, but not in the test environment. The server in the development environment has iis, sql and sql reporting services on it and test only has iis (I didn't set them up). I'm guessing there is something that gets installed with SSRS that is not installed with just the report viewer. The report has several parameters going into it and a few generic lists going in for datasources. Some tables for display as well as a list. Working with the 2005 version. Exception follows: [NullReferenceException: Object reference not set to an instance of an object.] Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack) +91 Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout) +607 Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets() +150 Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet() +358 Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream) +158 Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream) +385 Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) +230 [ReportRenderingException: An error occurred during rendering of the report.] Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) +296 Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) +1124 [WrapperReportRenderingException: An error occurred during rendering of the report.] Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension renderer, CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) +1681 Microsoft.Reporting.LocalService.RenderWithDataCache(PreviewItemContext itemContext, ParameterInfoCollection reportParameters, IEnumerable dataSources, DatasourceCredentialsCollection credentials, IRenderingExtension renderer, ReportProcessing repProc, CreateAndRegisterStream createStreamCallback, ReportRuntimeSetup runtimeSetup) +1693 Microsoft.Reporting.LocalService.Render(PreviewItemContext itemContext, Boolean allowInternalRenderers, ParameterInfoCollection reportParameters, IEnumerable dataSources, DatasourceCredentialsCollection credentials, CreateAndRegisterStream createStreamCallback, ReportRuntimeSetup runtimeSetup, ProcessingMessageList& warnings) +227 Microsoft.Reporting.WebForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, CreateAndRegisterStream createStreamCallback, Warning[]& warnings) +235 [LocalProcessingException: An error occurred during local report processing.] Microsoft.Reporting.WebForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, CreateAndRegisterStream createStreamCallback, Warning[]& warnings) +276 Microsoft.Reporting.WebForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, String& mimeType, String& encoding, String& fileNameExtension, String[]& streams, Warning[]& warnings) +189 Microsoft.Reporting.WebForms.LocalReport.Render(String format, String deviceInfo, String& mimeType, String& encoding, String& fileNameExtension, String[]& streams, Warning[]& warnings) +28 Microsoft.Reporting.WebForms.LocalReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileNameExtension) +52 Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +153 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +202 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +303 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

  Read the article

• Export GridView to Excel

  - by nCdy

  using Matt's util code (a bit edited for Unicode text) public class GridViewExportUtil { /// <param name="fileName"></param> /// <param name="gv"></param> public static void Export(string fileName, GridView gv) { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache); HttpContext.Current.Response.Charset = System.Text.Encoding.Unicode.EncodingName; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode; HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); HttpContext.Current.Response.AddHeader( "content-disposition", string.Format(//"content-disposition", "attachment; filename=Report.xml"));//, fileName)); // Need .XLS file using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // Create a form to contain the grid Table table = new Table(); // add the header row to the table if (gv.HeaderRow != null) { GridViewExportUtil.PrepareControlForExport(gv.HeaderRow); table.Rows.Add(gv.HeaderRow); } // add each of the data rows to the table foreach (GridViewRow row in gv.Rows) { GridViewExportUtil.PrepareControlForExport(row); table.Rows.Add(row); } // add the footer row to the table if (gv.FooterRow != null) { GridViewExportUtil.PrepareControlForExport(gv.FooterRow); table.Rows.Add(gv.FooterRow); } // render the table into the htmlwriter table.RenderControl(htw); // render the htmlwriter into the response HttpContext.Current.Response.Write(sw.ToString()); HttpContext.Current.Response.End(); } } } /// <summary> /// Replace any of the contained controls with literals /// </summary> /// <param name="control"></param> private static void PrepareControlForExport(Control control) { for (int i = 0; i < control.Controls.Count; i++) { Control current = control.Controls[i]; if (current is LinkButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text)); } else if (current is ImageButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText)); } else if (current is HyperLink) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text)); } else if (current is DropDownList) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text)); } else if (current is CheckBox) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False")); } if (current.HasControls()) { GridViewExportUtil.PrepareControlForExport(current); } } } Question : How to make downloaded file editable (not Read only) And ... XLS wont opens with Unicode format. When I changing format to UTF8 I can't see Russian words :S Second question : How to make Unicode for .xls Third question : How can I save table lines ? Thank you.

  Read the article

< Previous Page | 158 159 160 161 162 163 164 165 166 167 168 169  | Next Page >