Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
References to worksheets in formulae
Hi, I've got a vlookup and the range I want looked at can be one of four tables, each of these is on a different sheet. Currently I either use an if: if(V2=1, vlookup..., if(V2=2, vlookup... and so on or I filter the data on the value that affects the range I want looked at and only type then copy the correct formula in each case. I was wondering, however, if there is any way to include the value in the lookup table array. The reference to different worksheets is "'-Name of steet'!-" Could I replace this somehow with a cell reference so that if I named the sheets the same as the values that affect the result this calculates automatically. Or am I being daft and should carry on as I am. Any help/guidance would be appreciated Andy -- andyiain ------------------------------------------------------------------------ andyiain's Profile: http://www.excelforum.com/member.php...fo&userid=8335 View this thread: http://www.excelforum.com/showthread...hreadid=560170 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
References to worksheets in formulae
andyiain wrote:
Hi, I've got a vlookup and the range I want looked at can be one of four tables, each of these is on a different sheet. Currently I either use an if: if(V2=1, vlookup..., if(V2=2, vlookup... and so on or I filter the data on the value that affects the range I want looked at and only type then copy the correct formula in each case. I was wondering, however, if there is any way to include the value in the lookup table array. The reference to different worksheets is "'-Name of steet'!-" Could I replace this somehow with a cell reference so that if I named the sheets the same as the values that affect the result this calculates automatically. Or am I being daft and should carry on as I am. Any help/guidance would be appreciated Andy I think you can use the INDIRECT function: if the sheets are in the same workbook there's no problem, but if they are in different workbooks, the workbok with the sheets need to be opened because otherwise the formula returns the REF! error. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
References to worksheets in formulae
Hi Franz, thanks for the reply. I'm not sure how one would use indirect here though. I thought indirect would go to a cell reference, I need a reference to a worksheet as part of the array reference in a vlookup. Regards, Andy -- andyiain ------------------------------------------------------------------------ andyiain's Profile: http://www.excelforum.com/member.php...fo&userid=8335 View this thread: http://www.excelforum.com/showthread...hreadid=560170 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
References to worksheets in formulae
andyiain wrote:
Hi Franz, thanks for the reply. I'm not sure how one would use indirect here though. I thought indirect would go to a cell reference, I need a reference to a worksheet as part of the array reference in a vlookup. Regards, Andy Hi Andy, You can "build" the reference to a worksheet using the indirect function; for example, the formula: =INDIRECT("'"&B4&"'!B15") is a reference to the cell B15 of the sheet which name is in cell B4 of the sheet in which the formula is written. If you need more help, maybe you could upload an example file to www.savefile.com -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto-increment data source cell references when copying cha | Charts and Charting in Excel | |||
How do you copy and rename linked worksheets? | Excel Discussion (Misc queries) | |||
Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
Excel needs to have the ability to insert "SUB" worksheets | Excel Worksheet Functions | |||
Replicating Formulas with Various Worksheet References | Excel Worksheet Functions |