Copy Path Down to Next Row but Change Workbook Name

[Code Ghost]

Good morning,

I'll try to be concise.
I am retrieving a value in currency from a saved Invoice to a Record of Invoices Workbook (Two pictures are inserted for reference.)

I am actually retrieving seven different payments from a column in the saved Invoice to a row in the Record to calculate pending balances.
The row in the Invoice with the balances contains merged cells (I am not sure this is of consequence).

I have manually copied the path from an invoice saved as 20008 (no reason for this) to the next cell in the row below. I, then, modified the path to reflect the next Invoice. Instead of copying a payment from invoice 20008, now it will copy a payment from invoice 20009.
Thank God, as I saved the new invoice 20009, the first two payments were copied to the record giving me a new balance (I did not yet copy down the other columns for the rest of the payments).
Now I need to modify the path for the next payments in columns R to W (The payments are recorded in rows Q to W).

For each column, I need, in addition to changing the invoice number, to change the row that reflects the payment in the invoice:
Column Q reflects payment in row 38.
Column R reflects payment in row 39.
And so on.

If I had to modify the path to 100 rows I would do it manually, but I need to do this for at least 10,000 rows accounting for a year in sales.
I think VBA would help, but it's just beyond me at the moment.

I beg of you, o, wise ones! (I just read that in an Avengers comic)
I appreciate in anticipation all the help and advice given.

 

[etaf]

you have shown images - difficult to see
but in the image you refer to a spreadsheet name of 20008.xls in cell Q3
when you copy down to Q4 you want the same path BUT
20009.xls

and when you copy from column Q to R,S etc, you want the row to change

again in the image you have
A138
so when you move from Q3 to R3 = you want A139

Q3 = 20008.xlsx]invoice!A138
R3 = 20008.xlsx]invoice!A139

Q4 = 20009.xlsx]invoice!A138
R4 = 20009.xlsx]invoice!A139

have i understood or totlaly missed

i'm wondering if you could use an indirect() and column() +xxx or row() +xxxx
EDIT
On reflection
but that would be 1000's of indirect() and so NOT good
maybe VBA is the answer , which i do not provide on forums

 

[Code Ghost]

you have shown images - difficult to see
but in the image you refer to a spreadsheet name of 20008.xls in cell Q3
when you copy down to Q4 you want the same path BUT
20009.xls

and when you copy from column Q to R,S etc, you want the row to change

again in the image you have
A138
so when you move from Q3 to R3 = you want A139

Q3 = 20008.xlsx]invoice!A138
R3 = 20008.xlsx]invoice!A139

Q4 = 20009.xlsx]invoice!A138
R4 = 20009.xlsx]invoice!A139

have i understood or totlaly missed

i'm wondering if you could use an indirect() and column() +xxx or row() +xxxx
EDIT
On reflection
but that would be 1000's of indirect() and so NOT good
maybe VBA is the answer , which i do not provide on forums

Hi etaf,
Yes, you got it.
It is AI instead of A1, but, for practical reference, it's the same.
Thank you for your answer!

 

[etaf]

its difficult to see the details in the IMAGE what cell you are in , and the path details -

as i edited , i think 10,0000 rows with indirect() would not be great as its a volatile function
so somthing like - in the first CELL - you would put

=("this is the path["&ROW()+19870&".xls]rest of path!AI"&COLUMN()+21)
I have used the row() i'm in and the number to get the correct name , same for column - hope you can see that in the xl2bb at the end

you can see here how using the column() and row() references will change the number ,I think as you want

Now if you wrap that around an INDIRECT() is should use all the info within a function

so you could alter
=("this is the path["&ROW()+19870&".xls]rest of path!AI"&COLUMN()+21)

and put
INDIRECT(=("this is the path["&ROW()+19870&".xls]rest of path!AI"&COLUMN()+21))

