Combo Box dropdown data

[Lynda]

Hi everyone,

I am back with a question I asked earlier and didn’t receive a response.
Perhaps I didn’t 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

 

[Jim Cone]

=OFFSET(Sheet2!B1,Sheet1!C5-1,0,1,1)
--
Jim Cone
Portland, Oregon USA


"Lynda" <[email protected]>
wrote in message
Hi everyone,
I am back with a question I asked earlier and didn’t receive a response.
Perhaps I didn’t 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

 

[Lynda]

Hi Jim, thank you for responding. When i enter your function i get #REF!

Cheers
Lynda

 

[Jim Cone]

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"
<[email protected]>
wrote in message
Hi Jim, thank you for responding. When i enter your function i get #REF!
Cheers
Lynda

 

[Lynda]

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 can’t 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

 

[Jim Cone]

Re: "=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"
<[email protected]>
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 can’t 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

 

[Lynda]

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

 

[Lynda]

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

 

[Jim Cone]

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" <[email protected]>
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]

Clarification...
Cell C5 being the linked cell for the combo box.
'--
Jim Cone

 

[Lynda]

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