Increment Value Within Function Text

**aharb** · 04-22-2013, 02:12 PM

Hi,

This is probably a dumb question and probably been covered somewhere in the forums. I search and all I could find were questions about incrementing the row/column value. I have the following function in column L:

=VLOOKUP(B8,Midnite6Read_20130415.csv!$B:$N,7,FALSE)

I want to be able to drag the function to the right to fill the cells in columns M - S. Column M needs to increment the file's date by 1 (Midnite6Read_20130416.csv) and so on for each column going right. Is there are way to make the one value within the file name in the function increment as I auto-fill it to the right?

Thanks for any help.

**Lemice** · 04-22-2013, 02:21 PM

Hello,

What you can do is removing the "$" in your formula, like

Please Login or Register  to view this content.

So when you drag it to the right, the N column will move accordingly.

**aharb** · 04-22-2013, 03:02 PM

Originally Posted by Lemice

Hello,

What you can do is removing the "$" in your formula, like

Please Login or Register  to view this content.

So when you drag it to the right, the N column will move accordingly.

Sorry if I explained it poorly. That's not what I'm wanting to do. I'm wanting this to be in column M:

Please Login or Register  to view this content.

The date value in the file name is what I'm wanting to increment. If I were to keep dragging the formula, here is what I would want for the rest of the columns:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Sorry for the poor explanation. It's hard for me to convey exactly what I'm trying to do.

Thanks.

**Lemice** · 04-22-2013, 03:09 PM

I see, then you can use an INDIRECT function, something like

Please Login or Register  to view this content.

Paste it on Column M, row 8, then drag it to the right

**aharb** · 04-22-2013, 03:45 PM

Thanks for the help. It works perfectly when I have the file open. When I close the file, it attempts to insert the full file path and doesn't work. Is there an easy way to modify it to work with the full file path? I've tried playing with it, but can't get it to work. Here's the code I receive after I close the file the function it is calling.

Please Login or Register  to view this content.

**Jonmo1** · 04-22-2013, 03:55 PM

Indirect won't work on a closed file.

The MoreFunc AddIn has a similar indirect function that will work over closed books.
Google search for MoreFunc Addin
You'll have to sort through a bunch of ads and junk to find it.

**Lemice** · 04-22-2013, 04:03 PM

You can paste the name of the file in a Cell somewhere in your workbook, and have INDIRECT use it as the path. But this can be inconvenient sometimes.