Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
Hi,
I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
You can use Data Validation to create these drop down lists.
Check out these pages of Debra Dalgleish, where you can out everything about them. http://www.contextures.com/tiptech.html Scroll down to the D's, and see all the pages referring to Data Validation. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Daiv" wrote in message ... Hi, I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
That was very helpfull!!
Iam still unsure how to apply a function that is specified in another cell. these functions will be applied 20+ times, and they will be referencing different cells each time (in a predictable pattern) When I try this i just get the value of the cell holding the formula instead of the formula itself being applied to the cell referencing it. Thanks again! Daiv "Ragdyer" wrote: You can use Data Validation to create these drop down lists. Check out these pages of Debra Dalgleish, where you can out everything about them. http://www.contextures.com/tiptech.html Scroll down to the D's, and see all the pages referring to Data Validation. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Daiv" wrote in message ... Hi, I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
You're being very general.
Post back with specific examples ... what your data configuration looks like, what you would like to see happen. The more you tell us, the better we can make accurate suggestions. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Daiv" wrote in message ... That was very helpfull!! Iam still unsure how to apply a function that is specified in another cell. these functions will be applied 20+ times, and they will be referencing different cells each time (in a predictable pattern) When I try this i just get the value of the cell holding the formula instead of the formula itself being applied to the cell referencing it. Thanks again! Daiv "Ragdyer" wrote: You can use Data Validation to create these drop down lists. Check out these pages of Debra Dalgleish, where you can out everything about them. http://www.contextures.com/tiptech.html Scroll down to the D's, and see all the pages referring to Data Validation. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Daiv" wrote in message ... Hi, I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
Sorry about that. i thought there was just a command or something to tell it
to take the formula instead of the value. I am working with a sheet that calculates the velocity of water flow. the measurements are taken many times and each time the same calculation needs to be done. for example DISTANCE WIDTH DEPTH REVS TIME VELOCITY AREA DISCHARGE (m) (m) (m) (s) (m/s) (m2) (m3/s) 0.00 RB 0.00 1.00 1.000 0.00 0 40.0 0.006 0.000 0.000 2.00 1.000 0.00 0 40.0 0.006 0.000 0.000 3.00 1.000 0.00 0 40.0 0.006 0.000 0.000 The meter that was used to determine the REVS each have different formulas to come up with the velocity. I am wanting to get a drop box that lists the different meters (which I have accomplished), and based on the meter, have it automatically insert the correct formula in each VELOCITY cell. Currently I have the list items stored in adifferent sheet, and want to put the formula in a cell beside the list item so the list can grow/shrink easily as time goes on. The velocity formulas use the width, revs and time values in each respective row. I hope this helps. Let me know if you need more info. Daiv. "RagDyeR" wrote: You're being very general. Post back with specific examples ... what your data configuration looks like, what you would like to see happen. The more you tell us, the better we can make accurate suggestions. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Daiv" wrote in message ... That was very helpfull!! Iam still unsure how to apply a function that is specified in another cell. these functions will be applied 20+ times, and they will be referencing different cells each time (in a predictable pattern) When I try this i just get the value of the cell holding the formula instead of the formula itself being applied to the cell referencing it. Thanks again! Daiv "Ragdyer" wrote: You can use Data Validation to create these drop down lists. Check out these pages of Debra Dalgleish, where you can out everything about them. http://www.contextures.com/tiptech.html Scroll down to the D's, and see all the pages referring to Data Validation. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Daiv" wrote in message ... Hi, I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
Right now we have 5 different meters. the formulas are unique to each meter
and the differences are not predictable on the surface. as an example, one formula is :IF(REVS=40,((REVS/t*2.17)+0.03)*0.3048,((REVS/t*2.18)+0.02)*0.3048), another is: (0.6711*revs/t)+0.0061 I have a drop down data validation list elsewhere on the sheet for the user to choose the meter used. "paul" wrote: how many meters/formulas do you have and how do the formulas differ?there will have to be an identifier on your table to know which meter/formula to use -- paul remove nospam for email addy! "Daiv" wrote: Sorry about that. i thought there was just a command or something to tell it to take the formula instead of the value. I am working with a sheet that calculates the velocity of water flow. the measurements are taken many times and each time the same calculation needs to be done. for example DISTANCE WIDTH DEPTH REVS TIME VELOCITY AREA DISCHARGE (m) (m) (m) (s) (m/s) (m2) (m3/s) 0.00 RB 0.00 1.00 1.000 0.00 0 40.0 0.006 0.000 0.000 2.00 1.000 0.00 0 40.0 0.006 0.000 0.000 3.00 1.000 0.00 0 40.0 0.006 0.000 0.000 The meter that was used to determine the REVS each have different formulas to come up with the velocity. I am wanting to get a drop box that lists the different meters (which I have accomplished), and based on the meter, have it automatically insert the correct formula in each VELOCITY cell. Currently I have the list items stored in adifferent sheet, and want to put the formula in a cell beside the list item so the list can grow/shrink easily as time goes on. The velocity formulas use the width, revs and time values in each respective row. I hope this helps. Let me know if you need more info. Daiv. "RagDyeR" wrote: You're being very general. Post back with specific examples ... what your data configuration looks like, what you would like to see happen. The more you tell us, the better we can make accurate suggestions. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Daiv" wrote in message ... That was very helpfull!! Iam still unsure how to apply a function that is specified in another cell. these functions will be applied 20+ times, and they will be referencing different cells each time (in a predictable pattern) When I try this i just get the value of the cell holding the formula instead of the formula itself being applied to the cell referencing it. Thanks again! Daiv "Ragdyer" wrote: You can use Data Validation to create these drop down lists. Check out these pages of Debra Dalgleish, where you can out everything about them. http://www.contextures.com/tiptech.html Scroll down to the D's, and see all the pages referring to Data Validation. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Daiv" wrote in message ... Hi, I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
so you want a formula in the velocity column to work to the meter chosen at
that time..The problem with that is that every time you change the drop down box and choose meter 2 All velociies will change to meter two.You need to have another column in your table so you can say type in 2 for meter 2,3 for meter 3 and so on,and then use vlookup from there back to your list. -- paul remove nospam for email addy! "Daiv" wrote: Right now we have 5 different meters. the formulas are unique to each meter and the differences are not predictable on the surface. as an example, one formula is :IF(REVS=40,((REVS/t*2.17)+0.03)*0.3048,((REVS/t*2.18)+0.02)*0.3048), another is: (0.6711*revs/t)+0.0061 I have a drop down data validation list elsewhere on the sheet for the user to choose the meter used. "paul" wrote: how many meters/formulas do you have and how do the formulas differ?there will have to be an identifier on your table to know which meter/formula to use -- paul remove nospam for email addy! "Daiv" wrote: Sorry about that. i thought there was just a command or something to tell it to take the formula instead of the value. I am working with a sheet that calculates the velocity of water flow. the measurements are taken many times and each time the same calculation needs to be done. for example DISTANCE WIDTH DEPTH REVS TIME VELOCITY AREA DISCHARGE (m) (m) (m) (s) (m/s) (m2) (m3/s) 0.00 RB 0.00 1.00 1.000 0.00 0 40.0 0.006 0.000 0.000 2.00 1.000 0.00 0 40.0 0.006 0.000 0.000 3.00 1.000 0.00 0 40.0 0.006 0.000 0.000 The meter that was used to determine the REVS each have different formulas to come up with the velocity. I am wanting to get a drop box that lists the different meters (which I have accomplished), and based on the meter, have it automatically insert the correct formula in each VELOCITY cell. Currently I have the list items stored in adifferent sheet, and want to put the formula in a cell beside the list item so the list can grow/shrink easily as time goes on. The velocity formulas use the width, revs and time values in each respective row. I hope this helps. Let me know if you need more info. Daiv. "RagDyeR" wrote: You're being very general. Post back with specific examples ... what your data configuration looks like, what you would like to see happen. The more you tell us, the better we can make accurate suggestions. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Daiv" wrote in message ... That was very helpfull!! Iam still unsure how to apply a function that is specified in another cell. these functions will be applied 20+ times, and they will be referencing different cells each time (in a predictable pattern) When I try this i just get the value of the cell holding the formula instead of the formula itself being applied to the cell referencing it. Thanks again! Daiv "Ragdyer" wrote: You can use Data Validation to create these drop down lists. Check out these pages of Debra Dalgleish, where you can out everything about them. http://www.contextures.com/tiptech.html Scroll down to the D's, and see all the pages referring to Data Validation. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Daiv" wrote in message ... Hi, I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
All the measurements will be done with the same meter. When a new set of
measurememnts will be done, this template will be opened and saved as a new file. So there is no need to account for multiple meters within the same measurement set. "paul" wrote: so you want a formula in the velocity column to work to the meter chosen at that time..The problem with that is that every time you change the drop down box and choose meter 2 All velociies will change to meter two.You need to have another column in your table so you can say type in 2 for meter 2,3 for meter 3 and so on,and then use vlookup from there back to your list. -- paul remove nospam for email addy! "Daiv" wrote: Right now we have 5 different meters. the formulas are unique to each meter and the differences are not predictable on the surface. as an example, one formula is :IF(REVS=40,((REVS/t*2.17)+0.03)*0.3048,((REVS/t*2.18)+0.02)*0.3048), another is: (0.6711*revs/t)+0.0061 I have a drop down data validation list elsewhere on the sheet for the user to choose the meter used. "paul" wrote: how many meters/formulas do you have and how do the formulas differ?there will have to be an identifier on your table to know which meter/formula to use -- paul remove nospam for email addy! "Daiv" wrote: Sorry about that. i thought there was just a command or something to tell it to take the formula instead of the value. I am working with a sheet that calculates the velocity of water flow. the measurements are taken many times and each time the same calculation needs to be done. for example DISTANCE WIDTH DEPTH REVS TIME VELOCITY AREA DISCHARGE (m) (m) (m) (s) (m/s) (m2) (m3/s) 0.00 RB 0.00 1.00 1.000 0.00 0 40.0 0.006 0.000 0.000 2.00 1.000 0.00 0 40.0 0.006 0.000 0.000 3.00 1.000 0.00 0 40.0 0.006 0.000 0.000 The meter that was used to determine the REVS each have different formulas to come up with the velocity. I am wanting to get a drop box that lists the different meters (which I have accomplished), and based on the meter, have it automatically insert the correct formula in each VELOCITY cell. Currently I have the list items stored in adifferent sheet, and want to put the formula in a cell beside the list item so the list can grow/shrink easily as time goes on. The velocity formulas use the width, revs and time values in each respective row. I hope this helps. Let me know if you need more info. Daiv. "RagDyeR" wrote: You're being very general. Post back with specific examples ... what your data configuration looks like, what you would like to see happen. The more you tell us, the better we can make accurate suggestions. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Daiv" wrote in message ... That was very helpfull!! Iam still unsure how to apply a function that is specified in another cell. these functions will be applied 20+ times, and they will be referencing different cells each time (in a predictable pattern) When I try this i just get the value of the cell holding the formula instead of the formula itself being applied to the cell referencing it. Thanks again! Daiv "Ragdyer" wrote: You can use Data Validation to create these drop down lists. Check out these pages of Debra Dalgleish, where you can out everything about them. http://www.contextures.com/tiptech.html Scroll down to the D's, and see all the pages referring to Data Validation. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Daiv" wrote in message ... Hi, I have seen spreadsheets that have a drop down control that is populated by a range of cells somewhere else in the spreadsheet. I would like to use this technique but cannot seem to get it working. can anyone help me with this? Also, I am going to want to reference the value of this list and assign an equation based on it's value. Thanks for all your help!!! Daiv. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
List control
Vlookup seems to be on track but I am still having an issue somewhere. When
I have the formula I want to use for the meters, the cell displays "#VALUE" because there are no numbers for it to use while in the formula list. when I call it inot the spreadsheet the cell displays "#value" still, then it should have no problem referencing the proper cells to find the values it should use. My thoughts are that vlookup is pulling the value of the cell instead of the formula itself and applying it in it's new location. is there a way to have it pull the formula and it's cell references instead of the resulting value? Thanks., "Daiv" wrote: All the measurements will be done with the same meter. When a new set of measurememnts will be done, this template will be opened and saved as a new file. So there is no need to account for multiple meters within the same measurement set. "paul" wrote: so you want a formula in the velocity column to work to the meter chosen at that time..The problem with that is that every time you change the drop down box and choose meter 2 All velociies will change to meter two.You need to have another column in your table so you can say type in 2 for meter 2,3 for meter 3 and so on,and then use vlookup from there back to your list. -- paul remove nospam for email addy! "Daiv" wrote: Right now we have 5 different meters. the formulas are unique to each meter and the differences are not predictable on the surface. as an example, one formula is :IF(REVS=40,((REVS/t*2.17)+0.03)*0.3048,((REVS/t*2.18)+0.02)*0.3048), another is: (0.6711*revs/t)+0.0061 I have a drop down data validation list elsewhere on the sheet for the user to choose the meter used. "paul" wrote: how many meters/formulas do you have and how do the formulas differ?there will have to be an identifier on your table to know which meter/formula to use -- paul remove nospam for email addy! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control Display of Drop Down List in Validation | Excel Worksheet Functions | |||
How to Change List Based on Value Chosen in Another List | Excel Worksheet Functions | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Creating a list from an existing list. | Excel Worksheet Functions |