Retrieving And Listing Values From Series Of Tabs
Jul 31, 2012
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
ADVERTISEMENT
Feb 17, 2010
Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.
Example:
On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.
I have attached a sample spreadsheet for clarification.
View 7 Replies
View Related
Apr 14, 2014
I have created multiple nametags. I want to use a button which when pressed will run a loop to extract each individual element from the nametag and assigns a value to cell based on that. I have coded for the other functionality that happens when the cell value is changed but can't figure out a way to run the loop.
View 2 Replies
View Related
Feb 28, 2014
To establish the order for on duty personnel every month, I need to find a proper formula.
I have attached the workbook.
So, when I pick out a month from a drop list in cell A1, in column B returns (based on a formula) net work days of that month.
In range H:T, I have a table with the personnel names (in the header) and the individual holidays.
What I want is to have a formula in column C, that returns the name of the first available person (not on holiday in that day), in exactly the same order as it is in the table's header.
Attached File : ON DUTY.xlsm
View 5 Replies
View Related
Jan 20, 2009
I'm using this code to input values into the next available row of columns of A and B.
Range("A65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Textbox1
Range("B65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Textbox2
I have this piece of code right under it:
Range("E65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Range("B65536").End(xlUp).Offset(1) - Range("A65536").End(xlUp).Offset(1)
The value being returned is 0.
What I want that last part to do is take the last row of column B and subtract the last row in column A.
View 9 Replies
View Related
Aug 9, 2007
I have a two dimensional database from which I need to pull information based on multiple listbox values (these listboxes are located in a userform). The user selects one or more cars and then chooses from various parameters for that car. The output has to show the results - for example, if Jeep Cherokee and mpg are chosen, the output will be 23 mpg. I need to be able to do this for multiple cars and multiple parameters.
I need for the outputs to be located in a worksheet. I already have the code for setting up the list of cars and parameters selected (this gives me a row and column header), but now I need to be able to cross reference this information to pull the output from the master database. I was wondering if I can use a vlookup function to do this, but I've never used it with 2 dimensional data.
View 14 Replies
View Related
Jun 26, 2012
I am trying to use a lookup to find the quantity of a certain fabric. However, more than one style uses the same type of fabric. Is there a way to do a lookup that retrieves multiple values?
View 1 Replies
View Related
Jul 7, 2009
How can I add an array of values to a listobject, preferably in one big chunk? How can I read a chunk of values from a listobject into an array?
For the latter, I've tried:
View 2 Replies
View Related
Nov 2, 2008
Below is what I currently have on my excel spreadsheet:
1.
2. Input Column 3
3. 0 No
4. 2 Yes_2
5. 0 NO
6. 0 NO
7. 0 NO
8. 2 YES_6
9. 0 NO
10. 1 YES_8
11. 0 NO
The spreadsheet starts by the user inputting numbers ONLY in column 1 (under input). The rest is done automatically by implementing equations. Column 2's equation is easy, if the value of column 1 is greater than 0, return YES_(row number). In addition, the maximum number of Yes's in column 2 is three (constraint).
My problem is that I need a 3rd column, consisting of only 3 rows that search for YES_1, YES_2, and YES_3. The 3rd column's equation is easy, I will simply use the Vlookup function for YES_1, Yes_2, and Yes_3. Therefore the PROBLEM IS: I need column 2 to output YES_1 (in row 4, from the example above), YES_2 (in row 8) ...etc. This would mean chaning the basic IF equation that I have which simply sees if the value in column 1 is greater than 0 then outputting "YES_the row number".
View 3 Replies
View Related
May 27, 2009
I have a set of data that I'm trying to identify the unique values in a column and then sum the related quantites against each of those values:
View 14 Replies
View Related
Feb 4, 2010
I am setting up a questionnaire which needs to be dynamic. The questions in the questionnaire are pulled from a 'baseline sheet' using check boxes and an if statement, i.e. each question in the baseline sheet has a tickbox next to it, and if it is checked then the question is populated into the questionnaire sheet.
Im wondering if there anyway for the worksheet to populate the input cell with the next cell value from the baseline sheet if the first is blank? So if a box is not ticked, it will intelligently seek out the next box that has been ticked and retrieve that value. Essentially, my goal is to not have any blank spaces in the questionnaire which is what is happening now.
View 4 Replies
View Related
Jul 5, 2014
I have a table listing certain values (Column C) and their respective probabilities, based on normal distribution (Column D). As these probabilities are dependant only on values from Column C, I'd like to list all the values from that column into another fragment of the sheet, say F6 and below, but each value should appear there only once, no matter how often it occurs in its original Column C (I listed them manually in Column F in the enclosed example). Moreover, if a new value appears in Column C, it should be also included in the new place and sorted in increasing order.
View 7 Replies
View Related
Mar 31, 2014
So I have 2 sheets from a much larger worksheet where I wish for the first sheet to extract a row of values, one at a time from the second sheet using a range of numbers I enter as reference for where to look for the data. Sheet 1 can be thought of as a summary page and Sheet 2 is where data is stored.
I need to first check if any data under Sheet 2 column B fall within a specified range of numbers and if any of those numbers are found I want it to grab the largest and latest number from that range and pull all the data from certain columns in that same row and place it into Sheet 1, one column value at a time.
Sheet 1 A1 = A range of numbers as text. 10-15, 16-20 etc Only one range is entered. This also tells me what results I'm looking at.
Sheet 1 B4:B14 = Destination cells for the individual data I'm wanting to pull from Sheet 2 columns H:P. B4 wants H, B5 wants I, B6 wants J etc
Sheet 2 column A = A3:A102 are numbered 1:100.
Sheet 2 column B = A series of numbers anywhere from 10-70. Always in sequence and they can repeat. 10,10,11,12,13,14,15,15,15,16 etc. Ranges from B3:B102. Only one number per cell.
Sheet 2 columns H:P = Data in H3:P102 I want to extract to Sheet 1 B4:B14.
Now lets say I want a formula for Sheet 1 B4 which wants a value in Sheet 2 H. If Sheet 1 A1 = 10-15, I want to check whether Sheet 2 column B has any values equaling those and then tell me which row that last number appeared in.
Example: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5. (B6 is outside range and B4 isn't the last time the 14 appears.)
Then, knowing B5 is the value I want, find which row it is in (row 5 in this example) or use the number in A5 (3) and then find my way to column H (H5) where the value I want to pull is.
Example 2: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5.
Sheet 2 H4=10, Sheet 2 H5=32, Sheet 2 H6=42. Place "32" from H5 into Sheet 1 B4.
I'd like to also have some error control so I'm not trying to pull data from blank cells if it's relevant. Maybe check if Sheet 2 X3=0 and if it is, do nothing as no data appears if the cell is 0.
I have put a lot of time into trying to solve this myself but I feel way out of my depth. I've tried going step by step but I can't seem to figure out which functions are relevant and also things like how to return the range that the A1 values appear in or if using MAX, not having it return values outside of A1's range also.
View 7 Replies
View Related
May 28, 2008
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
View Related
May 19, 2013
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))))
View 2 Replies
View Related
Feb 26, 2013
I trying to use the formula below to add values across multiple tabs but I am getting a value error. I think it is just a small punctuation thing.
=SUM(INDIRECT("'"&REF!$A$2:$A$3&"'!&$F$62"))
REF is the name of my references tab and has a list of the tab names
View 1 Replies
View Related
May 9, 2012
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?
View 2 Replies
View Related
Aug 19, 2009
what 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".
View 9 Replies
View Related
Jan 23, 2014
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?
View 8 Replies
View Related
Feb 10, 2014
I have a large spreadsheet and want to sort by Column "L" and copy all the rows where Column L has the same value into a new tab named with the value in Column L.
View 2 Replies
View Related
Sep 9, 2009
I need to create a chart that will show month over month sales for different channels. But each month total is in a separated tab in the same file.
Example:
August totals
Channel Sales
Channel 1 $1000
Channel 2 $2000
Channel 3 $500
I need a chart that will show sales for Channel 1 ,2 and 3 from jan to aug.
View 8 Replies
View Related
Jul 6, 2014
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.
View 3 Replies
View Related
Jun 4, 2014
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.
View 6 Replies
View Related
May 22, 2014
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.
View 2 Replies
View Related
May 22, 2014
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 Related
Aug 4, 2014
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] .....
View 1 Replies
View Related
Dec 22, 2011
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.
View 4 Replies
View Related
May 31, 2008
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?
View 4 Replies
View Related
Feb 7, 2014
I have never written a macro and when I record one I usually have trouble with the relative references.
I am trying to write a macro to transform formulas into values every month.
I want to transform formulas:
- across a range of tabs: each tab is exactly alike and is named page-1 to page 25
- on a different column every month (same column across all tabs)
- on the same rows: L168 to L227 and L266 to L277 (same rows across all tabs)
View 6 Replies
View Related
Aug 21, 2006
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 ............
View 9 Replies
View Related
