#### Drop down values relating to cells in next worksheet

```Here goes, hope this makes sense!  I have a worksheet that contains a
questionnaire, which is filled in by choosing the reply from the drop down
(Data/Validation) box, e.g. 'Strongly Agree', 'Disagree' etc.  I need to be
able to make these choices relate to my scoresheet in the next worksheet, so
that when 'Strongly disagree' is chosen, a score of 10 is transferred to the
related cell in the scoresheet.  There are 4 choices - strongly disagree =
10, disagree = 9, agree = 1, strongly agree = 0.  I haven't a clue how to do
this, hope someone can help!
```
 0
AbbyLT (7)
10/28/2004 9:13:05 AM
excel.misc 78881 articles. 5 followers.

3 Replies
437 Views

Similar Articles

[PageSpeed] 11

```Hi
use vLOOKUP. e.g.
=VLOOKUP(A1,'other_sheet'!A1:20,2,0)

"AbbyLT" wrote:

> Here goes, hope this makes sense!  I have a worksheet that contains a
> questionnaire, which is filled in by choosing the reply from the drop down
> (Data/Validation) box, e.g. 'Strongly Agree', 'Disagree' etc.  I need to be
> able to make these choices relate to my scoresheet in the next worksheet, so
> that when 'Strongly disagree' is chosen, a score of 10 is transferred to the
> related cell in the scoresheet.  There are 4 choices - strongly disagree =
> 10, disagree = 9, agree = 1, strongly agree = 0.  I haven't a clue how to do
> this, hope someone can help!
```
 0
frank.kabel (11126)
10/28/2004 9:25:04 AM
```in sheet 2 -C4 to D8 are

strongly disagree         10
disagree                         9
agree                                 1
strongly agree                 0

call this database for e.g. as "data"

in sheet 1  -   the answers to your questions are there as follows in B6 to
D11

1                             strongly disagree
2                             strongly agree
3                             agree
4                             disagree
5                             strongly agree

in D7 type
=VLOOKUP(C7,data,2,FALSE)
you will get 10
copy D7 down upto D11
you will get the corresponding marks

D7 will be 10
D8 will be  0
D9 will be 1
D10 will be 9 and
D11 will be  0

may not be elegant but it works. I am sure you get the hang of it.

MAKE SURE that the spellings are same in both sheets.

AbbyLT <AbbyLT@discussions.microsoft.com> wrote in message
news:03AC3B71-6D19-45F6-9607-FC8E84A8570C@microsoft.com...
> Here goes, hope this makes sense!  I have a worksheet that contains a
> questionnaire, which is filled in by choosing the reply from the drop down
> (Data/Validation) box, e.g. 'Strongly Agree', 'Disagree' etc.  I need to
be
> able to make these choices relate to my scoresheet in the next worksheet,
so
> that when 'Strongly disagree' is chosen, a score of 10 is transferred to
the
> related cell in the scoresheet.  There are 4 choices - strongly disagree =
> 10, disagree = 9, agree = 1, strongly agree = 0.  I haven't a clue how to
do
> this, hope someone can help!

```
 0
R
10/28/2004 9:51:04 AM
```Thats great, thanks muchly!

"R.VENKATARAMAN" wrote:

> in sheet 2 -C4 to D8 are
>
> strongly disagree         10
> disagree                         9
> agree                                 1
> strongly agree                 0
>
> call this database for e.g. as "data"
>
> in sheet 1  -   the answers to your questions are there as follows in B6 to
> D11
>
> 1                             strongly disagree
> 2                             strongly agree
> 3                             agree
> 4                             disagree
> 5                             strongly agree
>
> in D7 type
> =VLOOKUP(C7,data,2,FALSE)
> you will get 10
> copy D7 down upto D11
> you will get the corresponding marks
>
> D7 will be 10
> D8 will be  0
> D9 will be 1
> D10 will be 9 and
> D11 will be  0
>
>
> may not be elegant but it works. I am sure you get the hang of it.
>
> MAKE SURE that the spellings are same in both sheets.
>
>
>
>
> AbbyLT <AbbyLT@discussions.microsoft.com> wrote in message
> news:03AC3B71-6D19-45F6-9607-FC8E84A8570C@microsoft.com...
> > Here goes, hope this makes sense!  I have a worksheet that contains a
> > questionnaire, which is filled in by choosing the reply from the drop down
> > (Data/Validation) box, e.g. 'Strongly Agree', 'Disagree' etc.  I need to
> be
> > able to make these choices relate to my scoresheet in the next worksheet,
> so
> > that when 'Strongly disagree' is chosen, a score of 10 is transferred to
> the
> > related cell in the scoresheet.  There are 4 choices - strongly disagree =
> > 10, disagree = 9, agree = 1, strongly agree = 0.  I haven't a clue how to
> do
> > this, hope someone can help!
>
>
>
```
 0
