Need Help! - desired number to increment does not increment

[arif89]

I've got a problem which required me to copy data from other workbook to target workbook.

Main workbook name: ABC
Target workbook from : 123

The problem is when i copy from Target workbook to Main workbook after enter =

it look like this ='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[801 bungkai.xls]1a'!$D$21

when i try to drag down i want it to be like this

='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[801 bungkai.xls]1a'!$D$21
='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[802 bungkai.xls]1a'!$D$21
='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[803 bungkai.xls]1a'!$D$21

....until
='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[820 bungkai.xls]1a'!$D$21

but it only be like this

='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[801 bungkai.xls]1a'!$D$21
='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[801 bungkai.xls]1a'!$D$21
='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[801 bungkai.xls]1a'!$D$21
='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[801 bungkai.xls]1a'!$D$21
='\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[801 bungkai.xls]1a'!$D$21

...
How to solve this problem, please help..
i copy from other target workbook to main workbook..

 

[Gary's Student]

In A1 enter:

="'\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[" & 800+ROW() & "bungkai.xls]1a'!$D$21"

and copy down.

 

[LloydFinancials]

You'll have to change the references, but this should get you going. 2 macros.

Sub OpenAll()
Application.ScreenUpdating = False
Dim i As Long
Dim j As Long
For i = 801 To 820
  Workbooks.Open ("H:\Save.72815\Excel\In Progress\" & i & " bungkai.xlsx")
Next i
End Sub

Sub CopyDown()
Application.ScreenUpdating = False
Dim s As String
Dim j As Long
Dim k As Long
With Workbooks("Need Help! - desired number to increment does not increment").Sheets("Sheet1")
  s = .Range("A1").Formula
  k = 802
  For j = 2 To 20
    Range("A" & j).Value = Left(s, InStr(s, "[")) & k & Right(s, Len(s) - InStr(s, "[") - 3)
    k = k + 1
  Next j
End With
Application.ScreenUpdating = True
End Sub

 

[arif89]

thx guys for reply, will try tomorrow, hope it works

 

[arif89]

well guys,i have tried it but it wont work.

Forgot to mention that the link ="'\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[" & 800+ROW() & "bungkai.xls]1a'!$D$21"

supposed to turn into a reflection of digit on the target workbook.

I mean, when i copy a data from target workbook which is 14.5, then paste it to other workbook , it should appear in the main workbook cell 14.5 same like in target workbook.

but when i close the target workbook and put my cursor at the digit, it will show like this ="'\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[" & 800+ROW() & "bungkai.xls]1a'!$D$21"


but appear in the cell 14.5


still need help, please

 

[FDibbins]

In A1 enter:

="'\\L_QA_PC5\movt inspection report\movt data\2016\4R\0816\[" & 800+ROW() & "bungkai.xls]1a'!$D$21"

and copy down.

That wont work unless you put it inside the INDIRECT() function, and INDIRECT only works on open workbooks

 

[arif89]

So here i want this two number for X and SD taken from target workbook example 501.xls to my main workbook example Cal 4R movement data 2016 Blank.xls


currently, i manually replace the link ='C:\Users\HP\Downloads\movtreportcal4r501510\[501.xls]1a'!$D$21
to ='C:\Users\HP\Downloads\movtreportcal4r501510\[502.xls]1a'!$D$21 and the number shown in column 502 will appear 44.9 (refer picture)

but when i drag down, the link only became like this
='C:\Users\HP\Downloads\movtreportcal4r501510\[501.xls]1a'!$D$21
='C:\Users\HP\Downloads\movtreportcal4r501510\[501.xls]1a'!$D$21
='C:\Users\HP\Downloads\movtreportcal4r501510\[501.xls]1a'!$D$21
='C:\Users\HP\Downloads\movtreportcal4r501510\[501.xls]1a'!$D$21

while the cellblock dragged shown 14.5 until below..like picture below

 

[FDibbins]

As I said, you could do this using gary's suggestion, but you would need to put that inside san INDIRECT statement, and INDIRECT only works on open workbooks/files - will the source files be open?

 

[arif89]

If the source file need to open during transfering data, that will be ok...but when someone who doesn't have an access to the target workbook and can't open it, is the data can be shown in the main workbook?

 

[FDibbins]

The (Last) answer will be shown until anything is entered - anywhere - then the formula will show an error