Increasing Cell Reference to External Workbook by Fixed Number of Rows

[yehhaaa]

hi. simple question i hope.

Say in my summary worksheet in cell A1 I have the formula:

='C:\Documents\[Document Name.xls]Sheet1'!$C$1

And say in cell A2 I need to have the formula

='C:\Documents\[Document Name.xls]Sheet1'!$C$16

and in cell A3 I need to have the formula

='C:\Documents\[Document Name.xls]Sheet1'!$C$31

...so that (in the example above) the number of rows increases by 15 (or some other fixed number) each time I go down one row in my master sheet.

How do I write this as a formula so I can auto - complete (ie expand these down my master sheet rows without having to manually change the row number each time.

PS: I am completely crap at Excel - so won't be able to pull off anything fancy.

Any help much appreciated.

Thanks!!

 

[sijpie]

The easiest is to take your first two entries. Edit each and put a ' at the start (before the =)
Now you have converted them nto text ending with a number.
Select both cells, go to the bottom right corner to change the cursor into the plus, and drag the cursor down to cpy the cells with the increasing numbers at the end.

Now go back to the first cell. press F2 to edit, home to get to the front and delete to delete the '. The Enter. now down to the next cell and repeat.

 

[yehhaaa]

hi sijpie

thanks very very much. its a really workable solution.

if i could find a way to delete the ' before the = by doing a find and replace that would really be great. unfortunately excel doesn't seem to be able to find the ' before the = when using CTRL-F.

anyhow, this is still a fair bit quicker than adding the numbers in my head and editing each formula.

if anyone knows a simple way to automate it other than this still interested.

thanks again sijpie. awesome help.

 

[sijpie]

well you could do it with a macro, but as you said you were not too good at excel, I gave you a simple workable solution.

I just discovered an even easier way

Instead of copying
='C:\Documents\[Document Name.xls]Sheet1'!$C$1
down like I said, chang the = into a qq
like
qq'C:\Documents\[Document Name.xls]Sheet1'!$C$1
qq'C:\Documents\[Document Name.xls]Sheet1'!$C$16

Now copy this duo down.
Then do a find and replace for qq with =

 

[yehhaaa]

You are a bloody legend (australian slang for "well done").

Thanks a heap (australian slang for "i am grateful).

you've just save me buckets of time (i actually have a bucket here that I keep my time in).



sijpie for president!

 

[sijpie]

sijpie for president!

and clean up that watery mess in Queensland? no thanks...

To think I was planning to go to Brisbane for Broken Hill a long time ago. Where are you based?

 

[yehhaaa]

I'm in Sydney.