Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2008
    Location
    Bury St Edmunds
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Help!!

    In the attached workbook i have created dependant drop down lists ('Graphs' tab) using named ranges etc from the 'lookup' tab. On the last criteria selected i then need to lookup the data for that criteria and make some graphs from it. What i cant seem to work out is how to lookup that cell across the 10 worksheets and bring back that data.

    I did think about INDIRECT formula but i can get it to work properly as i am looking for a range of data rather than just one cell's data.


    Any ideas....?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In cell C21 on the Graphs sheet:

    =OFFSET(INDIRECT("'"&$C$5&" "&$C$11&"'!$BH$1"),MATCH($C$16,INDIRECT("'"&$C $5&" "&$C$11&"'!A:A"),0)+ROW()-22,0)

    and fill down to C43.

    There's a lot of repetition in these formulas; you could store the sheet name formula =C5&" "&C11 in a cell named SheetName and the offset of the doctor's name =MATCH($C$16,INDIRECT("'"&SheetName&"'!A:A"),0) in a cell named NameOffset. The formula in C21 then becomes a lot shorter:

    =OFFSET(INDIRECT("'"&SheetName&"'!$BH$1"),NameOffs et+ROW()-22,0)

  3. #3
    New Lounger
    Join Date
    Oct 2008
    Location
    Bury St Edmunds
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans for your speedy response...however I am now slightly lost and do not understand the part in the formula that references cell BH1. Plus it didnt return anything at all which has made me even more confused!

    =OFFSET(INDIRECT("'"&$C$5&" "&$C$11&"'!$BH$1"),MATCH($C$16,INDIRECT("'"&$C $5&" "&$C$11&"'!A:A"),0)+ROW()-22,0)

    Also which i failed to mention - i need to go across to column BB on the doctors/wards/specialty splits as well - so i just need to lift that entire section from the particular criteria chosen....

    I really do appreciate any help you can give!

    Kirsty

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I had assumed that you just wanted the end total from column BH (hence the $BH$1 in the formula). If you want the entire range from column H to column BH (not BB, I think), you can use

    =OFFSET(INDIRECT("'"&SheetName&"'!$H$1"),NameOffse t+ROW()-22,COLUMN()-3)

    See the attached version (I removed most of the sheets to reduce the file size, but the idea remains the same).
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Oct 2008
    Location
    Bury St Edmunds
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Brilliant Hans - you are a genius! exactly what i was after - thanks ever so much!! I totally understand it now!

    K

  6. #6
    New Lounger
    Join Date
    Oct 2008
    Location
    Bury St Edmunds
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    This still isnt working....i have done exactly what you said and have created Sheetname and Nameoffset, however when you do the match, the figures do not reconcile with the actual base data...is it something to do with the counting of the rows and columns?

    Please see attached...im working as a test on the surgical wards f5 hardy ward.....
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The table on the Graphs sheet has been moved up 3 rows from the version you originally attached. This means that the row offset has to be changed from -22 to -19: the formula for C18 (originally C21) should now be

    =OFFSET(INDIRECT("'"&SheetName&"'!$C$1"),NameOffse t+ROW()-19,COLUMN()-3)

  8. #8
    New Lounger
    Join Date
    Oct 2008
    Location
    Bury St Edmunds
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    wow....i didnt get that bit....22 rows from the top and three columns across....of course because i had 'tidied' it up and deleted some rows that changed the count from the top....excellent - its been driving me bonkers...

    you've been brilliant - thanks ever so much.

    Kirsty

Posting Permissions

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