I need to obtain a range of cells starting on cell B10 to the end of records (vertical)
I need to do two things with this:
1) Count the number of records from cell B10 to the last row populated with data
2) Format the cells in this range
FYI - "R" is always the last column.
I've got a problem where I need to assign values to ranges between 0 and 3.
Eg.
RANGE VALUE
2-3 2.2
1-3 1.6
0-3 0.8
0-2 0.5
0-2 0.5
0-1 0.1
0-1 0.1
0-1 0.1
0-1 0.1
The values above are simply approximates done manually but I need it done automatically using a formula. The resulting values should be proportioned similarly to the ones above but they need to all sum up to 6 and be no more than 3 each.
I have a sheet with 5000+ entries, column A is an ID number, column B has one of 4 outcomes C,D and E are just times and quantities and are not important.
In column A there could be multiple entries of the same ID number.
In column B it will show Pcid243A, Pcid243B, Ddar400A or Ddar400B.
I need to export (to another sheet) any entries with the following conditions:
A = ID number with any instance in B showing both a 243 and 400 condition.
E.G
A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E
In the example above, I'd need the red cells exporting to a new sheet as they meet the conditions of having the same ID and both a 243 & 400 outcome:
A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E
Now I've spent ages trying different things and can't get anything to work...
I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...
Jan
Feb
Mar
Apr
May
Jun
Team 1
25087
23029
32991
21319
25271
25136.8
[Code] .........
I have two worksheets, sheet 1 and sheet 2. On sheet 1, I have a information on two columns (Column A and B) Column a has information up to row 10 (aaa, bbb, ccc, ddd, ...). On column B, I have 111, 222, 333 . I have same information on Sheet 2. However, sheet two has an additional column (Column C) with 10 rows on information z, y, x, w, v ...
What I need to do is to match column A and B in sheet 1 to column A and B in sheet 2 and if both columns matches on the same row, copy the value in sheet 2, column C and paste it on sheet 1 column C.
Important: Information on sheet 2, column B may be twisted (As they may not appear exactly the same as in sheet 1. Which means 111,222,333 can be 222,333,111 along the column). Therefore, need to match the entire range.
I have a listbox (form control) on an excel sheet, where the user can select (or deselect) multiple entries.
Is there a way to have vba spit out which options are selected? if I choose "single" for selection type, it will tell me which option i have selected. Is there something similar for multiple?
I am going to create a large drop down list with data validation the list is to do with fitness the list will containt a sport with a numeral after it
for example : GOLF 4.5 then under that Basketball 12
what i am wanting to do is make a calculation based on what sport is selected in that cell for instance GOLF 4.5 is selected in the cell so i need to say valueinA43*D43/B9 can this be done?? Or if not. can i extract the value from the list to another cell without the text and then perform the calculation.........
I have a large number (couple of hundred) pdf's in a folder for electrical test certificates. The pdf's are in the format 54 Pike Drive.pdf etc. and have the date modified.
Ideally, as well as the address I would like the date modified as this is pretty close to the date of the certificate so will suffice.
Failing the date, just the addresses will do which need to go on separate rows in Excel.
I've tried copying and pasting the files but to no avail.
I use the following fomula to test and advise of an upcoming birthday:-
=IF(AND(MONTH(TODAY())-MONTH(B6)=0,DAY(B6)-DAY(TODAY()) 25.
I have a spreadsheet with macro's in and everytime the user opens it that annoying security box opens and asks to allow/disable macro's. I believe it is due to a digital certificate. How to i obtain one, and if i do get one will it stop thie popup occuring for other users on different computers or just me????
View 2 Replies View RelatedI'm working on a program, which gives me interpolated values of z from a digitized graph based on the cursor position or input values of x and y. Thing that bothers me for quite some time is how to get properly interpolated z value. I was using SADA (spatial analysis software), where I was creating a 100x100 matrix, but the interpolation methods there are not as accurate as I need.
Basically, from digitizing a graph I get a matrix:
VB:
x1, y1, z1;
x2, y2, z1;
x3, y3, z2;
x4, y4, z2;
.
.
.
x3742, y3742, z24
Now, inputting x and y that are not in the matrix should give me interpolated value of z, which gets even more tricky when the axes are in log scale. The only idea that I have in my mind and that I think would work is this:
for each missing z, from zmin=0,625 to zmax=3 , every 0,001, I would make 1000 (enough accuracy in this case) xs and ys. How to automate the process.
I have 2 worksheets with the below data.
Sheet 1
Part Number Client X Client Y Client Z
127-003104-03 $38,835.00 $27,756.75 $38,835.00
127-002114-03 $31,600.00 $27,037.50 $31,600.00
127-010113-01 $25,000.00 $24,270.00 $25,000.00
127-001113-03 $25,000.00 $24,270.00 $25,000.00
138-001113-01 $27,900.00 $26,790.00 $27,900.00
Sheet 2
PurchaserPart # Outward Rate
Client X 127-002114-03
I want a formula for "the Outward rate" which will check the 2 criteria of the client name and product code and return the unit price for the product.
so basically, im trying to find a way to get cell G3 to have Value X dependant on value from Cell G2. e.g. if cell g2 is 8 and 9, g3=10 if g2 is 11, g3=20 and so on. i dont mind having to add a preset table of values if we need, but it would be set on sheet 2.
EDIT
Value from cell G2 will always be between 4 and 20, set by the user. what i want is that each of those values gives G3 a pre-planned value.
if g2 has value 4. g3 value would be -5
if g2 has value 5. g3 value would be 0
if g2 has value 6. g3 value would be 5
if g2 has value 7. g3 value would be 5
if g2 has value 8. g3 value would be 10
if g2 has value 9. g3 value would be 10
if g2 has value 10. g3 value would be 15
if g2 has value 11. g3 value would be 20
if g2 has value 12. g3 value would be 20
if g2 has value 13. g3 value would be 25
and so on until it reaches g2=20
i was thinking of setting a table on sheet 2 for the g3 values, and get an IF function to do something like IF g2 = a then x, if g2 = b then y etc etc. im just not sure how it would work, unless an INDEX function would work better?
Is it possible to obtain the numerator and denominator from a fraction?
For example, I entered different numbers and display them as fractions in the cell using the format "?/?"
Is it possible to obtain the numerator and denominator from that cell and place them into a different cell?
ex: In cell [A1] is the value "1/4"
can i take the numerator "1" and place it in cell [A2] and the denominator "4" and place it in cell [A3]
By the way, I've tried using the functions LEFT and RIGHT but they display the value in it's decimal form. (example: RIGHT(A1, 2) displays the number "25" from 1/4=0.25 as opposed to "/4"
I have the following snippet of -
Worksheets(2).Cells(20, 3) = "=IF(C29=""09"",""09"",IF(C28=""08"",""08"",IF(C27=""07"",""07"",IF(C26=""06"",""06"",IF(C25=""05"",""05"",IF(C24=""04"",""04"",IF(C23=""03"",""03"",IF(C22=""02"",""02"",""00""))))))))"
which works. However I want it to be: -
Worksheets(2).Cells(20, 3) = "=IF(C29=""09"",""09"",IF(C28=""08"",""08"",IF(C27=""07"",""07"",IF(C26=""06"",""06"",IF(C25=""05"",""05"",IF(C24=""04"",""04"",IF(C23=""03"",""03"",IF(C22=""02"",""02"",IF(C21=""01"",""01"",""00"")))))))))"
but I can't because I can only have 8 nested IF's.
Is there an alternative way of obtaining the last line of code?
I want to automate the process of updating my football stats and have delved into getting my data from the web. I have managed to import a football league onto my "standings" sheet but I want my "strengths" sheet to read it's values from this new "standings" sheet.
The "standings" sheet is broken down into 2 tables (home & away) one above the other.
The values I want are Home Games, Away Games, Home Goals For, Away Goals For, Home Goals Against And Away Goals Against from both the home table and the away table. The main problem I have is that the cell information I need is likely to change as I update the league tables from the web. So say one week Team A's data is in row 7 the following week it could be in row 8 so i need the data to correlate with the team name cell in my "strengths" sheet.
So at the moment Atromitos Home games (cell B8 on my strengths sheet) needs the value from cell C8 on my "standings" sheet but by next week it could need the value from C9.
Ps I am assuming that I can just just hit the refresh button on the ribbon and the data from the web site will update. Is this correct?
I want to know how to link te two lists in my register so that when i select a particular route in one column the corresponding column automatically selects the transit time from the reference list.
View 2 Replies View RelatedI am familiar with the use of the INDIRECT function to retrieve data from different sheets in a workbook. However, is there a simple way of obtaining a list of all sheet names in a workbook (I have about 50) rather than typing them into the sheet individually?
I can see them in the workbook properties but can't copy and paste them.
very basic Excel user (using Excel 2007). Trying to see how I can use the VLookup function from multiple worksheets to get an average. I know it can be done, but just not sure how to go about doing that. My spreadsheet is attached - basically what I want to do is to get an average for the individuals listed in the "Consensus" tab for the figures that appear in Worksheets "1", "2" and "3".
View 14 Replies View RelatedThe function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
i have a workbook with two sheets. lets just call them sheet 1 and sheet 2. on sheet 1 i have data for employees and their current wages and other info. on sheet 2 the data is for compensation scale on three separate columns.......
in order for me to automatically get the data from (sheet 2 B3) the formula for sheet 1 E1 would be: ='Sheet 1'!B3. how do i formulate the equation so that i can do ='Sheet 2'!(C1)(D1)?. in other words i want to specify the column and row from the values declared in sheet 1 column c and column d respectively.
Trying to assign code to comm. button on User form to copy lets say:
(sheet1, rangeB2:B21) to (sheet2, first blank row rangeB2:B21) and paste it as text value one more question: what to be aware in case of sheets format (merged cells, hidden rows...)
Need a code using application.inputbox to get a range, then use that range to copy and paste the range's link and format to a different sheet? The specifics don't matter, I just can't figure out the syntax. Here is what I have currently:
View 2 Replies View RelatedI'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies View RelatedI need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:
{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}
The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.
So my formula looks like this:
{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}
This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.
Here's what I have as start info and where I want to get to.
Sheet1
A
B
1
Ranges
[Code] ......
Excel 2003
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
[Code]....