Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Variable sheets in a formula (2000 and up)

    Hi, I have a problem which I think more people know, so the answer should be out there.

    I am making an printable form in Excel with a lot of variables which i want to set in a dialog. My problem is that with one of the variables a lookup function should change the sheet it is looking in. The sheets are filled with exchange rates on different moments in time. Every moment has its own tab. Based on the experience of the users it is not possible to put everything in one sheet. Or maybe it is but i don't want to think of explaining to them how they should do it.

    I should be able to differ the reference to a sheet based on the input on the dialog.

    Does anyone know this problem and how to solve it. I have run into it a couple of times this year and really need a solution this time.

    Thanks in advance for your idea's

    GRTZ Forbaty

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable sheets in a formula (2000 and up)

    You can use the INDIRECT function. You can include a reference to a cell that contains a sheet reference and this cell can be the result of a lookup.

    If you need a more specific example, could you provide a question with some more details so we can provide a more specific example.

    Steve

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Variable sheets in a formula (2000 and up)

    Steve,

    Thanks for your answer. Sorry I react so late but Year end closing busy busy busy. I thought of the INDIRECT function but could not think of a way how put a sheet reference in a cell. The sheets are called 0301; 0302; etc. And more how am I going to use this result in another lookup.

    Greating Forbaty

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variable sheets in a formula (2000 and up)

    See attached workbook for an example, using the INDIRECT function. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable sheets in a formula (2000 and up)

    If Sam's example doesn't answer your question. If you post an more detailed request of what you are trying to accomplish, we could provide an example that is more applicable to what you are trying to do.

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Variable sheets in a formula (2000 and up)

    Thanks Sammy,

    It worked great.

  7. #7
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Variable sheets in a formula (2000 and up)

    Sammy,

    I now want to do this with external links. I tried something but I keep having problems with the syntax. Could you help me again?

    GRTZ Forbaty

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable sheets in a formula (2000 and up)

    An external reference looks like this:<pre>='[wopr.xls]2004'!$B$2</pre>

    If you want to use INDIRECT, you must concatenate the various parts, for example:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Workbook:</td><td>wopr.xls</td><td><-- for testing enter wopr.xls</td><td align=center>2</td><td>Worksheet:</td><td align=center>2004</td><td><-- for testing enter 2004 or 2003</td><td align=center>3</td><td>Person:</td><td align=center>John</td><td><-- for testing use Sam or John</td></table>
    In cell B4, enter this formula:<pre>=VLOOKUP($B$3,INDIRECT("'["&$B$1&"]"&$B$2&"'!$A$1:$B$2"),2,FALSE)</pre>

    Note: external links with INDIRECT only work if the workbook referred to is open, otherwise, you'll get #REF. If you want to be able to refer to cells in a closed workbook indirectly, download Laurent Longre's free MoreFunc add-in from Excel add-ins; it contains a custom function INDIRECT.EXT that handles references to closed external workbooks.

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variable sheets in a formula (2000 and up)

    Hans is quick & correct. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Variable sheets in a formula (2000 and up)

    Hans

    I think this add-in will give me a lot of fun. Thanks.
    I used to think that i was pretty good in Excel. Knowing this site has down graded it to a mere beginner.

    Regards Forbaty
    (Marcel de Vlieger)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable sheets in a formula (2000 and up)

    >> Knowing this site has down graded it to a mere beginner.

    You'd better stop visiting here then <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    (Just joking!)

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable sheets in a formula (2000 and up)

    Keep coming back and you'll soon rise to be a pro!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •