Posts by t.palinkas

Yes! That is perfect!

...once you see it lol.

Thanks so much, saved my day,.

EDIT:

If I use this in a cell:

=INDEX(price_list[P1]&CHAR(44)&price_list[P2]&CHAR(44)&price_list[P3],MATCH(A2,price_list[PN],0))

I get the expected 3 values separated by a comma. I used CHAR(44) instead of "," because I thought it was conflicting the double quotes in the INDIRECT("..."). However, this does not work in the Data Validation formula:

=INDIRECT("INDEX(price_list[P1]&CHAR(44)&price_list[P2]&CHAR(44)&price_list[P3],MATCH(A2,price_list[PN],0))")

It accepts this, but says resolves to an error and no items in dropdown:

=INDEX(INDIRECT("price_list[P1]")&CHAR(44)&INDIRECT("price_list[P2]")&CHAR(44)&INDIRECT("price_list[P3]"),MATCH(A2,INDIRECT("price_list[PN]"),0))

I feel like this should work... Once I get that figured, I want to replace the "A2" reference with a offset but i'm not sure if that is possible yet.

I'm having some trouble creating a Data Validation List formula that works.

I have the following Price list table: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 64"]Item[/TD]
[TD="width: 64"]LIST[/TD]
[TD="width: 64"]P1[/TD]
[TD="width: 64"]P2[/TD]
[TD="width: 64"]P3[/TD]

Item1

$5.00

$4.00

$7.00

$2.00

Item2

$6.00

$5.00

$3.00

$5.00

Item3

$4.00

$3.00

$5.00

$7.00

Item4

$5.00

$5.00

$2.00

$3.00

[/TABLE]
On my Main sheet I have a table as follows: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 64"]Item[/TD]
[TD="width: 64"]LIST[/TD]
[TD="width: 98"]Price[/TD]

Item1

$5.00

DROPDOWN

Item3

$4.00

DROPDOWN

[/TABLE]
What I'm trying to do is on the main sheet, based on the selection on the item column, I need the 3 prices (P1, P2, P3) to be in a dropdown in the "Price" column.

to get the list column i am using.

INDEX(price_list
[LIST],MATCH(B14,price_list[PN],0))

I thought maybe by using INDIRECT I could do that 3 times separated by comma's in the DV formula but that doesn't work. I would also have to go to each cell and increment the cell reference (B14). I would like to use OFFSET in the datavalidation formula but I cannot get anything to work. Is this possible?

Re: Get the cell under a lookup result

Sure thing.

First Part:

If(ISERROR(Logical Test, Do this part if there is an error, Or do this if there is no error))

Basically this allows you to stretch this formula over more cells then you would get results. IE if spanned 5 cells, and only had 3 results, the last two cells would simply be blank, instead of show an error.

The main part of the formula is:

INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

You'l notice this is used twice, once as the logical test, and again as what to do if there is no error. Below are some links on INDEX and SMALL functions. But as you copy the formula down, you will notice ROW(1:1) will increment each time. When small returns the array of values that match the query in column 2, ROW tells which result to return 1,2,3....

INDEX(array, row_num, [column_num])
https://support.office.com/en-…c&ui=en-US&rs=en-CA&ad=CA

SMALL( array, nth_position )
http://www.techonthenet.com/excel/formulas/small.php

As another note, using this way, if you were to add other items under dinner1, you wouldn't need to insert them directly underneath the other dinner1, you can just add at the end and this formula would find info. Can be treated like a database.

I hope this helps!

Re: #VALUE error on VB function call when using IF statement in formula

Here is a simple example book. Thanks!

FYI I also tried this (not in example book)

=IF(CellEntry()<>"",CellEntry(),IF((G44<>0)*AND(H44<>0),IF(INDEX




(companies_range[special_rates],MATCH($F$14,companies_range[company_name],0))




="",INDEX(labour_range[1],MATCH(G45,labour_range[Unit],0)),INDEX(INDIRECT




("labour_range["&INDEX(companies_range[special_rates],MATCH($F




$14,companies_range[company_name],0))&"]"),MATCH(G43,labour_range[Unit],0)))
,""))

Re: Get the cell under a lookup result

How about using INDEX to return a array of values. Try something like this. make sure you press CTRL+SHIFT+ENTER to let excel know you are using an array formula. Then copy (drag) that formula down how ever many lines you need. The key here is putting dinner1,dinner2 in every row.

[TABLE="class: grid, width: 500"]

dinner1

dinner1

starter1

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

dinner1

starter2

dinner1

starter3

dinner2

starter4

dinner2

starter5

dinner2

starter6

[/TABLE]

Will this work for you?

I'm not even quite sure how to explain this other then the title. I will just provide examples and some explanation.

This works:

=IF(CellEntry()<>"",CellEntry(),IFERROR(INDEX(INDIRECT("labour_range["&INDEX(companies_range[special_rates],MATCH($F$14,companies_range[company_name],0))&"]"),MATCH(G45,labour_range[Unit],0)),""))

Adding an additional index in place of the "" does not:

