I'd like a formula to be inserted in cell A4 that returns all products listed in row 1 that have FALSE in row 2. Is this possible? I tried a HLOOKUP and a MATCH yet it wasn't returning all occurances
View 9 Replies
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
I am trying to read a value in cell A1 and make say cell B1 read the same. The data in cell A1 is a real time percentage value that gets updated every lets say 10 secs. Every time the data is updated the new value goes into the next cell A2, A3, A4..... and so on. Every time it gets updated (i.e into A1,A2,A3,A4.....and so on), I want to read the new value each time in B1.
View 9 Replies View RelatedMy question is a display/ chart format question. I need to convey what I am showing on this chart in a less "busy" fashion. Is there a way to display the data in an easier to read and see format? In particular, is having both averages on the chart in the same format an issue?
View 2 Replies View RelatedHow can I avoid that an empty series shows up in the legend of a graph?
View 9 Replies View RelatedI would like to display the latest data label value for a data series So if I update my table for period 2 the chart should update to display only this value. Then period 3 and so on Workbook attached
View 3 Replies View RelatedI need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
P.S. I have excel 2010 and windows 7
I have a problem where I am linking data from two worksheets say: worksheet 1: A1 = 10, A2 = 20 worksheet 2: A1 = =Sheet1!A1, A2 = =Sheet1!A2
I have filled this formula through a certain number of cells on sheet 2 so that if i add more data to sheet1 it will appear in the appropriate place on sheet2. Is it possible to show the formula results in the formula bar, i.e. sheet2: A1=10, A2=20, such that i can used 'Find' or 'Autofilter' on the cell values rather than the formula?
Am using Excell for some times and understood the basic operations like VLOOKUP and other formulas, and it was really usefull. Now i have another requirement where i need to get all the values matching from another tab.
Below is the example: I will be entering all the values in Tab 2 manually. As well as i will be entering the Categories (Column A) in Tab1. I want the Values (Column B) in Tab1 to be populated.
TAB 1CATEGORY
VALUES
AA1
1
2
AA2
5
BB1
3
BB2
4
6
TAB 2
CATEGORY
VALUES
AA1
1
AA1
2
BB1
3
BB2
4
AA2
5
BB2
6
I have 2 problems in that
1) It is not dynamic, i have to change Tab 2 frequently.
2) values are listed below the categories
I would like to have a dialog box "popup" if the value of two cells do NOT equal.
cell C11 has net sales values for the day, including cash, checks, and credit cards. After sub total of these values, I subtract any charges(voids) and get a total(c11). I enter a sales(c12), tax(c13), no tax(C14), misc. amounts(C15 & C16) ,reductions(c21) and put the total in Cell C22 with the formula =Sum(c12:c16)-c21). Because I do not want the formula overwritten, I protect the sheet including the amounts in cell c11 and c22. If they do "Not" match I would like a dialog box to appear so the error can be corrected.
Triggering a message box. one of the worksheets in my workbook is called Update Comments - this is a sheet that contains data in the following format (headers)
B7 = Week Number
C7 = W/C
D7 = Update Due
E7 = Updated By
G7 = Update Comments
I have a formula in column D (beginning D8 and copied down for the year) as follows:
=IF(AND(C8
is there a way that i can insert cell values in a userform
i have data which needs to be displayed on 33 lines
line 1 cell value from d10 then space cell value from d185 then space cell value from d186
this needs to be repeated
line 2 cell value from e10 then space cell value from e185 then space cell value from e186
line 3 cell value from f10 then space cell value from f185 then space cell value from f186
line 4 cell value from g10 then space cell value from g185 then space cell value from g186
this continues trough to line 33
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
[Code] .....
I am trying to create a formula to display a string of text that refers to multiple cells. for example, =If(A1=0,"insert text here" &B1 "insert more text here" &B2 "insert even more text here" &B3, "insert text here" &C1 "Insert text here").
It works fine for one cell value, like ="Total: " &A1
I have a VBA module in Access that extracts data and copies to an Excel spreadsheet. Every time I run the code additional data is extracted and displayed on a chart together with previous data. When I enter values to the series collection the first axis be it X or Y is entered correctly but the second axis produces error 1004 'unable to set XValues property of the series class' If I swap the lines of code around the error switches to 'Values' (y axis)
wchart.SeriesCollection(NoCurves).Values = wsheet.Cells(9, 3 + (3 * (NoCurves - 1)))
wchart.SeriesCollection(NoCurves).XValues = wsheet.Cells(8, 3 + (3 * (NoCurves - 1)))wsheet.Cells(a, b))
wchart.SeriesCollection(NoCurves).Name = wsheet.Range(Cells(6, 2 + (3 * (NoCurves - 1))), Cells(7, 2 + (3 * (NoCurves - 1))))
I have a column:
LABEL COUNT
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 16
15 empty cells
I need to fill the empty cell with the "series" (2 of 2, 2 of 16, 3 of 16). This is just an example of data - there are all different series - 1 of 12, 1 of 5, etc.
Anyway - was hoping to highlight the whole column and be able to auto fill instead of having to extend each series. I have tried straightforward fill series which doesnt work of course. I have tried go to special - blanks but cant find right combo to make it work.
Will it work or do I have to either do manually or with a macro?
I have a series of tabs in my sheet, the first one is called 'START', the last one 'END', and between them a series of other identical sheets ('Division 1', 'Division 2'... etc). In each of the 'Division' tabs, cell A1 (say) contains a string that I need to retrieve and order into a list on a summary sheet. The order doesn't really matter (i.e. it doesn't have to be alphabetical or anything), though it would be useful if there were no gaps in the list.
View 1 Replies View Relatedwhat I am trying to do:
I have a workbook with 2 sheets.
Sheet "A" has a column called "FILEPATH" that contains the complete path to a file (eg. "C:mystuffdocumentsdocA.pdf"). There are about 3500 rows of filepaths on this sheet.
Sheet "B" has a column called "DELETED FILES" that contains a list of deleted filenames (eg. docA.pdf, docC.pdf, etc.), one filename in each cell.
I want to highlight the rows on Sheet "A" that contain a path to a file that is listed as deleted on Sheet "B".
I have a series of 12 monthly data values:
1/31/19990.155375
2/28/19990.179262
3/31/19990.179392
4/30/19990.178993
5/31/19990.17128
6/30/19990.139678
7/31/19990.15518
8/31/19990.15536
9/30/19990.17732
10/31/19990.117725
11/30/19990.134171
12/31/19990.149113
and also a series of the 365 days that comprise these 12 months.
I need to populate each of the 365 days within this period (1/1/1999-12/31/1999) with its corresponding value for the month, i.e. 1/21/1999 (and all days in Jan) will take 0.155375 and 5/4/1999 (and all days in May) will take 0.17128.
How I should set out my data and which formulas I need to use?
I still get the 'Object doesn't support this property or method. Error code 438' error. I am using Excel 2010
VB:
j = 3
Do Until j = 6
ActiveWorkbook.Sheets(k).Activate
Set chartX = ActiveSheet.ChartObjects("X")
[Code]....
The macro is supposed to dynamicaly adjust series range for 3 charts for x,y,z, values in each of sheets. Charts are a line type.
I would like to multiply a serie in 'series values' (before presenting on a chart) with a factor (in a formula like ='[1]name'!$V$2:$V$148)
in other words I would like to do something like
=('[1]name'!$V$2:$V$148)*50
but I don't seem to succeed with the correct syntax.
In Excel searching for "multiply series values" under "edit series" there is to read:
"Use this option to include additional data series on the chart or to modify the name and values of existing data series without affecting the data on the worksheet."
So what I want to do seems to be possible.
Excel 2007 version 12.0.6611.1000
I know I can create a temporary column with the multiplied values but THIS IS NOT the way I want to solve the problem.
I'm having some problems with the removal of duplicate values in a time series. The time series consist of stock data for about 10000 firms, but about 3000 of these firms died(delisted) before the end of the sample. The database I used to extract these data, however, does not remove the firm values from the sample, but keeps the last known value of the firm for the remainder of the time. Therefore, I need to remove these duplicate values, but only if they are sequential. Furthermore, normally if you remove duplicate values, excel will delete the cell and shift the data upwards, since it's a time-series I would rather have excel just blanking out the cell instead of removing it.
I've looked at normal solutions, like using the countif functionallity in excel, but this is not feasible because of the size of the worksheet, also, it does remove non-sequential duplicate values as well.
summary:
- Want to remove duplicate values, but only sequential values.( It's possible firm value is the same somewhere in the time period)
- The cells should be blanked out instead of deleted.
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.
The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.
Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.
I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:
I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)
How do I write that kind of formula?
I would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.
The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.
Excel version: Excel 2010
OS: Windows 7