to simply i have just used a sheet name of 20008 and a cell A38 (i know its AI - but just to simply
=INDIRECT((ROW()+19870&"!A"&COLUMN()+21))

this is with the indirect() function

Book5
	Q
138	this is sheet 20008 cell A38
Sheet1

Cell Formulas
Range	Formula
Q138	Q138	=INDIRECT((ROW()+19870&"!A"&COLUMN()+21))

This is in sheet 20008

Book5
	A
38	this is sheet 20008 cell A38
20008

here you can see it changing as copied across and down - from the 1 cell

Book5
	Q	R	S	T
138	this is the path[20008.xls]rest of path!AI38	this is the path[20008.xls]rest of path!AI39	this is the path[20008.xls]rest of path!AI40	this is the path[20008.xls]rest of path!AI41
139	this is the path[20009.xls]rest of path!AI38	this is the path[20009.xls]rest of path!AI39	this is the path[20009.xls]rest of path!AI40	this is the path[20009.xls]rest of path!AI41
140	this is the path[20010.xls]rest of path!AI38	this is the path[20010.xls]rest of path!AI39	this is the path[20010.xls]rest of path!AI40	this is the path[20010.xls]rest of path!AI41
141	this is the path[20011.xls]rest of path!AI38	this is the path[20011.xls]rest of path!AI39	this is the path[20011.xls]rest of path!AI40	this is the path[20011.xls]rest of path!AI41
142	this is the path[20012.xls]rest of path!AI38	this is the path[20012.xls]rest of path!AI39	this is the path[20012.xls]rest of path!AI40	this is the path[20012.xls]rest of path!AI41
Sheet1

Cell Formulas
Range	Formula
Q138:T142	Q138	=("this is the path["&ROW()+19870&".xls]rest of path!AI"&COLUMN()+21)

 

[etaf]

i have put the sample onto dropbox - maybe easier then xl2bb
BUT will only be on for a few days after the thread is solved - or another member offers a better solution VBA maybe

Dropbox

www.dropbox.com

 

[Code Ghost]

i have put the sample onto dropbox - maybe easier then xl2bb
BUT will only be on for a few days after the thread is solved - or another member offers a better solution VBA maybe

Dropbox

www.dropbox.com

Hi etaf!

That looks great!
I downloaded the sample from Dropbox. Thank you!
I think that will solve my dilemma.
I'll try it tomorrow morning and let you know how it works.
Thanks a million!

 

[etaf]

That looks great!

well, as i say indirect is volatile - so it is recalculated every time a change on the spreadsheet , and so can slow a spreadsheet down , especially if you have 10,000+ entries
so it may be OK on a sample - BUT not in the real data , and if there are other volatile functions used as well

Excel performance - Improving calculation performance

Discover how to improve workbook performance by focusing on calculation improvements in Excel 2016.

learn.microsoft.com

A volatile function is always recalculated at each recalculation even if it does not seem to have any changed precedents. Using many volatile functions slows down each recalculation, but it makes no difference to a full calculation.

Some of the built-in functions in Excel are obviously volatile: RAND(), NOW(), TODAY(). Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO().

Some functions that have previously been documented as volatile are not in fact volatile: INDEX(), ROWS(), COLUMNS(), AREAS().

 

[Code Ghost]

well, as i say indirect is volatile - so it is recalculated every time a change on the spreadsheet , and so can slow a spreadsheet down , especially if you have 10,000+ entries
so it may be OK on a sample - BUT not in the real data , and if there are other volatile functions used as well

Excel performance - Improving calculation performance

Discover how to improve workbook performance by focusing on calculation improvements in Excel 2016.

learn.microsoft.com

Hi etaf,
I've been reading about the INDIRECT function and I'm afraid it will indeed slow down my spreadsheet.
I am going to try it for now anyway.
If it seriously affects the workflow, I'll look for another solution.
Still, I'll try your sample and let you know.
Fingers crossed!

 

[Code Ghost]

Hi etaf,

Slows down. Pity.

Thanks a lot for your input and your time!

Have a good one!

 

[etaf]

shame,
you need VBA i suspect then - or some formula wich is NOT volatile
I dont provide VBA solutions to forums, so hopefully a member , who does , may see the post and answer soon
offset is also volatile