Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute Reference (again)
First time posting here, but a question that most of you will probably have
heard before. I'm trying to sort out a formula which involves taking data from one sheet in the work book and using it on another sheet. Say for example, Sheet1 and Sheet2. The data I want in Sheet1 is all on row 17, but I want to use this on column B in Sheet2 The formula I currently have is B1 =+Sheet1'A17 when I try to copy this down column B, it turns into B2 =+Sheet1'A18 B3 =+Sheet1'A19 I tried adding an absolute value on the 17, ie B1 =+Sheet1'A$17 and copying down, but end up with B2 =+Sheet1'A$17 B3 =+Sheet1'A$17 What I'm after is B2 =+Sheet1'B$17 B3 =+Sheet1'C$17 etc etc etc preferably without resorting to VBA or macros. I know it's possible to do if you're going down the row instead of across, but I can't figure this out! ANy help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute Reference (again)
Enter *anywhere* and copy down:
=INDEX(Sheet1!$17:$17,ROWS($1:1)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "LCC Jon-Kun" <LCC wrote in message ... First time posting here, but a question that most of you will probably have heard before. I'm trying to sort out a formula which involves taking data from one sheet in the work book and using it on another sheet. Say for example, Sheet1 and Sheet2. The data I want in Sheet1 is all on row 17, but I want to use this on column B in Sheet2 The formula I currently have is B1 =+Sheet1'A17 when I try to copy this down column B, it turns into B2 =+Sheet1'A18 B3 =+Sheet1'A19 I tried adding an absolute value on the 17, ie B1 =+Sheet1'A$17 and copying down, but end up with B2 =+Sheet1'A$17 B3 =+Sheet1'A$17 What I'm after is B2 =+Sheet1'B$17 B3 =+Sheet1'C$17 etc etc etc preferably without resorting to VBA or macros. I know it's possible to do if you're going down the row instead of across, but I can't figure this out! ANy help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute Reference (again)
Thanks for having a look at this, but it doesn't appear to be working. This
seems to highlight cell A2 in Sheet2 when I look at the ROW(A2) reference, and I'm not 100% sure why. The INDIRECT command is a new one on me, as is ADDRESS, so I'm looking at the help files for them and trying to puzzle this one out. All I know is that this isn't copying the data from Sheet1 to Sheet2. Oh, and I've been a bit of an idiot and missed out the "!" between 'Sheet1' and the cell number in the example below. Been staring at these spreadsheets for far too long... "Bob Phillips" wrote: Try this =INDIRECT("'Sheet1'!"&ADDRESS(17,ROW(A2))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LCC Jon-Kun" <LCC wrote in message ... First time posting here, but a question that most of you will probably have heard before. I'm trying to sort out a formula which involves taking data from one sheet in the work book and using it on another sheet. Say for example, Sheet1 and Sheet2. The data I want in Sheet1 is all on row 17, but I want to use this on column B in Sheet2 The formula I currently have is B1 =+Sheet1'A17 when I try to copy this down column B, it turns into B2 =+Sheet1'A18 B3 =+Sheet1'A19 I tried adding an absolute value on the 17, ie B1 =+Sheet1'A$17 and copying down, but end up with B2 =+Sheet1'A$17 B3 =+Sheet1'A$17 What I'm after is B2 =+Sheet1'B$17 B3 =+Sheet1'C$17 etc etc etc preferably without resorting to VBA or macros. I know it's possible to do if you're going down the row instead of across, but I can't figure this out! ANy help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
absolute reference | Excel Worksheet Functions | |||
absolute reference | Excel Discussion (Misc queries) | |||
absolute reference | Excel Discussion (Misc queries) | |||
Absolute Reference | Excel Discussion (Misc queries) | |||
Absolute Reference | Excel Worksheet Functions |