Choose Cell In Range

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


Let User Choose Column Range

Aug 28, 2007

I 'm trying to do one macros where the user has to choose the column where he wants to work, to avoid the user start counting the number of the letter that corresponds in the alphabet, I would like to make it easer, because working with column numbers when programming is easier, but in terms of user is easier to work with letters

View 8 Replies View Related

Allow User To Choose Range For Chart

Apr 29, 2008

I want to make a chart with the time in x axis and all of the S in the y axis. The problem is I want to be able to choose which S that I want to put in the chart. Example, I want to make 2 chart with S2,S3 and S5 in the first chart and S1 and S6 in the second chart. What I want is when I click the button 'chart', a pop-up appear and ask which S that I want to include in the chart.

View 7 Replies View Related

ChoosE Multiple Values For The Second Condition In The Range

Feb 12, 2009

I am having problems with choosing multiple values for the second condition in the b range. The formula works for one account in the array, but not multiples.

=SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--('Data Sheet'!$B$2:$B$997={"300100","302100"}),'Data Sheet'!D$2:D$997)

View 3 Replies View Related

AutoFilter - User Can Choose Range And Input From / To Date

Mar 19, 2012

I have a work book.

In column C27 and down, the user can input a date.
In column M27 down, the user chooses pass or fail.

N8, contains a date chosen by user as the "From" date and P8 the "to" date.

Cell o11 is "Passed" and cell 012 is "failed"

The user can choose a date range and input the from and to date in N8 and P8, this will count the number of pass and fails and input the number in O11 and o12.

Formulas are below.

=COUNTIFS('Aff MFR'!C27:C1663,">="&'Aff MFR'!N8,'Aff MFR'!C27:C1663,"="&'Aff MFR'!N8,'Aff MFR'!C27:C1663,"

View 1 Replies View Related

Choose Selective Data From A Range Driven From Dropdown List?

Aug 23, 2013

I have a spreadsheet listing various pieces of computer hardware, software and mobile accessories, etc with costs attached.

I have created a dropdown list of staff profiles eg a sales representative, finance assistant, IT administrator.

What I want to do is get Excel to pull the data through from the data sheet, to a printable summary output form (something you could also cut and paste from). For example, a sale rep would need to be set up with a laptop, laptop case, mobile phone, etc, so when you select Sales rep from droplist, I want that action to pull all the associated data through to the summary form. Similarly, a Finance assistant would require different kit , perhaps a desktop PC , monitor , etc.

Next to my data I have created columns with "Y" for yes in cells containing kit that match the staff role requirements, but I am not sure of the best way to pull this together.

View 3 Replies View Related

Excel 2007 :: Choose Date From A Range Of Dates In Column On Sheet

Feb 6, 2012

Excel 2007 Q: I have a combo box (ActivX) that I want to be able to choose a a date from a range of dates in column A on sheet 'Working Copy'. I want a cell to update with the number/position of the date when the date is chosen i.e. the first date in the range yields 1 the second date yields 2 and so on. I also want the date to be displayed in dd-mmm-yyyy format. I am able to get the cell to give the correct position of the date but the date reverts to five digit number in combo box. I can change the code to return the correct date in the combo box when the date is chosen but then the cell just returns 0.

How can I make it so that the combo box returns the correct date and the cell updates with the position instead of jsut one or the other?

Here is the code I am using

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Text, "dd-mmm-yyyy")
End Sub
Private Sub ComboBox1_Change()
Sheets("Working Copy").Range("G1").Value = ComboBox1.ListIndex + 1
End Sub

View 1 Replies View Related

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 View Related

Choose Which Column To Filter Based On Cell Value?

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


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

Choose & Return Cell Based On Text In Another

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

Lookup Function - Find Value In Column For Value Choose In Cell

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


View 4 Replies View Related

Lookup Beginning Of Word And Returns The Value Of A Cell I Choose

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

Choose Random Number Out Of Numbers If They Are In One Cell Separated By Coma

Mar 22, 2009

I am curious can we chose random number out of numbers if they are in one cell separated by coma

*A111, 12, 14, 18, 19

Is there any formula so Random number can be selected from cell A1

View 9 Replies View Related

Choose Only 1 Of 2 Responses

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

How To Choose Only 1 Value In Array

May 2, 2013

Any good formula for the following:



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

Add New Tab Within Workbook Containing Dashboard Using CHOOSE And IF

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:

Here's the pretty view:

View 11 Replies View Related

Pop Up Calendar To Choose Dates?

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

Choose A Name Based On Hours

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

IF Formula To Choose A Rating

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

Use OR To Choose From Several Values In Array?

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:


This is returning a #VALUE error. A

View 5 Replies View Related

Choose Lowest Value Greater Than X

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

Macro To Choose Between Different Formulas?

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

Choose The Next Number In Sequence

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

How To Know Which Object Library To Choose

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

Formula: Choose The Calculation

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

Choose A Value From A Drop-down List

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

Choose Value From Lookup Vector

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

Choose Destination Folder With Vba

Nov 27, 2007

I would like a user of my program to choose the destination folder where a .txt file will be saved. I have code below which allows the chooser to browse through folders and choose a file:

Dim f1 As Variant, s1 As Variant
s1 = "#1: Choose the .txt file which contains montly data for the stock price."

f1 = Application _
. GetOpenFilename("TextFiles(*.txt),*.txt", , s1)
txt2.Text = f1

I want the user to browse folders and subfolders like they can with the above code. However the user will select a file path not a particular file. In the case of the code above, the text of Text Box txt2 would be set to the file path (i.e. "C:Documents and SettingsAdministratorDesktop")

View 3 Replies View Related

Choose Drive & Folder

Feb 22, 2008

I am trying to get the user to choose which disc the file structure should be stored in. Is it posible to get a dialog box which contains all the disc drives? I tried using the GetOpenFileDialog, but it seems I must choose a file. I must only choose Disc Drive.

View 5 Replies View Related

Show Pop-Up To Choose Sheet

Jun 11, 2008

I have used VBA and can do basic programming, but have no experience with popups beyond msgbox.

I've written a macro that I want to run, but it needs an input. This input is a worksheet name. What I want to do is run the macro, have a popup that has a combo box ask me to select which worksheet I want, either click ok and save that worksheet name to a variable or click cancel and exit the macro.

My Pseudo code looks like this:

Run Macro
Combo Box Popupoption1=sheet1option2=sheet2option3=sheet3If OK ThenSelection = Combo Box choiceElseIf Cancel ThenExit Sub
...Macro Code....

View 9 Replies View Related

Copyrights 2005-15, All rights reserved