Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP......I am going insane....
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 dont 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP......I am going insane....
Use Indirect
=Indirect(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)) "Lynda" wrote: 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 dont 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP......I am going insane....
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" wrote: Use Indirect =Indirect(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)) "Lynda" wrote: 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 dont 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP......I am going insane....
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!G 35-1,0,1,1))) "Lynda" wrote: 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" wrote: Use Indirect =Indirect(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)) "Lynda" wrote: 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 dont 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP......I am going insane....
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" wrote: 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!G 35-1,0,1,1))) "Lynda" wrote: 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" wrote: Use Indirect =Indirect(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)) "Lynda" wrote: 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 dont 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP......I am going insane....
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" wrote: 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" wrote: 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!G 35-1,0,1,1))) "Lynda" wrote: 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" wrote: Use Indirect =Indirect(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)) "Lynda" wrote: 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 dont 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP......I am going insane....
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. "Joel" wrote: 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" wrote: 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" wrote: 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!G 35-1,0,1,1))) "Lynda" wrote: 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" wrote: Use Indirect =Indirect(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)) "Lynda" wrote: 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 dont 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This is DRIVING ME INSANE! | Excel Worksheet Functions | |||
PivotCharts, PivotTables, I'm going insane | Charts and Charting in Excel | |||
I am going insane trying to work this out... | Excel Discussion (Misc queries) | |||
Workbook file size is insane, calcs take forever | Excel Discussion (Misc queries) |