Changing part of formula by 1 when you drag.

[Thranton]

Hey everyone,

I've created an excel sheet that references info from multiple different excel files. My problem is now that I've created the formula for Week 1, I also need to copy said formula 52 times down and 6 across.

The formula in question is:

='C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week 1.xls]Time'!$AG$40

The part that I need to change is the Week 1... Week 2.. Week 3 etc.

The usual method of selecting either one or two isn't working for me. Is there a quick and easy way to do this?

 

[tygrrboi]

for dragging down, try this:

=INDIRECT("'C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week " & ROWS($A$1:$A1) & ".xls]Time'!$AG$40")


I am not sure what you expect the result to be when you drag it across so I cant help you there without more information.

 

[Thranton]

I expect when I drag down that formula that the WEEK 1 part changes to WEEK 2, WEEK 3, WEEK 4......WEEK 52..

I will try this though and get back to you. Thanks!

 

[Thranton]

Not sure how to edit my last post but your suggestion didn't work

It's giving me a #REF once entered in.

I should say that the bold letters here are what I want to change by increments of one once I drag it down.

='C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week 1.xls]Time'!$AG$40

Where Week 1 is another excel file being referenced. Then Week 2 is another, week 3, week 4 and so on.

 

[tygrrboi]

have you entered it in exactly as I have written it? Without changing anything?

=INDIRECT("'C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week " & ROWS($A$1:$A1) & ".xls]Time'!$AG$40")


Also do those file names actually exist? and do they each have a sheet "Time"?

Note that you cannot drag this formula up, only down.


It might also be that the files need to be opened in order to be referenced... Have you tried it with Week 1 and Week 2 etc open? to see if the results appear?

 

[Thranton]

Oh sorry that was just to give you an idea of how many I had to do.

If I can get the dragging down to work nicely, the other 6 columns I can just type in manually (for the first one) and then drag down for the next 52.

 

[tygrrboi]

sorry I edited a previous question instead of posting a new one... reposting:


have you entered it in exactly as I have written it? Without changing anything?

=INDIRECT("'C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week " & ROWS($A$1:$A1) & ".xls]Time'!$AG$40")


Also do those file names actually exist? and do they each have a sheet "Time"?

Note that you cannot drag this formula up, only down.

It might also be that the files need to be opened in order to be referenced... Have you tried it with Week 1 and Week 2 etc open? to see if the results appear?

 

[Thranton]

It was a copy and paste. I changed the "Person" to my name. But that's about it. And yes in those excel files there's three sheets. Week, time and expenses. I will be referencing both the time and expense sheets in each file, but in different columns.

 

[tygrrboi]

If this is something that you just want to do once to prevent having to edit 50 rows of data... you might want to do the following.

put this in the cell.
="'C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week " & ROWS($A$1:$A1) & ".xls]Time'!$AG$40"
drag down as far as you want.
Then copy and paste the whole range, when you paste it make sure it pastes as a Value, not as a formula. You should see that the cells now have the correct path to your files listed.

Then go into the Find/Replace menu and
replace 'C:
with ='C:
for the whole range. That should convert it into the formula that you wanted.


This method is not dynamic (in that it wont adjust itself as you change the spreadsheet... but it might suit your purpose.

 

[Thranton]

When I enter that line:

="'C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week " & ROWS($A$1:$A1) & ".xls]Time'!$AG$40"

it keeps it as a value (as in that entire line shows up in the box). Which looks like what you described it should do.

I then go and replace the 'C: with ='C:

="='C:\Users\Person\Dropbox\Work Related Forms\Time Sheets 2016\[Week " & ROWS($A$1:$A1) & ".xls]Time'!$AG$40"

but it keeps it as a value in the box (just as before).


Now this sheet I will be using for 2016 and on, I.E for 2017 and 2018. This excel file is a self audit in which it pulls the times that I have entered into my Week 1, Week 2, Week 3, etc. files and pulls them directly into this. This allows me to easily keep track of all my time that I have put down and then compare it to what the company has pad me for. There for I will be changing it slightly for 2017 and 2018 etc.

I could somehow link my two excel sheets (audit and week 1) and then you'd get a better understanding of my issue.