=IF(CellEntry()<>"",CellEntry(),IFERROR(INDEX(INDIRECT("labour_range["&INDEX(companies_range[special_rates],MATCH($F$14,companies_range[company_name],0))&"]"),MATCH(G45,labour_range[Unit],0)),INDEX(labour_range[1],MATCH(G45,labour_range[Unit],0))))

the CellEntry function is something I found online a while back the allows the user to overwrite the result of a formula in a cell while still retaining the formula. I can post the source if required, but I'm not sure it is the porblem. I have tried a few different things such as:

=IF(CellEntry()<>"",CellEntry(),IF((G44<>0)*AND(H44<>0),INDEX(INDIRECT("labour_range["&IFERROR(INDEX(companies_range[special_rates],MATCH($F$14,companies_range[company_name],0)),1)&"]"),MATCH(G44,labour_range[Unit],0)),""))

But it seems adding any other IF or other statement causes a #VALUE Error immediately on CellEntry()

What the formula does is find the company name in the companies table, get the special rate number, and lookup that rate in the labor_range table and match with the unit number. What I am trying to accomplish is if the company_name is not found in the table default to a special rate of 1.

Thanks

Re: Using a formula as column name in INDEX/MATCH

Yup. Works great Thanks!

Hello.

I'm thinking there might be a simple solution to this but I can't seem to find it. What I need to do is use the result from one table query as the column name to return in a separate query. Excel calls and error at a formula inside the square brackets. My formula is below:

=INDEX(labour_range[INDEX(companies_range[special_rates],MATCH($F$14,companies_range[company_name],0)],MATCH(G43,labour_range[Unit],0),))

Use the result from this:

=INDEX(companies_range[special_rates],MATCH($F$14,companies_range[company_name],0)

and use it in the spot marked HERE:

=INDEX(labour_range[HERE],MATCH(G43,labour_range[Unit],0),))

Thanks in advance

Re: Passing variable from button and using it in userform initialize

Thanks that worked perfectly as soon as i changed from Userform_initialize to activate it worked perfect. Thanks so much.

I have a variable that loads a userform sets a variable then shows the userform. Everything was working good, then i decided to make a change to the initialize part which would use that variable. i cant seem to figure out how to set the variable before the userform is loaded and have it transfer through.

This is my code for the button:

Sub Button1_Click()
    With Publish
        Dim rngSheetInfo As Range
        Set .rngSheetInfo = ThisWorkbook.Worksheets("Summary").Range("B5")
        .TicketNum.Caption = .rngSheetInfo.Value
        .Show
    End With
End Sub

And this is my Userform_Initialize

Private Sub UserForm_Initialize()
If rngSheetInfo.Offset(0, 25).Value >= 1 Then
    OriginalCopy.Value = False
    OriginalCopy.Enabled = False
Else
    OriginalCopy.Value = True
    OriginalCopy.Enabled = True
End If
If rngSheetInfo.Offset(0, 24).Value >= 1 Then
    OfficeCopy.Value = False
    OfficeCopy.Enabled = False
Else
    OfficeCopy.Value = True
    OfficeCopy.Enabled = True
End If
If rngSheetInfo.Offset(0, 25).Value >= 1 Then
    CustomerCopy.Value = False
    CustomerCopy.Enabled = False
Else
    CustomerCopy.Value = False
    CustomerCopy.Enabled = True
End If
Printer.Value = True
pdf.Value = False
Location.Text = ThisWorkbook.Worksheets("Summary").Range("J2")

rngSheetInfo is declared as public in the userform.
Can anyone help? It would be greatly appreciated

Re: #Value Error with protected sheet with UserInterFaceOnly True

I read through that but dont seem to understand why im having a pproblem. I forgot to mention that it works flawlessly when the sheet is unprotected. The UDF is not modifying the book, just saves the formula if someone enters a value into the cell, if the entered value is deleted, the formulas value will be put back in. Forgive me if im missign something.

In my workbook i am using some code i found online to allow users to overwrite the result of a formula if needed but not overwrite the formula. the problem is that when the sheets are protected all the cells that are using this formula get a #VALUE error as soon as any change is made to the workbook. I have code that protects all sheets with UserInterfaceOnly enabled when the workbook is opened. I also tried protecting and unprotecting the sheet when the code is ran, but it didnt seem to work and it slows excel down. I have looked high and low for a solution, but cant find one. can anyone help me

In an unportected cell i am using:
=IF(CellEntry()<>"",CellEntry(),IF(SUM(A13)>0,VLOOKUP(B13,PriceList,2,FALSE),""))

In ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim myRange As Range, oneCell As Range, xVal As Variant
On Error Resume Next
Set myRange = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each oneCell In myRange
    With oneCell
        If .HasFormula Then
        Rem formula entered
            If InStr(LCase(.FormulaR1C1), functionName) = 0 Then
                Rem non-ce formula entered, delete ce-Validation
                .Validation.Delete
            Else
                Rem new ce formula entered, update stored formula
                xVal = CellEntry(oneCell)
            End If
        Else
            If InStr(LCase(.Validation.ErrorMessage), functionName) = 0 Then
                Rem cell has non-CE validation
            Else
                Rem set new value for CellEntry and replace formula in cell
                .Validation.InputMessage = CStr(.Value)
                Application.EnableEvents = False
                .FormulaR1C1 = .Validation.ErrorMessage
                Application.EnableEvents = True
            End If
        End If
    End With
