| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo Box dropdown data
Hi everyone,
I am back with a question I asked earlier and didnt receive a response. Perhaps I didnt explain myself well enough. I will try again. I have 3 spreadsheets in the one workbook. Sheet one has my Forms-Combo Box/dropdowns in it. (I prefer to use these as I have a number of others throughout the sheet with quite large VBE data attached to them and rather than rewrite everything I chose to use the Forms-Combo Box to keep everything uniform.) Anyway, sheet 2 has my lists in it for the combo boxes and sheet 3 is my data capture sheet. Sheet 1 Combo box gets list from sheet 2. Combo box is attached to cell C5 in sheet 1. Sheet 2 has my list going from B1:B15 Sheet 3 I want the cell A1 (sheet3) to pick up the data from cell C5 (sheet1). I can get a1(sheet3) to recognise the cell C5(sheet1) but it puts the number of the cell from sheet2 instead of the text, eg In my combo box/dropdown on sheet 1 I chose Apple which in my list on sheet 2 would be B4. Instead of showing Apple in Sheet3 A1 it puts the number 4. I am wanting to capture all the data on a separate sheet in each survey as this will be going to a large number of people and it will make collating the data in a master sheet much easier. BTW I am using Excel 2003. I hope this makes better sense and someone will be able to help me. Cheers Lynda |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
=OFFSET(Sheet2!B1,Sheet1!C5-1,0,1,1)
-- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi everyone, I am back with a question I asked earlier and didnt receive a response. Perhaps I didnt explain myself well enough. I will try again. I have 3 spreadsheets in the one workbook. Sheet one has my Forms-Combo Box/dropdowns in it. (I prefer to use these as I have a number of others throughout the sheet with quite large VBE data attached to them and rather than rewrite everything I chose to use the Forms-Combo Box to keep everything uniform.) Anyway, sheet 2 has my lists in it for the combo boxes and sheet 3 is my data capture sheet. Sheet 1 Combo box gets list from sheet 2. Combo box is attached to cell C5 in sheet 1. Sheet 2 has my list going from B1:B15 Sheet 3 I want the cell A1 (sheet3) to pick up the data from cell C5 (sheet1). I can get a1(sheet3) to recognise the cell C5(sheet1) but it puts the number of the cell from sheet2 instead of the text, eg In my combo box/dropdown on sheet 1 I chose Apple which in my list on sheet 2 would be B4. Instead of showing Apple in Sheet3 A1 it puts the number 4. I am wanting to capture all the data on a separate sheet in each survey as this will be going to a large number of people and it will make collating the data in a master sheet much easier. BTW I am using Excel 2003. I hope this makes better sense and someone will be able to help me. Cheers Lynda |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
Hi Jim, thank you for responding. When i enter your function i get #REF!
Cheers Lynda "Jim Cone" wrote: =OFFSET(Sheet2!B1,Sheet1!C5-1,0,1,1) -- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi everyone, I am back with a question I asked earlier and didnt receive a response. Perhaps I didnt explain myself well enough. I will try again. I have 3 spreadsheets in the one workbook. Sheet one has my Forms-Combo Box/dropdowns in it. (I prefer to use these as I have a number of others throughout the sheet with quite large VBE data attached to them and rather than rewrite everything I chose to use the Forms-Combo Box to keep everything uniform.) Anyway, sheet 2 has my lists in it for the combo boxes and sheet 3 is my data capture sheet. Sheet 1 Combo box gets list from sheet 2. Combo box is attached to cell C5 in sheet 1. Sheet 2 has my list going from B1:B15 Sheet 3 I want the cell A1 (sheet3) to pick up the data from cell C5 (sheet1). I can get a1(sheet3) to recognise the cell C5(sheet1) but it puts the number of the cell from sheet2 instead of the text, eg In my combo box/dropdown on sheet 1 I chose Apple which in my list on sheet 2 would be B4. Instead of showing Apple in Sheet3 A1 it puts the number 4. I am wanting to capture all the data on a separate sheet in each survey as this will be going to a large number of people and it will make collating the data in a master sheet much easier. BTW I am using Excel 2003. I hope this makes better sense and someone will be able to help me. Cheers Lynda |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
It works for me. The formula was entered on Sheet3 in cell A1.
Check the sheet names - no spaces in the ones I used. The #REF! error value occurs when a cell reference is not valid. -- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi Jim, thank you for responding. When i enter your function i get #REF! Cheers Lynda "Jim Cone" wrote: =OFFSET(Sheet2!B1,Sheet1!C5-1,0,1,1) -- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi everyone, I am back with a question I asked earlier and didnt receive a response. Perhaps I didnt explain myself well enough. I will try again. I have 3 spreadsheets in the one workbook. Sheet one has my Forms-Combo Box/dropdowns in it. (I prefer to use these as I have a number of others throughout the sheet with quite large VBE data attached to them and rather than rewrite everything I chose to use the Forms-Combo Box to keep everything uniform.) Anyway, sheet 2 has my lists in it for the combo boxes and sheet 3 is my data capture sheet. Sheet 1 Combo box gets list from sheet 2. Combo box is attached to cell C5 in sheet 1. Sheet 2 has my list going from B1:B15 Sheet 3 I want the cell A1 (sheet3) to pick up the data from cell C5 (sheet1). I can get a1(sheet3) to recognise the cell C5(sheet1) but it puts the number of the cell from sheet2 instead of the text, eg In my combo box/dropdown on sheet 1 I chose Apple which in my list on sheet 2 would be B4. Instead of showing Apple in Sheet3 A1 it puts the number 4. I am wanting to capture all the data on a separate sheet in each survey as this will be going to a large number of people and it will make collating the data in a master sheet much easier. BTW I am using Excel 2003. I hope this makes better sense and someone will be able to help me. Cheers Lynda |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
Hi Jim, thank you so much, I got it to work. I have been trying to use it for
my other dropdowns but I just can't seem to get it to work. I went online at work today to try to work out how it is done and I think I have managed to understand how it works when everything is on one page but I just can't work it out when I involves more than one sheet. I was starting to feel like an idiot until I started reading other peoples comments on the OFFSET function and I don't feel so bad that I am having trouble understanding it, it appears as though it is not easy to use. The ones I cant get to work are as follows sheet 1(dropdowns) sheet 2(List) sheet 3(Data) D38 Column A G3 G38 Column B H3 K38 Column C I3 E44 Column O K3 H44 Column P L3 I would really like to be able to understand how they work. I tried everything today but it beat me. Thank you for your help Jim. Cheers Lynda |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
"=OFFSET(Sheet2!B1, Sheet1!C5-1,0, 1,1)" An Offset formula has three parts... 1. The referenced formula starts at a specific cell: Sheet2!B1 2. It returns a cell a specified number of rows and columns away... Rows down... The value in Sheet1!Cell C5 minus 1 Columns to the right... Zero (no change) 3. The cell returned can be "resized"... 1,1 means no resizing - it is one row high and one column wide. -- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi Jim, thank you so much, I got it to work. I have been trying to use it for my other dropdowns but I just can't seem to get it to work. I went online at work today to try to work out how it is done and I think I have managed to understand how it works when everything is on one page but I just can't work it out when I involves more than one sheet. I was starting to feel like an idiot until I started reading other peoples comments on the OFFSET function and I don't feel so bad that I am having trouble understanding it, it appears as though it is not easy to use. The ones I cant get to work are as follows sheet 1(dropdowns) sheet 2(List) sheet 3(Data) D38 Column A G3 G38 Column B H3 K38 Column C I3 E44 Column O K3 H44 Column P L3 I would really like to be able to understand how they work. I tried everything today but it beat me. Thank you for your help Jim. Cheers Lynda |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
Hi Jim, please be patient with me while I get a handle on this.
"=OFFSET(Sheet2!B1,Sheet1!C5 (I understand this bit) 1,1)" (I understand this bit) The bit I don't understand is -1,0, How do you decide when it is a minus number, what controls that decision? Also the columns, what controls that decision? Does it make a difference if they are on seperate sheets? I thought it may have been the B in sheet2 and the C in sheet one but then the more I looked at it the less convinced I was. Sorry to be a nuisance, thank you for your time. Cheers Lynda "Jim Cone" wrote: "=OFFSET(Sheet2!B1, Sheet1!C5-1,0, 1,1)" An Offset formula has three parts... 1. The referenced formula starts at a specific cell: Sheet2!B1 2. It returns a cell a specified number of rows and columns away... Rows down... The value in Sheet1!Cell C5 minus 1 Columns to the right... Zero (no change) 3. The cell returned can be "resized"... 1,1 means no resizing - it is one row high and one column wide. -- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi Jim, thank you so much, I got it to work. I have been trying to use it for my other dropdowns but I just can't seem to get it to work. I went online at work today to try to work out how it is done and I think I have managed to understand how it works when everything is on one page but I just can't work it out when I involves more than one sheet. I was starting to feel like an idiot until I started reading other peoples comments on the OFFSET function and I don't feel so bad that I am having trouble understanding it, it appears as though it is not easy to use. The ones I cant get to work are as follows sheet 1(dropdowns) sheet 2(List) sheet 3(Data) D38 Column A G3 G38 Column B H3 K38 Column C I3 E44 Column O K3 H44 Column P L3 I would really like to be able to understand how they work. I tried everything today but it beat me. Thank you for your help Jim. Cheers Lynda |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
Hi Jim, It is now 12 midnight here on the east coast of Australia, I need to
go to bed as I have to rise early for work tomorrow. I am aware it is early morning for you there. Hopefully you will respond to my query and you will look out for me when you rise tomorrow morning. Thank you for your help. Cheers Lynda "Lynda" wrote: Hi Jim, please be patient with me while I get a handle on this. "=OFFSET(Sheet2!B1,Sheet1!C5 (I understand this bit) 1,1)" (I understand this bit) The bit I don't understand is -1,0, How do you decide when it is a minus number, what controls that decision? Also the columns, what controls that decision? Does it make a difference if they are on seperate sheets? I thought it may have been the B in sheet2 and the C in sheet one but then the more I looked at it the less convinced I was. Sorry to be a nuisance, thank you for your time. Cheers Lynda "Jim Cone" wrote: "=OFFSET(Sheet2!B1, Sheet1!C5-1,0, 1,1)" An Offset formula has three parts... 1. The referenced formula starts at a specific cell: Sheet2!B1 2. It returns a cell a specified number of rows and columns away... Rows down... The value in Sheet1!Cell C5 minus 1 Columns to the right... Zero (no change) 3. The cell returned can be "resized"... 1,1 means no resizing - it is one row high and one column wide. -- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi Jim, thank you so much, I got it to work. I have been trying to use it for my other dropdowns but I just can't seem to get it to work. I went online at work today to try to work out how it is done and I think I have managed to understand how it works when everything is on one page but I just can't work it out when I involves more than one sheet. I was starting to feel like an idiot until I started reading other peoples comments on the OFFSET function and I don't feel so bad that I am having trouble understanding it, it appears as though it is not easy to use. The ones I cant get to work are as follows sheet 1(dropdowns) sheet 2(List) sheet 3(Data) D38 Column A G3 G38 Column B H3 K38 Column C I3 E44 Column O K3 H44 Column P L3 I would really like to be able to understand how they work. I tried everything today but it beat me. Thank you for your help Jim. Cheers Lynda |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
The decision is controlled by the selection in the combo box.
Changing the combo box selection changes the value in C5. If the value in C5 is 3 then... Offset(B1, 3,1) returns the value in cell B4 Offset(B1, 3 -1,1) returns the value in cell B3 -- Jim Cone "Lynda" wrote in message Hi Jim, please be patient with me while I get a handle on this. "=OFFSET(Sheet2!B1,Sheet1!C5 (I understand this bit) 1,1)" (I understand this bit) The bit I don't understand is -1,0, How do you decide when it is a minus number, what controls that decision? Also the columns, what controls that decision? Does it make a difference if they are on seperate sheets? I thought it may have been the B in sheet2 and the C in sheet one but then the more I looked at it the less convinced I was. Sorry to be a nuisance, thank you for your time. Cheers Lynda "Jim Cone" wrote: "=OFFSET(Sheet2!B1, Sheet1!C5-1,0, 1,1)" An Offset formula has three parts... 1. The referenced formula starts at a specific cell: Sheet2!B1 2. It returns a cell a specified number of rows and columns away... Rows down... The value in Sheet1!Cell C5 minus 1 Columns to the right... Zero (no change) 3. The cell returned can be "resized"... 1,1 means no resizing - it is one row high and one column wide. -- Jim Cone Portland, Oregon USA "Lynda" wrote in message Hi Jim, thank you so much, I got it to work. I have been trying to use it for my other dropdowns but I just can't seem to get it to work. I went online at work today to try to work out how it is done and I think I have managed to understand how it works when everything is on one page but I just can't work it out when I involves more than one sheet. I was starting to feel like an idiot until I started reading other peoples comments on the OFFSET function and I don't feel so bad that I am having trouble understanding it, it appears as though it is not easy to use. The ones I cant get to work are as follows sheet 1(dropdowns) sheet 2(List) sheet 3(Data) D38 Column A G3 G38 Column B H3 K38 Column C I3 E44 Column O K3 H44 Column P L3 I would really like to be able to understand how they work. I tried everything today but it beat me. Thank you for your help Jim. Cheers Lynda |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
Clarification...
Cell C5 being the linked cell for the combo box. '-- Jim Cone |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo Box dropdown data
Thank you for your time Jim but I give up, I have tried and tried and I just
can't get my head around it. I will experiment with some other functions to try to get the same result. Maybe one day when I am not under so much pressure to get this thing completed I will take more time to study it because I really want to work it out. Thank you so much. Cheers Lynda |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combo Box / Dropdown Box HELP!!!! | Excel Discussion (Misc queries) | |||
| Dependant Dropdown lists using Combo Boxes | Excel Discussion (Misc queries) | |||
| How to Changing Dropdown text color in a Combo Box. | Excel Discussion (Misc queries) | |||
| combo box dropdown list | Excel Worksheet Functions | |||
| create dropdown menu using Combo box | Excel Worksheet Functions | |||
Linear Mode