AbbyLT (7)
10/28/2004 10:19:07 AM

Similar Artilces:

Table SY02000
In table SY02000 we have records with a RESTYPE value of 23. What is this? I know what 1 ans 2 are but I am stumped about 23. -- Robert J. O'Donnell MBS Consultant US and LATAM Rock Solid Technologies 512.347.9399x109 Hi Robert Resource type 23 is a report. Usually referred to with the constant REPORTTYPE. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessaril...

Trying to add two rows of times with the result in one cell, but having trouble. When I try to add the two rows together, I get a “#VALUE” result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis –– but I need to display just one result for both rows - in the “Row 2” cell of the “HOURS WORKED” column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that &...

Drop Down List #9
I am trying to create a drop down list using two worksheets. How would I create this. One sheet would where the final product is and the other would be where the data is being kept. Watch this 5 min video: How to setup a data validation drop down list: http://youtube.com/watch?v=t2OsWJijrOM -- Biff Microsoft Excel MVP "Vincent Kerzman" <Vincent Kerzman@discussions.microsoft.com> wrote in message news:B2DAA48B-0BD2-4932-AE91-B86D55B8D8A6@microsoft.com... >I am trying to create a drop down list using two worksheets. How would I > create this. > > One s...

Drag and drop of eMail to own application
Hi everybody, i like to drag and drop an eMail of Outlook ( Express & Xp ) and Thunderbird into my own application ( MFC ). There'in the dropped eMail should be readed and the subject, body text, Recepiant, ... should be displayed into textboxes. I can check the different formats of the Drag-Event or Clipboard data. An Outlook Express mail is stored in plaintext into the clipboard, so it's seriously possible to extract it. But with Outlook XP Mails i see only the name of a .eml file ( the of file which would be saved on the Destop for example ) But how to extract the data? Do...

Splitting a Cell
Hello, I have a Cell that contains first and last name data. I would like to split this one cell into two cells, one with first name data and one with last name data. I have tried doing this through a Macro, but with no sucess. Can anyone point me in the right direction? Thank you! Hi use 'Data - Text to columns' for this -- Regards Frank Kabel Frankfurt, Germany "Roast" <Noonan.Bill@gmail.com> schrieb im Newsbeitrag news:1103217577.972829.21480@c13g2000cwb.googlegroups.com... > Hello, > I have a Cell that contains first and last name data. I would like ...

how do i use format cells to alpha / numeric values
HAVE A STRING OF DATA i WANT TO ENTER into a cell, but I also want to set format to set the values. eg. if I put in asd123 I want it to appear as ASD123 but there are also occasions when I will put in a different mix of letters/numbers a12s3d, which I also want to come out as A12S3D. I can do this in Access but cant seem to crack the code in excel. Please help. Try =UPPER(E2) HTH Regards, Howard "Samvid69" <Samvid69@discussions.microsoft.com> wrote in message news:701E3143-A79D-4FDE-8661-325795CE50F0@microsoft.com... > HAVE A STRING OF DATA i WANT T...

Including Chart with emailed worksheet
For some reason when selecting the "Send To, Mail Recipient" option in an Excel Worksheet the chart included on the worksheet is being stipped from the email. In a company of about 15 workstations this in only happening on one workstation. Not sure if this is an Excel issue or Outlook issue or even possibly an Exchange issue? Any suggestions would be appreciated. ...

