Choose A Specific Cell
Jan 8, 2008
I have an Excel file with a column a with names and a column d with numbers. Another Excel file needs the numbers from column d, but only always from the first two alphas, the first two betas, the first two deltas and the first two gammas.
The problem is that the amount of alpha, betas, deltas and gammes vary each month so i cannot choose a specific cell because this will change.
Column AColumn D
Alpha 100
Alpha 200
Alpha 300
Beta 400
Beta 500
Beta 600
Delta 700
Delta 800
Delta 900
Delta 1000
Gamma 1100
Gamma 1200
View 9 Replies
ADVERTISEMENT
Feb 6, 2009
The choose function only works with separate values, but not with a range.
I'm looking for a formula that returns the value in a range based on a number.
e.g. CHOOSE(5, A1:A10) should return the value of A5.
I can program it as a function, but I expect there is a standard formula for this.
View 2 Replies
View Related
Apr 23, 2013
conditional Macro or filter? Easy to record a Macro which filters, but choosing what (column) to filter based on values of a cell I'm not sure how to go about that. It means implementing IFs into a Macro, or..?
Data on "Staff List" sheet I have a list with all staff details including availability. I need to filter their availabiltiy and copy values over to another sheet. The availabiltiy shows Monday to Sunday and states for which of our offices they are are available. Here the columns BS to BY
Available Location Monday
Available Location Tuesday
Available Location Wednesday
[Code]....
Now I need the Macro to choose which column to filter based on the office set in cell C3 on the "Availability" sheet. And then filter that column for YES.
View 9 Replies
View Related
May 2, 2008
I am attempting to choose a cell containing a text string based on information placed in a raw data area.
My output from this formula will be a line of text picked from yet another reference cell, based on each of the four choices.
Removed non pertinent information and edited for clarity.
View 3 Replies
View Related
May 28, 2014
I have this issue where I get an #N/A error when I i run a VLookup in cell H7. I have posted the worbook.
I want to find the value in column D for the value you choose in cell G2
Excelforum.xlsx
View 4 Replies
View Related
Mar 3, 2009
I'm trying to create a lookup formula where it matches the beginning of words and returns the value of a cell I choose. This is my layout. In Workbook1 in column E I have a list of medicine names which i'm putting in from personal medical lists. These names are not "complete", i.e. they aren't the exact names of the medicines. In workbook 2, I have a sheet with the exact names and corresponding codes of the medicines. I want to return the code of the medicine. The beginning of the names of the medicines are the same, so I want to lookup the the X first letters of the medicines I write in manually (Workbook1 column E) and find it in Workbook 2 column B and return value of Workbook2 column A for that medicine (on the same row).
View 5 Replies
View Related
Mar 22, 2009
I am curious can we chose random number out of numbers if they are in one cell separated by coma
Example:
*A111, 12, 14, 18, 19
Is there any formula so Random number can be selected from cell A1
View 9 Replies
View Related
Jan 20, 2009
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
View 3 Replies
View Related
Apr 16, 2014
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
View 2 Replies
View Related
Nov 7, 2008
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
View 9 Replies
View Related
Apr 4, 2013
Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?
This has seemed to have happened on a computer at work.
In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"
If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.
View 6 Replies
View Related
Jul 1, 2014
Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.
Code in plain english would look like this:
For EACH cell in range A1:BZ500 meeting the following criteria:
Cell value is a date
AND
Cell's date is at least a week or more in the future
AND
Cell background (Fill) = RGB color code: (R:191 G:191 B:191)
DO the following:
Call repeatingsub
View 11 Replies
View Related
Sep 14, 2008
I'm trying to search through multiple worksheets (that are closed) to see if a value in cell B12 (of every worksheet) matches a value in a seperate worksheet (which is in a seperate workbook)
View 14 Replies
View Related
Aug 14, 2009
let say in cell A6 i have a list
SnG
Cash
MTT
I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.
So in cell A6 is the list.
in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.
But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this
=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
View 12 Replies
View Related
Aug 5, 2013
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies
View Related
Mar 3, 2007
I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.
View 3 Replies
View Related
Jan 13, 2009
I have created a form where people will take a survey, and the responses from that survey will be carried over to another worksheet that will analyze their results.
For each question in the survey, I want them to choose only 1 of 2 responses. For example:
Place and X next to the statement that describes your opinion most often:
__ I prefer to work with others.
__ I prefer to work alone.
The responder will choose either the first or second response, but I do not know how to limit it so they can't answer both. I want a way to limit the worksheet so if they type an X in the first field, they are unable to type one in the second field, and vice versa.
Is there any way to limit the responders ability so that if they choose to respond to the first statement, they are not able to also respond with the second statement, and vice versa, while not affecting the results page that uses the IF formula =IF(sheet1!A8="X","X","")
View 4 Replies
View Related
May 2, 2013
Any good formula for the following:
Item
Value
Result
[Code].....
The formula needs to first look at each item, select the max value in each group (there are three in the "1" group and four in the "2" group), then return ONLY one result per group. The problem I am having is only returning one value per group. I can write an array formula that returns 1200 or 1000 in each group in every row, but I need to only return one value.
View 4 Replies
View Related
Jul 18, 2014
I downloaded a dashboard template that has two tabs of data and I would like to add additional tabs.
I would have to modify the IF and CHOOSE functions?
Formula view:
Capture.jpg
Here's the pretty view:
Capture2.jpg
View 11 Replies
View Related
Oct 1, 2008
how I can make a calendar that pops up when the user mouse clicks or tabs/arrows over to a cell so they can choose a date instead of manually typing in the date?
View 6 Replies
View Related
Dec 30, 2008
If you look at "B9" I have created a list of names and when I choose a name their ot hours appear in "K9". Now when I try to do the same in "H9" to bring back there qualification it gives me an error. I also tried to do the same in "M9" for the phone numbers and it didn't work.
View 7 Replies
View Related
Nov 3, 2009
I've different rating matrix as below;
more than 90% - " Excellent"
80% to 89% - "Very Good"
65% to 79% - "Good"
below 64% - "Low"
hence if any of the rating falls in a cell need a formula to corresponds that (lets say in cell "A1" if the rating is 85% then result should be "Very Good")
View 9 Replies
View Related
Mar 14, 2012
I'm trying to count the number of times "Y" occurs in column H and one of four values occurs in column B. I'm new at writing arrays and what I have so far is:
{=SUM((Main!$H$4:$H$700="Y")*OR(Main$B$4:$B$700="FGZ","FHZ","FLZ","NAV"))}.
This is returning a #VALUE error. A
View 5 Replies
View Related
Apr 18, 2013
i have a column of data, and from that list i want to choose the lowest value grater than a reference value.
for example
the list: 1 2 3 4 5 6
the reference: 2
what function should i use to get the lowest value grater than 2?
View 4 Replies
View Related
May 8, 2013
I have two formula's available for a cell. Its actual value, and a previously forecasted value. The actual value is found using an index formula and the forecasted value is a forecast function. i want to create a macro and link it to a button such that i can change which formula used in the cell accordingly. Is this possible?
View 1 Replies
View Related
Feb 9, 2007
"A1" has a number containing "1000"
I want column "B2" to choose the next number in sequence,(1001) so long as "A2" contains these letters "EHT". if it does not contain "EHT" i dont want excel to insert the number "1001"
But if cell A3 contains, "EHT" I want the next number in sequence to be instered automatically... (1001, 1002, 1003")
View 9 Replies
View Related
Feb 15, 2007
how you know which object library to add to references when you want to automate an application?
for example Adobe acrobat.
or internet explorer ( ie).
I know that the .dll for ie id shdocvw (an i know it's explicitly listed under internet controls) but how would i know this is it wasn't listed?
Also, correct me if i'm wrong, but usung the shdocvw.dll will only give you access to the main controls of Ie nd you would need to add a HTML library to do anything use full. How do you know if there are other libraries available can make the 'usefulness' of one library more useful?
View 9 Replies
View Related
May 8, 2006
I am putting together a formula that will be able to choose the calculation. There is a couple of choices with the spreadsheet registry, non registry, and interfile. They have different standards per hour 56, 40 and so on. What I have so far is not working. It is =volume/(time*standard),Volume/(time*standard) with each standard being different to bring the correct percentage. How can I put the two to three formulas in one cell
View 2 Replies
View Related
Mar 17, 2007
I have 8 values in a drop-down list that I've created with excel " Validation" function. Each one of these values changes my data in the sheet. I'm trying to set up a macro that will choose each one of these values and print out the sheet when each new value is chosen.
View 3 Replies
View Related
Apr 24, 2007
I have = LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(C3,{1,2,3},{2,1,0}) )) in cell D3. C3=1 so I expected a result of D3=2. Instead I am getting a blank cell.
View 4 Replies
View Related
