Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Link
I am working on a workbook with around 50 worksheets in it. These worksheets
contain values (amounts of money to be precise) and i wish to create links in a worksheet at the front to each of these. The worksheet at the front where I want the links to be has the names of the other worksheets in different rows. However the data in the linked sheets have data vertically in columns. The formula i'm using looks like this =FLOTTA!$D25 where FLOTTA is one of the sheet names I can enter the first link fine. Because I have hundreds of them I want to copy the formula across so that it picks up the data in the following cell down and so on... So, I want the 25 to change to 26, 27, 28 and so on.... but it does not change when I replicate it across the row. Is there a way to get around this? .. However, when I try to replicate it, the row number does not change even if i dont have a '$ sign infront of it. How can I get the row number to increase sequentially? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Link
This might work (I tried it on a small scale, if I understand your question
correctly)... Instead of copying your formula across the columns, first copy it down the rows until you get all of the rows you needed. Then do a find/replace and replace $D with $D$. Then copy your range and do a paste special - transpose where you want the formulas to show up in columns. I hope I explained that right. Good luck! "Neilly" wrote: I am working on a workbook with around 50 worksheets in it. These worksheets contain values (amounts of money to be precise) and i wish to create links in a worksheet at the front to each of these. The worksheet at the front where I want the links to be has the names of the other worksheets in different rows. However the data in the linked sheets have data vertically in columns. The formula i'm using looks like this =FLOTTA!$D25 where FLOTTA is one of the sheet names I can enter the first link fine. Because I have hundreds of them I want to copy the formula across so that it picks up the data in the following cell down and so on... So, I want the 25 to change to 26, 27, 28 and so on.... but it does not change when I replicate it across the row. Is there a way to get around this? . However, when I try to replicate it, the row number does not change even if i dont have a '$ sign infront of it. How can I get the row number to increase sequentially? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Link
Why would you expect the row number to change if you copy across?
If you copy down they will increase which is logical but if you copy across the column will increase unless you use absolute reference. You would need to add a formula like for instance =OFFSET(FLOTTA!$D$25,COLUMNS($A:A)-1,) or =INDEX(FLOTTA!$D:$D,COLUMNS($A:Y)) the latter is not volatile Both of these when copied across will lookup the row numbers -- Regards, Peo Sjoblom "Neilly" wrote in message ... I am working on a workbook with around 50 worksheets in it. These worksheets contain values (amounts of money to be precise) and i wish to create links in a worksheet at the front to each of these. The worksheet at the front where I want the links to be has the names of the other worksheets in different rows. However the data in the linked sheets have data vertically in columns. The formula i'm using looks like this =FLOTTA!$D25 where FLOTTA is one of the sheet names I can enter the first link fine. Because I have hundreds of them I want to copy the formula across so that it picks up the data in the following cell down and so on... So, I want the 25 to change to 26, 27, 28 and so on.... but it does not change when I replicate it across the row. Is there a way to get around this? . However, when I try to replicate it, the row number does not change even if i dont have a '$ sign infront of it. How can I get the row number to increase sequentially? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Link
That is superb man! The offset one works brilliant!
What would make it even quicker would be that the sheet names changed too when i copied it down. At the moment I'm copying the formula you gave me and re-entering the sheet name where 'FLOTTA' was originally. The sheet names are to the left of the first cell im entering your formula into. These names are also hyperlinked to the sheets themselves. Any way to include that? "Peo Sjoblom" wrote: Why would you expect the row number to change if you copy across? If you copy down they will increase which is logical but if you copy across the column will increase unless you use absolute reference. You would need to add a formula like for instance =OFFSET(FLOTTA!$D$25,COLUMNS($A:A)-1,) or =INDEX(FLOTTA!$D:$D,COLUMNS($A:Y)) the latter is not volatile Both of these when copied across will lookup the row numbers -- Regards, Peo Sjoblom "Neilly" wrote in message ... I am working on a workbook with around 50 worksheets in it. These worksheets contain values (amounts of money to be precise) and i wish to create links in a worksheet at the front to each of these. The worksheet at the front where I want the links to be has the names of the other worksheets in different rows. However the data in the linked sheets have data vertically in columns. The formula i'm using looks like this =FLOTTA!$D25 where FLOTTA is one of the sheet names I can enter the first link fine. Because I have hundreds of them I want to copy the formula across so that it picks up the data in the following cell down and so on... So, I want the 25 to change to 26, 27, 28 and so on.... but it does not change when I replicate it across the row. Is there a way to get around this? . However, when I try to replicate it, the row number does not change even if i dont have a '$ sign infront of it. How can I get the row number to increase sequentially? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Link
Unless there is some logic to the sheet names like an index number e.g.
Sheet1 Sheet2 and so on If that's the case it can be quite easily fixed but if they have no logical connection you would need to use a macro (or type them) to extract all sheet names, maybe put that list somewhere on a hidden sheet and refer to it when you copy. So it's doable, it's even rather easy if they have the same name and a number that changes but a bit cumbersome if they are just different names. -- Regards, Peo Sjoblom "Neilly" wrote in message ... That is superb man! The offset one works brilliant! What would make it even quicker would be that the sheet names changed too when i copied it down. At the moment I'm copying the formula you gave me and re-entering the sheet name where 'FLOTTA' was originally. The sheet names are to the left of the first cell im entering your formula into. These names are also hyperlinked to the sheets themselves. Any way to include that? "Peo Sjoblom" wrote: Why would you expect the row number to change if you copy across? If you copy down they will increase which is logical but if you copy across the column will increase unless you use absolute reference. You would need to add a formula like for instance =OFFSET(FLOTTA!$D$25,COLUMNS($A:A)-1,) or =INDEX(FLOTTA!$D:$D,COLUMNS($A:Y)) the latter is not volatile Both of these when copied across will lookup the row numbers -- Regards, Peo Sjoblom "Neilly" wrote in message ... I am working on a workbook with around 50 worksheets in it. These worksheets contain values (amounts of money to be precise) and i wish to create links in a worksheet at the front to each of these. The worksheet at the front where I want the links to be has the names of the other worksheets in different rows. However the data in the linked sheets have data vertically in columns. The formula i'm using looks like this =FLOTTA!$D25 where FLOTTA is one of the sheet names I can enter the first link fine. Because I have hundreds of them I want to copy the formula across so that it picks up the data in the following cell down and so on... So, I want the 25 to change to 26, 27, 28 and so on.... but it does not change when I replicate it across the row. Is there a way to get around this? . However, when I try to replicate it, the row number does not change even if i dont have a '$ sign infront of it. How can I get the row number to increase sequentially? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Link
Yes it just different names altogether. It would be magic if I could do that!
Would save hours of work! Thanks for all your help! Will definitely give you a shout if I need any more help! Top man! "Peo Sjoblom" wrote: Unless there is some logic to the sheet names like an index number e.g. Sheet1 Sheet2 and so on If that's the case it can be quite easily fixed but if they have no logical connection you would need to use a macro (or type them) to extract all sheet names, maybe put that list somewhere on a hidden sheet and refer to it when you copy. So it's doable, it's even rather easy if they have the same name and a number that changes but a bit cumbersome if they are just different names. -- Regards, Peo Sjoblom "Neilly" wrote in message ... That is superb man! The offset one works brilliant! What would make it even quicker would be that the sheet names changed too when i copied it down. At the moment I'm copying the formula you gave me and re-entering the sheet name where 'FLOTTA' was originally. The sheet names are to the left of the first cell im entering your formula into. These names are also hyperlinked to the sheets themselves. Any way to include that? "Peo Sjoblom" wrote: Why would you expect the row number to change if you copy across? If you copy down they will increase which is logical but if you copy across the column will increase unless you use absolute reference. You would need to add a formula like for instance =OFFSET(FLOTTA!$D$25,COLUMNS($A:A)-1,) or =INDEX(FLOTTA!$D:$D,COLUMNS($A:Y)) the latter is not volatile Both of these when copied across will lookup the row numbers -- Regards, Peo Sjoblom "Neilly" wrote in message ... I am working on a workbook with around 50 worksheets in it. These worksheets contain values (amounts of money to be precise) and i wish to create links in a worksheet at the front to each of these. The worksheet at the front where I want the links to be has the names of the other worksheets in different rows. However the data in the linked sheets have data vertically in columns. The formula i'm using looks like this =FLOTTA!$D25 where FLOTTA is one of the sheet names I can enter the first link fine. Because I have hundreds of them I want to copy the formula across so that it picks up the data in the following cell down and so on... So, I want the 25 to change to 26, 27, 28 and so on.... but it does not change when I replicate it across the row. Is there a way to get around this? . However, when I try to replicate it, the row number does not change even if i dont have a '$ sign infront of it. How can I get the row number to increase sequentially? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link a Master Template Excel file to different excel source files | Excel Worksheet Functions | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
How to create a link between(Excel) Gantt chart and Excel source | Setting up and Configuration of Excel |