reference cell value from fixed column with variable row
template wizard wont let me select a form control (combo box) value to associate a field with but provides a reference cell value which gives me the row number in the other workbook of the value. how do i make a valid cell reference from this reference? i.e. col of referenced value is "A", row is variable depending on what value i pick in combo box. how do i make a reference out of "A" plus the value to equal "A5" ? (so that i can associate a field with it using the template wizard) -- bob z ...

Vlookup found what it was looking for, but what cell was in in?
I need to do an OFFSET(???, 3,2) The ??? would be the cell that a VLOOKUP found the Lookup_Value in the Table_Array. or The Vlookup found what it was looking for, but what cell was in in? Any ideas how I can do this? I was told to.. How do I get the result from a VLOOKUP into the function? You could use application.match() Dim res as variant 'could be an error dim somevalue as string 'or what??? dim somerange as range set somerange = worksheets("somesheet").range("a:a") res = application.match(somevalue, somerange,0) if iserror(res) then msgbox "no ...

drag and drop
Using office XP I cannot drag and drop items from one folder to another in outlook 2002. Any ideas? Thanks ...

drop down calendar #4
In a cell I want to have a drop down calendar of the current month. I downloaded a template of a calendar and tried data valadation but it only displayed the source range. Any ideas? TIA http://www.windowsdevcenter.com/pub/a/windows/2004/04/27/excelhacks.html "Ray A" wrote: > In a cell I want to have a drop down calendar of the current month. I > downloaded a template of a calendar and tried data valadation but it only > displayed the source range. Any ideas? > TIA > Ray See Ron de Bruin's site for this. http://www.rondebruin.nl/calendar.htm Gord D...

How do I copy a filtered subset of data to another worksheet?
I am trying to develop a series of analysis from a major worksheet. I need to capture as a report each of the subsets of data for a snapshot of today's work. When I try to copy - the Clipboard, of course, copies everything, including the hidden data. I know there is a way to copy just what is visible but I can't remember and can't find any reference in any help files. Anyone? Edit|Go To|Special...|Visible Cells only "sftwrqn" wrote: > I am trying to develop a series of analysis from a major worksheet. I need > to capture as a report each of the subsets...

drop down data want to choose more than one item for cell
I have a drop down box that i want folks to be able to choose more than one item from the box to go into the cell. Multiselect from a DV dropdown is possible using VBA. See Debra Dalgleish's site for a downloadable sample workbook with event code. http://www.contextures.on.ca/excelfiles.html#DV0017 Note that you can have the selections in an adjacent cell or in the same cell. Gord Dibben MS Excel MVP On Fri, 26 Feb 2010 12:40:08 -0800, FranW <FranW@discussions.microsoft.com> wrote: >I have a drop down box that i want folks to be able to choose more than...

Drop Down List #2
Hi All Can this be done, as I am struggling. I have data in sheet B and want to create a drop down list in sheet A from this data, and I can't make it happen, I can create a drop down list in sheet A from data in sheet A but not from sheet B Could anyone help please Cheers Steve Name the range on the other sheet, and you can refer to it in the data validation dialog box. There are instructions here: http://www.contextures.com/xlDataVal01.html Steve wrote: > I have data in sheet B and want to create a drop down list in sheet A from > this data, and I can't make it h...

Updating values in 2 forms
I have a script starting with the "FindAll" dialog that helps me search thru several sheet in a particular workbook. When I press "Find Next", the value of ActiveCell is updated in an UserForm1.txtLayout text box. Unfortunately, I dont know how to do it in any other workbook without inserting the following code it in all the sheets in the document. Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.txtLayout.Text = ActiveCell.Text End Sub If any idea please let me know!It is very frustrating! Compile succesfully! ...

Insert Worksheets based on column data.
Hi everyone I run a report that creates a 7 coumn spreadsheet analysing staff time through a week. The last column (G) uses a staff code and is sorted in ascending order. What I would like to do is to run a macro or program that will go through the spreadsheet and create a new worksheet for each Staff code, naming the worksheet exactly the same, and inserting all the rows of data belonging to each staff code into its individual worksheet. For instance, if one of the Staff codes in the original pages is TW and there are 9 rows of data for TW, I would like a worksheet inser...

