converting letters to numbers

I have a function that runs through data and breaks up, sums, and groups different information (big thanks to Hans for helping me out with it).

Now i would like to make it more user friendly. Before i had the column numbers hard coded into the formula, but now people in the office are starting to use the function for different data sets where the columns are different. I created a simple form were people can enter the number of the column.
row start = 2
group by col = 5
sum value of col = 11

the problem is that people have a hard time figuring out that column K is 11, and would much rather type in K into the form then 11.

is there a quick and easy way (or possibly a built in function in excel) that i can do this.
I realize i could build a giant Select Case statement, but i just think there has to be a better way.

Another quick question: is there a way to create an input rule in excel forms similar to Access, where i can limit what a user inputs, for instance, the row text box can only be numbers, and the column can only be letters.

thanks

I must go now and I have not seen the previous post, but it seems to me
you need the INDIRECT function. For example, if cell K1 contains the
number 2 (row start) and you want to sum 11 consicutive cells or
something similar (11 in K2), then

=SUM(INDIRECT("A"&K1&":A"&K1+K2-1))

will calculate the sum of the range A2:A12

Is this what you need?

Kostis Vezerides

You could ask them to point at a cell to use as the starting point.

dim myCellToStart as range
dim RowStart as long
dim SumValueOfCol as long

set mycelltostart = nothing
on error resume next
set mycelltostart=application.inputbox(Prompt:="CLick on a cell",type:=8)
on error goto 0

if mycelltostart is nothing then
'user hit cancel
exit sub '???
end if

RowStart = mycelltostart.row
sumvalueofCol = mycelltostart.column


If you need two columns--one to group by and one to sum, you can ask two
questions and just extract the column from each answer.

trav wrote:
>
> I have a function that runs through data and breaks up, sums, and groups
> different information (big thanks to Hans for helping me out with it).
>
> Now i would like to make it more user friendly. Before i had the
> column numbers hard coded into the formula, but now people in the
> office are starting to use the function for different data sets where
> the columns are different. I created a simple form were people can
> enter the number of the column.
> row start = 2
> group by col = 5
> sum value of col = 11
>
> the problem is that people have a hard time figuring out that column K
> is 11, and would much rather type in K into the form then 11.
>
> is there a quick and easy way (or possibly a built in function in
> excel) that i can do this.
> I realize i could build a giant Select Case statement, but i just think
> there has to be a better way.
>
> Another quick question: is there a way to create an input rule in excel
> forms similar to Access, where i can limit what a user inputs, for
> instance, the row text box can only be numbers, and the column can only
> be letters.
>
> thanks
>
> --
> trav
> ------------------------------------------------------------------------
> trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
> View this thread: http://www.excelforum.com/showthread...hreadid=514461

--

Dave Peterson

And there is no mask like this in excel--but you could do your own checking.

ps. Instead of asking multiple questions, it's sometimes easier to just display
a userform and allow the user to fill out a couple of prompts and then continue.

trav wrote:
>
> I have a function that runs through data and breaks up, sums, and groups
> different information (big thanks to Hans for helping me out with it).
>
> Now i would like to make it more user friendly. Before i had the
> column numbers hard coded into the formula, but now people in the
> office are starting to use the function for different data sets where
> the columns are different. I created a simple form were people can
> enter the number of the column.
> row start = 2
> group by col = 5
> sum value of col = 11
>
> the problem is that people have a hard time figuring out that column K
> is 11, and would much rather type in K into the form then 11.
>
> is there a quick and easy way (or possibly a built in function in
> excel) that i can do this.
> I realize i could build a giant Select Case statement, but i just think
> there has to be a better way.
>
> Another quick question: is there a way to create an input rule in excel
> forms similar to Access, where i can limit what a user inputs, for
> instance, the row text box can only be numbers, and the column can only
> be letters.
>
> thanks
>
> --
> trav
> ------------------------------------------------------------------------
> trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
> View this thread: http://www.excelforum.com/showthread...hreadid=514461

--

Dave Peterson

Thanks for help. I ll give the question answer thing a try,

As of now i do have a form, and it works great.
at first I had two different combo boxes and some check boxes that controlled different configurations and options for layout, but now too many people are using it for many different spreadsheets.
So I reconfigured the form so it now has three text boxes.
Example
Row Start = 2 ' the 2 is in the text box, and it tells the function what row to start on
Group by Col = 5 ' this is the column that the info is grouped by
Value Col = 11 ' this is the column that they value is subtotaled for each group

