HELP......I am going insane....

[Lynda]

I am sorry for posting this again but there must be someone out there among
you gurus, that can help me with a formula that will change numbers to text
PLEASE.
I have created a number of surveys or requests for information sheets in the
same format, that being dependant dropdown lists using the combo box from
forms toolbar so I really don’t want to change my format.


Sheet 1 – Combo boxes (dropdowns) gets lists from sheet 2. I have my
combo boxes attached to cells in sheet 1.
Sheet 2 – has my lists for the dropdowns
Sheet 3 – I want the cells in sheet3 to pick up the data from cells attached
the dropdown cells in sheet 1. When the data sheet picks up the info from
the cell in sheet 1 it gives me a number and not the text in that cell.
I was given a formula =OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1). I have
managed to get most of them working, the ones I am having trouble with now
are those that are attached to a dependant drop down list.

Sheet 1(dropdowns) Sheet 2(Lists) Sheet 3(Data)
D38 (got this one working) ColumnA G3
G38 (this one is dependent on D38) ColumnB H3
K38 (this one is dependent on G38) ColumnC I3

E44 (got this one working) ColumnO K3
H44 (this one is dependent on E44) ColumnP L3
I have tried experimenting with other formulas but I am just so hopeless.
I was going to give up but it is driving me insane, please, please, please
someone, anyone.
Thank you
Lynda

 

[Joel]

Use Indirect

=Indirect(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1))

 

[Lynda]

Thank you for responding Joel, I had the same problem with my first offset
function in that i kept getting the #REF! error and i managed to work it out
but i can't seem to work out the #REF! error with your function. Can you
advise me where i may be going wrong?

Thanks
Lynda

 

[Joel]

Usually with errors yo uhave to use an if stement like this

=if(iserr(MyFormula),"",Myformula)


=IF(ISERR(INDIRECT(OFFSET(Lists!J1,Dropdowns!G35-1,0,1,1))),"",INDIRECT(OFFSET(Lists!J1,Dropdowns!G35-1,0,1,1)))

 

[Lynda]

Hi Joel, thank you. Well you got rid of the #REF! error now i'm just getting
blank cells.
Do i need to put this formula in the data cell for the No. 1 dropdown or
just No. 2 (which is dependant on No. 1) and No. 3 (which is dependant on
No.2)

Cheers
Lynda

 

[Joel]

You can put any message you want into the if statement instead of the blank
string. I think you may want to return "Not found" so your boxes will
contain something the user will understand.

 

[Lynda]

I changed the blank to a zero (0) which is fine. the problem now is when i
operate the dropdowns the formula doesn't recognise what is in the dropdown
cell.