Next oneCell
End Sub

In A Module:

Function CellEntry(Optional ByVal inputCell As Range) As Variant
Rem returns the text last entered in the cell
Rem validation.InputMessage holds CellEntry value:  .ErrorMessage holds formula
On Error Resume Next
If inputCell Is Nothing Then Set inputCell = Application.Caller
On Error GoTo 0
If inputCell Is Nothing Then
    CellEntry = vbNullString
Else
    With inputCell.Range("a1").Validation
        On Error Resume Next
        If IsNumeric(.InputMessage) Then
            CellEntry = CDbl(.InputMessage)
        Else
            CellEntry = .InputMessage
        End If
        If .Parent.Address <> Application.Caller.Address Then Exit Function
        On Error GoTo 0
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertInformation, Formula1:="=(1=1)"
        .ErrorMessage = .Parent.FormulaR1C1
        .InputMessage = CellEntry
        .ShowInput = False
        .ShowError = False
    End With
End If
End Function

Re: Paste on worksheet removes all validation

ok i must be blind i fixed it
This is what i did correct me if its not proper
Thanks

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim myRange As Range, oneCell As Range, xVal As Variant
On Error Resume Next
Set myRange = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each oneCell In myRange
    With oneCell
        If .HasFormula Then
        Rem formula entered
            If InStr(LCase(.FormulaR1C1), functionName) = 0 Then
                Rem non-ce formula entered, delete ce-Validation
                .Validation.Delete
            Else
                Rem new ce formula entered, update stored formula
                xVal = CellEntry(oneCell)
            End If
        Else
            If InStr(LCase(.Validation.ErrorMessage), functionName) = 0 Then
                Rem cell has non-CE validation
            Else
                Rem set new value for CellEntry and replace formula in cell
                .Validation.InputMessage = CStr(.Value)
                Application.EnableEvents = False
                .FormulaR1C1 = .Validation.ErrorMessage
                Application.EnableEvents = True
            End If
        End If
    End With
Next oneCell
End Sub

Re: Paste on worksheet removes all validation

Hi thanks ya i was pretty sure this code was to blame, since i cleared the rest of code in the book except for that. The same code is also causing an error if i protect the sheet. I need this code because its pretects a formula but allows the user to overwrite the value that the formula comes up with if the person wants to. Im not sure how to tweak it because i cant see the connection between it and pasting to cells.

Here is the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim myRange As Range, oneCell As Range, xVal As Variant
On Error Resume Next
Set myRange = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each oneCell In myRange
    With oneCell
        If .HasFormula Then
        Rem formula entered
            If InStr(LCase(.FormulaR1C1), functionName) = 0 Then
                Rem non-ce formula entered, delete ce-Validation
                .Validation.Delete
            Else
                Rem new ce formula entered, update stored formula
                xVal = CellEntry(oneCell)
            End If
        Else
        Rem text entered
            If Application.CutCopyMode Then
                .Validation.Delete
            Else
                If InStr(LCase(.Validation.ErrorMessage), functionName) = 0 Then
                    Rem cell has non-CE validation
                Else
                    Rem set new value for CellEntry and replace formula in cell
                    .Validation.InputMessage = CStr(.Value)
                    Application.EnableEvents = False
                    .FormulaR1C1 = .Validation.ErrorMessage
                    Application.EnableEvents = True
                End If
            End If
        End If
    End With
Next oneCell
End Sub

Re: Paste on worksheet removes all validation

Here is a simple sample of my book that i made to show what is happening. Notice the validation dropdowns under material and labour headers. Then just type something in the description of work area or anywhere for that matter, even the cell does not contain validation, copy the cell and paste it in the same place on the next sheet notice the validation drop down clearsforum.ozgrid.com/index.php?attachment/34373/

In my workbook all worksheets have the same data validation drop down lists. if i copy information from cells on one sheet and paste to another it removes all validation everywhere on the sheet pasted to. This even happens when copy and pasting to/from cells without validation. Can someone help i can figure out why it is doing this.

Re: FileDialog Cancel Button Error

I actually figured it out, This is what i did

Private Sub ChooseLocation_Click()
    Dim MyFolder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then
            MyFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    Location = MyFolder & "\"
End Sub

I cant seen to get the cancel button to work properly. i know how to make it work by the If XXX = False... method, but since i need to use with i can figure out how to make it work properly. What Do i need to change? Where do i add the If vbCancel = False, or something else?

Heres my code

Private Sub ChooseLocation_Click()
    Dim MyFolder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    MyFolder = .SelectedItems(1)
    End With
    Location = MyFolder & "\"
End Sub

Re: pass range to userform

Thanks That worked Perfect, So Simple