Posts by Akr

Re: Convert Lotus .WR1 files into Excel file type

Thanks AAE!
I tried these earlier, none of them could solve my problem. However, I found ABC Amber Lotus Convertor, looks like this will solve my problem

Regards,

Akr

I have some lotus spreadsheet files with extension .WR1. These files were created in early 80's possible using Lotus 123. I need to open & read these files. Can somebody convert these files to excel or suggest software to buy.

Many Thanks!

Akr

Re: Indirect reference to dynamic range for Data Validation

Thanks a lot, Dave!

I'll go thru it. Hope it will solve my problem.

Regards,

AK

Hi,

I have worksheet where I have to assign a dynamic range as validation list to a cell (F6) through an indirect reference. I tried as in attached file but it does not work.

Any help would be highly appreciated.

Thanks

AK

Re: Dynamic range in reverse order

Thanks a lot, Tom. With little modification this will do the job.

Regards,

AK

Re: Dynamic range in reverse order

Tom,

Thanks for your reply. Obviously, all the cells above first blank cell are not necessarily blank. User defined VBA functions is a good idea and can be used in my worksheet. Let me know if you have something readily available.

Thanks & Regards,

AK

Re: Dynamic range in reverse order

Xlite Thanks for your help.

I am looking for non-VB solution.

Regards,

AK

Hi,

Normally we create Dynamic Ranges in ascending order, which is anchored at a top cell and goes down in the columns till it meets a blank cell. I have a problem wherein I have to create a dynamic range in a reverse order. For example the range starts a cell say “A100” and goes up in the column A99, A98, A97 …till it finds a blank cell.

Hope my problem is clear.

Any help would be highly appreciated.

Thanks

AK

Re: Update Links from a password protected workbook

Try the following code

Workbooks.Open "C:\Docs\JS\ODS\April 2005\Systems Down Time April.xls", Password:="systems", WriteResPassword:="systems",UpdateLinks:=0

Good luck!

Re: Worksheet selection change event and copy-paste

Thanks Tom and Bill for your help.

My worksheet selection change event code is as below-

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim r_ac, r_gr, r_sgr, r_shp As Range
    Set r_ac = Worksheets("Codes").Range("ac_table")
    Set r_gr = Worksheets("Codes").Range("gr_table")
    Set r_sgr = Worksheets("Codes").Range("subgr_table")
    Set r_shp = Worksheets("Codes").Range("shp_table")
    If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
        If Target.Value <> "" Then
            If Target.Column > 1 And Target.Column < 7 And Target.Row > 2 Then
                If Target.Column = 2 Or Target.Column = 3 Then Application.StatusBar = WorksheetFunction.VLookup(Target.Value, r_ac, 2)
                If Target.Column = 4 Then Application.StatusBar = WorksheetFunction.VLookup(Target.Value, r_gr, 2)
                If Target.Column = 5 Then Application.StatusBar = WorksheetFunction.VLookup(Target.Value, r_sgr, 2)
                If Target.Column = 6 Then Application.StatusBar = WorksheetFunction.VLookup(Target.Value, r_shp, 2)
            End If
        Else
            Application.StatusBar = ""
        End If
    Else
        Application.StatusBar = ""
    End If
    Sheets("Monthly_Sum").CommandButton1.Enabled = True
    Sheets("Expenses_Sum").CommandButton1.Enabled = True
    Sheets("SingleAccount").CommandButton1.Enabled = True
    Sheets("SingleGroup").CommandButton1.Enabled = True
End Sub

The code does the required operation. I can copy a cell but when I change selection to paste it I loose the clipboard and can not perform paste.

Regards,

AK

Hi,

I have a worksheet with selection change event, which works fine. The problem is, I am not able to use copy-paste cells because of it. Any ideas to overcome this problem.

Thanks,

AK

Re: Namingf a range on multiple worksheets

Hi JF,

Try following code in all worksheets -

Private Sub Worksheet_Activate()
    ActiveWorkbook.Names("lane1r1").Delete
    ActiveWorkbook.Names.Add Name:="lane1r1", RefersToR1C1:= _
        "=R5C9:R24C10"
End Sub

Good luck!

Re: #N/A error on VLOOKUP

VLOOKUP formula works only if your data is sorted in ascending order

Re: #N/A error on VLOOKUP

VLOOKUP formula does not work if your data is sorted in ascending order.

Re: searching for a string and removing unnecessary data

You may use the formula

=RIGHT(A13,LEN(A13)-FIND("\",A13,FIND("\",A13,1)+1))

Good Luck!

Re: Filtering Calculated Fields

Hi,

You can use a custom formula. See attached file.

Good Luck!

Re: Capture a cell value from each 5 previous sheets into active sheet

Done!!

All the best

Re: Capture a cell value from each 5 previous sheets into active sheet

Hi Gopal,

Hopefully this is what u are looking for.

Good luck!

Re: Deleting a set number of characters in a cell

Put your value in cell A1
and try using formula =RIGHT(A1,LEN(A1)-8) in cell A2.

Good Luck!

Re: Capture a cell value from each 5 previous sheets into active sheet

you have to press F9 after adding each sheet

Good Luck!