# Posts by jindon

• ## cast string to range

Hi,

Select the range A1:C3 and type "cast", then

Ctrl+Shift and press Enter to get out of the formula bar

Otherwise only vba solution, I think

jindon

• ## Indirect Function Usage

Hi, Kavir

It is comfusing indeed.

I think the beauty of using INDIRECT function is that it enables us to convert strings to the actual range reference in order to pass through other functions.

INDIRECT(A9&"!j5") returns East!j5
INDIRECT("A9"&"!j5") returns #REF!, because it is referring A9!j5

INDIRECT("A9") returns East
INDIRECT(A9) returns #REF!, becuase it refers the range named as East

Once you use &, every string with "" will be recognised only as text, not as reference.
So you cannot use " for the cell reference.

I'm really not sure and I may be wrong, don't trust me.

But this is how I understand this fuunction.

jindon

• ## Indirect Function Usage

Kavir

So sorry, I thought A9 would have a value like East!
If you change the value in cell A9 to East!, should work, or otherwise,

SUM(OFFSET(INDIRECT(A9&"!J5"),,,COUNT(INDIRECT(A9&"!J:J")),1))

should give you the result.

jindon

• ## Indirect Function Usage

Hi, Kavir

might be

SUM(OFFSET(INDIRECT(A9&"J5"),,,COUNT(INDIRECT(A9&"J:J")),1))

jindon

• ## returning a value based on a range of upper and lower limits

Hi,

by formula

assuming table in col. A & B, test value is in cell D1 then

=INDEX(A:B,MATCH(D1,A:A,1),2)

jindon

• ## Indirect Function Usage

Hi,

If you use INDIRECT function to refer other workbook, you need to open the referred file as well.

jindon

• ## Indirect Function Usage

Hi,

If you have 2017 in cell A5, then probably like

=SUMPRODUCT((INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!\$A\$14:\$A\$350")=\$A5)*(INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!\$C\$14:\$C\$350")>=J\$4)*(INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!\$C\$14:\$C\$350")<=DATE(YEAR(J\$4),MONTH(J\$4)+1,0))*(INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!\$E\$14:\$E\$350")))

jindon

• ## Automatically copy entire row

HI, James

I'm so glad that you've got what you desired.
I'll talk to you again next time.

All the best,

jindon

• ## Automatically copy entire row

Is this still going?

• ## Automatically copy entire row

I don't understand why it doesn't work.

The attached has the code and totally blank 2 sheets.
Copy all the data in your worksheets and paste to the attached and try.

hope it works this time.

jindon

• ## Automatically copy entire row

Hi,

If you want data in col. A, B, C, D, E and F on Sheet1 to be transferred to sheet2, the code should do it already.

Have you replaced the code? I mean have you overwrite the code?
You need to delete the old code and paste the new code to the same place.
If you do so, it never work like old one.

As you see in the code

.Resize(, 6).Copy Destination:= _
ws2.Range("a17")

is carry the data only from col. A to F.

Try copy the latest code again and replace the code.

jindon

• ## Multiple Items for Validation Filter

Hi,

table modified

• ## IF Formula Function

Hi,

The attached is only showing the idea.

jindon

• ## Add row when data changes and total different column

Hi, James

I didn't notice your second requirement,

Quote from James

I then want it to take the totals in column J and put it in the first tab in column S based again on the sequence number.

if that is the main purpose, you don't need to run macro, the forumula will do the thing.

jindon

• ## Add row when data changes and total different column

Hi,

I did it with the formula

in cell K2

=IF(ISNA(MATCH(A2,Orderlines!A:A,0)),"",SUMIF(Orderlines!A:A,A2,Orderlines!J:J))
then filldown

jindon

• ## Automatically copy entire row

Hi,

Right, up to col.F

Code
``````Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws2 As Worksheet
Set ws2 = Sheets("Sheet2")
With Target
If .Column <> 1 Then Exit Sub
.Resize(, 6).Copy Destination:= _
ws2.Range("a17")
End With
Set ws2 = Nothing
End Sub``````
• ## Automatically copy entire row

Hi, James
here's a code

Code
``````Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws2 As Worksheet
Set ws2 = Sheets("Sheet2")
With Target
If .Column <> 1 Then Exit Sub
.Resize(, 5).Copy Destination:= _
ws2.Range("a17")
End With
Set ws2 = Nothing
End Sub``````
• ## Add row when data changes and total different column

Hi,

try the attached

just run insert_sum
jindon

• ## Excel 2003 patch for Hlookup?

Hi,

hard to say anything without sample.

would you like to give some?

• ## Add row when data changes and total different column

Hi,

hope it works