cannot copy formula to below cell with numerical increase in increments of 1

[Special.Agent]

Hi,
I am a new user here, thanks in advance for any help you may be able to offer.
I have a formula that links a cell to a cell in another excel document.
I need to use this formula many times, linking it to many different documents. all these documents are saved as job numbers, 8202, 8203, 8204 etc.

='I:\Common\DSL\[8109.xlsx]Sheet1'!D$7

Thats the formula I am using. by using the dollar signs i am able to drag the formula into the next cells in a row, just the column will change and the row number will remain 7.
However, I need to drag this formula into the cells beneath, and wish to have the 8109 to change to 8110, 8111, 8112 etc, referring to the next document to link to.

It is possible to do this? I am not familiar with VB, hopefully there might be a simple solution.

Best Regards,
Fergal

[Special.Agent]

done. although i dont know how to phrase it

[JBeaucaire]

That portion of the formula does not "increment" that way when you copy downward. I am assuming all of these links are to closed workbooks from which you are retrieving info?

There is an ADDIN called MOREFUNC you could install that would give you 100s of additional functions, one of which is called INDIRECT.EXT(), a function that would allow you to "construct" a reference with text strings and in doing so you could create a formula that would increment that particular "string" the way you want as you copy it down. INDIRECT.EXT() would assemble the pieces of the reference to create the final reference, then retrieve the data from that closed workbook.

http://www.excelforum.com/excel-gene...ndirect.html#2


Once you've installed MOREFUNC, the formula you could try to start would look something like:

=INDIRECT("'I:\Common\DSL\[" & ROW($A8109) & ".xlsx]Sheet1'!" & ADDRESS(7,COLUMN(D1)))

...copied across from there.

[Special.Agent]

Hi,

Many thanks for your above, however it is beyond my capabilities at present.
I wonder is there another way for me to do this? I'll post exactly what I wish to achieve, maybe there is a simpler solution:

I have an excel sheet "Ships Log" containing maybe 30 different columns.
the number of rows will be in the hundreds, each job uses a new row.
Each row is populated with times and other information relating to that job.
The difficulty is that this worksheet is so large, and linked to other sheets in the same workbook, that entering the information becomes difficult and somewhat messy.
To solve this I had hoped to create an excel file for each job, the information entered here would automatically be updated to the "Ships Log".
any other docs we have would then draw the required information from the ships log.

Does that make sense? any ideas on how I could do this with relative ease?

Many thanks again for reading
Fergal

[JBeaucaire]

I didn't have any problems understanding the scenario. It is a common one. The way to resolve this with formulas is to install the MOREFUNC addin, which is not beyond your capabilities.


On the other hand, I wouldn't do what you're doing, if using Excel as a "database" (which it isn't!), then the data should be kept in one workbook, actually in one sheet, 1 row per project.

Then you can create other sheets that "draw" from that one database sheet to display your project information in a cool "report" format, and simply changing one key cell on the form would draw/display a different project. This is far superior working environment than the one you're trying for.

[JBeaucaire]

You may be at the point you're ready to start using VBA to create a methodology of using one PROJECT sheet to enter data into, then press a button and have that data added to your DATABASE sheet for you in the proper layout. The same PROJECT sheet could have a button to "Retrieve" data from the database into the PROJECT sheet based on the Project Name you would have already entered.

That might alleviate your messiness.

I can help you construct a basic macro and help you understand it so you can edit it later. Post up a sample 2-sheet workbook... one sheet showing the layout of your PROJECT input, the second being the same information in a DATABASE format. You only need show one project on the two sheets for use to be able to construct the macro.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[Special.Agent]

Hi Jerry,
Thanks very much for your time on this, it really is much appreciated.
The VBA sounds ideal and certainly worth a try. I have attached a sample worksheet, it doesn't have all the columns that the final sheet will have but should be ok as an example.
Best Regards,
Fergal

[JBeaucaire]

This will allow you to designate in row1 what cells are to be collected from each source file (code) entered in column B. When the entry is made in column B, a string is created using that code and the cell reference from row1, then entered into that row, continuing across the row.

Please Login or Register  to view this content.

This macro is in the sheet module.