Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill Down formula Help Please
I'm trying to fill the same formula (links) down a column on a worksheet. I'm linking from the same cell from a months worth of files,only the file reference is changing as shown below.(11-1-05,11-2-05,etc..) Cell A1='C:\Reports\Nov\[11-1-05.xls]Production'!$Z$1 CellA2='C:\Reports\Nov\[11-2-05.xls]Production'!$Z$1 CellA3 ='C:\Reports\Nov\[11-3-05.xls]Production'!$Z$1 Thanks, Greg -- singlgl1 ------------------------------------------------------------------------ singlgl1's Profile: http://www.excelforum.com/member.php...o&userid=26389 View this thread: http://www.excelforum.com/showthread...hreadid=478606 |
#2
|
|||
|
|||
Fill Down formula Help Please
Hi, Try this: In a helper column starting in row 1 type this: ="'C:\Reports\Nov\[11-"&ROW()&"-05.xls]Production'!$Z$1" and copy down as needed. then for your formula use: =INDIRECT(B1) B1 being your helper column HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=478606 |
#3
|
|||
|
|||
Fill Down formula Help Please
Hi!
The only way to do what you want requires that each of those files be open. You don't want all those files to be open do you? Biff "singlgl1" wrote in message ... I'm trying to fill the same formula (links) down a column on a worksheet. I'm linking from the same cell from a months worth of files,only the file reference is changing as shown below.(11-1-05,11-2-05,etc..) Cell A1='C:\Reports\Nov\[11-1-05.xls]Production'!$Z$1 CellA2='C:\Reports\Nov\[11-2-05.xls]Production'!$Z$1 CellA3 ='C:\Reports\Nov\[11-3-05.xls]Production'!$Z$1 Thanks, Greg -- singlgl1 ------------------------------------------------------------------------ singlgl1's Profile: http://www.excelforum.com/member.php...o&userid=26389 View this thread: http://www.excelforum.com/showthread...hreadid=478606 |
#4
|
|||
|
|||
Fill Down formula Help Please
No, I can't open all of the files at once so I guess that's ruled out. I Guess I could fill down as usual, then edit each formula to reflect the file that I need referenced.I was hoping there was an easier way to accomplish this.Thanks for any help on this -- singlgl1 ------------------------------------------------------------------------ singlgl1's Profile: http://www.excelforum.com/member.php...o&userid=26389 View this thread: http://www.excelforum.com/showthread...hreadid=478606 |
#5
|
|||
|
|||
Fill Down formula Help Please
The *easy* way is to use Indirect(), but that has it's shortcomings.
You must have all WBs *open* to use the data. It'll take a little work in order to be able to create the formulas (links) necessary so that you can access the data and *not* have to have the other WBs *open*. You'll start with a Text formula so it can increment, and then convert it to a real formula to calculate (return data). Start with an out of the way "helper" column, say AA. Enter this formula in the row where you would like your data to start to display in the main portion of your sheet, say AA20: ="='C:\Reports\Nov\[11-"&ROW(1:1)&"-05.xls]Production'!$Z$1" Don't be concerned that what you see in the formula bar does *not* match what you see in the cell. Now, click the fill handle and drag down to copy as far as needed. You'll see that the cells display your incremented WB names. NOW, while all the cells are *still* selected from the copy, right click in the selection and choose "Copy". Navigate to the main portion of your sheet and *right* click in the first cell that you wish to use for the data display and click on "Paste Special". Click on "Values", then <OK. AGAIN, while the cells are *still* selected from the Paste Special, click on <Edit <Replace And in the "Find What" box enter *= And in the "Replace With" box enter = Then click "Replace All". You should now have your links established where you can access your data from open or closed WBs, as long as you update your links whenever you open this main sheet. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "singlgl1" wrote in message ... I'm trying to fill the same formula (links) down a column on a worksheet. I'm linking from the same cell from a months worth of files,only the file reference is changing as shown below.(11-1-05,11-2-05,etc..) Cell A1='C:\Reports\Nov\[11-1-05.xls]Production'!$Z$1 CellA2='C:\Reports\Nov\[11-2-05.xls]Production'!$Z$1 CellA3 ='C:\Reports\Nov\[11-3-05.xls]Production'!$Z$1 Thanks, Greg -- singlgl1 ------------------------------------------------------------------------ singlgl1's Profile: http://www.excelforum.com/member.php...o&userid=26389 View this thread: http://www.excelforum.com/showthread...hreadid=478606 |
#6
|
|||
|
|||
Fill Down formula Help Please
Thanks ragdyer, your suggestion :) worked great!! -- singlgl1 ------------------------------------------------------------------------ singlgl1's Profile: http://www.excelforum.com/member.php...o&userid=26389 View this thread: http://www.excelforum.com/showthread...hreadid=478606 |
#7
|
|||
|
|||
Fill Down formula Help Please
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "singlgl1" wrote in message ... Thanks ragdyer, your suggestion :) worked great!! -- singlgl1 ------------------------------------------------------------------------ singlgl1's Profile: http://www.excelforum.com/member.php...o&userid=26389 View this thread: http://www.excelforum.com/showthread...hreadid=478606 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula will not fill correctly due to odd number rows | Excel Worksheet Functions | |||
Fill formula question | Excel Worksheet Functions | |||
Possible? formula in one cell can fill another cell | Excel Worksheet Functions | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) |