Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to have a variable cell reference across sheets?
Hello - I need to reference a variable row number in another sheet, based on
the results (value) of a row in my current sheet. Does anyone know how I can do this? i know how to reference another sheets value (='Sheet1'!A1). But in this case, I want to reference Row N based on the value of a cell on my current sheet. The example I can come up with - which I know doesn't work, but may help explain what I'm getting at - is: ='Sheet2'!B'Sheet1'!B2 In this case I would be trying to reference the value of B2 (lets say Sheet1 cell B2 value is 10) - so I'd be tryint reference the value of cell "B10" on Sheet2. Basically, the row number in the 2nd sheet is variable depending on the value of a local sheet cell value. Does that make sense? Thank you for any and all help/pointers. I've spent the last 3 hours searching the Excel help and online tips and help - but haven't been able to figure out how to do this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to have a variable cell reference across sheets?
Hi , No solution for you! However, by co-incidence I have made a very similar query on the forum just minutes earlier. Hopefully someone will sort us both out. See my "Dynamic Flexible Cell Address" query. -- dewsbury ------------------------------------------------------------------------ dewsbury's Profile: http://www.excelforum.com/member.php...o&userid=16084 View this thread: http://www.excelforum.com/showthread...hreadid=520300 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to have a variable cell reference across sheets?
Name your range in the second sheet. I named mine "thetable" in this example. In my example, it gives the item in column 2, and the proper row for the value that is in cell B2. =INDEX(thetable,B2,2) Hope that's what you were looking for! Shane Gibson Wrote: Hello - I need to reference a variable row number in another sheet, based on the results (value) of a row in my current sheet. Does anyone know how I can do this? i know how to reference another sheets value (='Sheet1'!A1). But in this case, I want to reference Row N based on the value of a cell on my current sheet. The example I can come up with - which I know doesn't work, but may help explain what I'm getting at - is: ='Sheet2'!B'Sheet1'!B2 In this case I would be trying to reference the value of B2 (lets say Sheet1 cell B2 value is 10) - so I'd be tryint reference the value of cell "B10" on Sheet2. Basically, the row number in the 2nd sheet is variable depending on the value of a local sheet cell value. Does that make sense? Thank you for any and all help/pointers. I've spent the last 3 hours searching the Excel help and online tips and help - but haven't been able to figure out how to do this. -- kevindmorgan ------------------------------------------------------------------------ kevindmorgan's Profile: http://www.excelforum.com/member.php...o&userid=32232 View this thread: http://www.excelforum.com/showthread...hreadid=520300 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to have a variable cell reference across sheets?
"kevindmorgan" wrote:
Name your range in the second sheet. I named mine "thetable" in this example. In my example, it gives the item in column 2, and the proper row for the value that is in cell B2. =INDEX(thetable,B2,2) Kevin, That worked perfectly. Thanks for the help. Here's what I did - selected my table in "Sheet2", named it "appreciation", then in "Sheet1", I referred to it as follows: =INDEX(appreciation,C20,2) This referenced the value of C20 on Sheet1 (say "10") and used that as the Row Number in the Named Table "appreciation". In this case "2" is column "B" - odd why we have to switch back to numerical references for column when it's defined as alpha by default. Thanks Kevin. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Can you have fixed cell reference when dragging/copying formulae? | Excel Discussion (Misc queries) | |||
Cell Reference locking | Excel Discussion (Misc queries) | |||
Getting contents of a cell when cell reference is in the sheet | Excel Discussion (Misc queries) | |||
can you use a variable or cell reference in a getpivotdata formul. | Excel Worksheet Functions |