Generating a cell reference
I cannot find the solution to my question anwhere. If I enter the letter A in cell b3, enter the number 10 in cell d3, enter the letter A in cell b5, and enter the number 20 in cell d5, then how do I create cell references from these entrys that can be used in formulas, such as sum(a10:a20)? I hope this makes sense, I really would like to accomplish this. Thanks for any advice and assistance. Glen Try this: =SUM(INDIRECT(B3&D3&":"&B3&D5)) You did say "A" was in B3 *and* B5, so I used B3 twice, since you wanted: <<"sum(a10:a20)">>...

Counting cells before/after a maximum value
If I have a list of numbers like the following... 2,5,2,2,2,2,2,2,2,1,2,5,5,6,9,11,8,11,12 .... across a row of cells, is there a way of counting the number of (non-empty) cells to left of the maximum value, and also to the right of it? In the above example, the answer to both should be 9, of course. TIA -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Well, I think the answer would be 9 for both if the number 1 in the middle of your data was actually a mis-type for something like 13 or 14, or if you want to count away from the MINIMUM and n...

Counting Values
What formula can I write to say: How many values in A1:A100 are greater than B2+B3? Try this =COUNTIF(A1:A100,">"&(B2+B3)) -- Regards Ron de Bruin http://www.rondebruin.nl "Adam1 Chicago" <Adam1Chicago@discussions.microsoft.com> wrote in message news:1493F672-3279-49C1-893D-289D71F7D55E@microsoft.com... > What formula can I write to say: How many values in A1:A100 are greater > than > B2+B3? Thanks, that worked well. One more question: How many values in A1:A100 are >B2-B3 and <B2+B3? (I tried using AND and the trick you showed m...

Question of drop-down listbox
Hello, Is it possible to have listbox or combobox with Auto HScroll (ES_AUTOHSCROLL) capabilities. Thank You! I don't get it. ES_AUTOHSCROLL is an Edit control style, how would it apply to a listbox or combobox? What are you trying to do? AliR. "Ririko Horvath" <horvathr@securemethods.com> wrote in message news:uHUo%23OyLFHA.3336@TK2MSFTNGP10.phx.gbl... > Hello, > > Is it possible to have listbox or combobox with Auto HScroll > (ES_AUTOHSCROLL) capabilities. > > Thank You! > > I'm soory for not expressing my question properly...

protect worksheet #2
I have a worksheet where I allow other users to edit by entering password, when I set the flag in delete row and delete column, when I protect the sheet other user can not delete rows and column. Hi Ib If there are cells in the row or column that are protect you can't delete the row or column Also if you flag the delete options -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Ib Kristensen" <anonymous@discussions.microsoft.com> wrote in message news:1C66FBAF-CC15-4042-B01B-4F5FDFC16D01@microsoft.com... > I have a worksheet where I allow o...

How to make one worksheet from multiple worksheets?
Hi All, Can someone tell me how to do the following: I have several worksheets of data in a file. All the worksheets have exactly the same Columns. I want to create a single worksheet with all the data from all the existing worksheets. Up to now I have been copy/pasting the data into a single worksheet. Is there a programmatic way of doing this for me? Thanks, John. Ron de Bruin has some same code at: http://www.rondebruin.nl/copy2.htm John Rugo wrote: > > Hi All, > Can someone tell me how to do the following: > > I have several worksheets of data in a file. > Al...

Values dependant on previous values
Hello I need to build a spreadsheet which when a person selects a particular option on one column than options dependant on that selection are given in the next. So for example if car colour blue us selected in column A, then black, beige and blue are options for the interior in column B, but if red colour is selected, then beige, red and green are displayed in then next column. Help I'm new to complex formlas in excel (if this is complex!) Thanks for looking You can use dependent data validation lists. There are instructions here: http://www.contextures.com/xlDataVal02.html...

Lead Field Values not available in Account
I have created some new values in the Industry pick list (i.e. Govt - Federal) for the Lead system, and it all works fine. If I convert the lead into an account, and then look at the new account's Industry pick list, I see "36" instead of Govt - Federal. Is there a way to map those two fields so that the values are always the same, or will I need to create the same values in both the Leads and Accounts section each time a need a new, custom value? Thanks! Adam Hi Adam, You will have to recreate the new values in Accounts as well. Pay attention to the id's that the ...