so what I would like is for the user to be able to enter
Row Start = 2
Group by Col = E 'use a letter instead of number
Value Col = K ' use a letter instead of number

i just figured that was the simplest way.
The other way would be to grab the names from the first row, which are the column names, and create a drop down selection box.

This would be the preferable way, but I am not that experienced with excel.

If I were using php I would take the values and create an array as I ran through the columns of row 1,
Then I would create loop that put them into an html selection box with the value being the column number, and the text would be the column name.

Is this possible in excel? If so that would be the best way

Otherwise just being able to convert a letter to a number would be fine.

Maybe just adding a couple of refedit controls would work for you. They work a
lot like application.inputbox(type:=8).

trav wrote:
>
> Thanks for help. I ll give the question answer thing a try,
>
> As of now i do have a form, and it works great.
> at first I had two different combo boxes and some check boxes that
> controlled different configurations and options for layout, but now too
> many people are using it for many different spreadsheets.
> So I reconfigured the form so it now has three text boxes.
> Example
> Row Start = 2 ' the 2 is in the text box, and it tells the function
> what row to start on
> Group by Col = 5 ' this is the column that the info is grouped by
> Value Col = 11 ' this is the column that they value is subtotaled for
> each group
>
> so what I would like is for the user to be able to enter
> Row Start = 2
> Group by Col = E 'use a letter instead of number
> Value Col = K ' use a letter instead of number
>
> i just figured that was the simplest way.
> The other way would be to grab the names from the first row, which are
> the column names, and create a drop down selection box.
>
> This would be the preferable way, but I am not that experienced with
> excel.
>
> If I were using php I would take the values and create an array as I
> ran through the columns of row 1,
> Then I would create loop that put them into an html selection box with
> the value being the column number, and the text would be the column
> name.
>
> Is this possible in excel? If so that would be the best way
>
> Otherwise just being able to convert a letter to a number would be
> fine.
>
> --
> trav
> ------------------------------------------------------------------------
> trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
> View this thread: http://www.excelforum.com/showthread...hreadid=514461

--

Dave Peterson

i don't really understand application.inputbox(type:=8)

i don't know much about excel and vb, besides the normal stuff, so all the functions i do are pretty much form based. how do you utilize this input box, and how can i apply it to input the column number?

application.inputbox is a quick way to get a range from a user. It allows the
user to use the mouse to point and click.

If you create a simple test macro, you may find it easier to understand.

Option Explicit
Sub testme()

Dim myCellToStart As Range
Dim RowStart As Long
Dim SumValueOfCol As Long

Set myCellToStart = Nothing
On Error Resume Next
Set myCellToStart = Application.InputBox(Prompt:="CLick on a cell", Type:=8)
On Error GoTo 0

If myCellToStart Is Nothing Then
'user hit cancel
Exit Sub '???
End If

RowStart = myCellToStart.Row
SumValueOfCol = myCellToStart.Column

MsgBox RowStart & vbLf & SumValueOfCol
End Sub


trav wrote:
>
> i don't really understand application.inputbox(type:=8)
>
> i don't know much about excel and vb, besides the normal stuff, so all
> the functions i do are pretty much form based. how do you utilize this
> input box, and how can i apply it to input the column number?
>
> --
> trav
> ------------------------------------------------------------------------
> trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
> View this thread: http://www.excelforum.com/showthread...hreadid=514461

--

Dave Peterson

THANKS, that works pretty nice.

just one more question,
in a combo box, is it possible to have a name that is different then the value.

so if i have a drop down list, say

company
quantity
unit price
sale

can i have a different value associated with them like

company = 1
quantity = 2
unit price = 3
sale = 4

and if so, how do i set that in the form.

Yep.

I'm assuming that the combobox is still on that userform.

If that's correct, take a look at the .listindex property of the combobox.

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.ComboBox1.ListIndex
End Sub

The first item on the list will have an index of 0, so...

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.ComboBox1.ListIndex + 1
End Sub

If you have something weirder(?) happening, you could always build a table on
another worksheet and use =vlookup() to get the number you want.

trav wrote:
>
> THANKS, that works pretty nice.
>
> just one more question,
> in a combo box, is it possible to have a name that is different then
> the value.
>
> so if i have a drop down list, say
>
> company
> quantity
> unit price
> sale
>
> can i have a different value associated with them like
>
> company = 1
> quantity = 2
> unit price = 3
> sale = 4
>
> and if so, how do i set that in the form.
>
> --
> trav
> ------------------------------------------------------------------------
> trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
> View this thread: http://www.excelforum.com/showthread...hreadid=514461

--

Dave Peterson