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

=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

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

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

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

"=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