1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF INDIRECT SEARCH multiple worksheets.

Discussion in 'Ask an Excel Question' started by e_var, Apr 20, 2017.

  1. e_var

    e_var New Member

    Messages:
    20
    This code is designed to search multiple worksheets and display the clinic ID number based on a dropdown box (cell B2) value the user selects...
    If they select 'June' then it will search WSheets 'June', and 'JuneFlu'

    Code (vb):
    =IF(ISBLANK(INDIRECT(""&$B$2&"!$A"&ROW()-1)),IF(ISBLANK(INDIRECT(""&$B$2&"Flu"&"!$B3")),"",INDIRECT(""&$B$2&"Flu"&"!$B3")),INDIRECT(""&$B$2&"!$A"&ROW()-1))

    The problem I have is when it searches the WSheet 'JuneFlu' it starts at the row where the first part of the search stopped (found blank cell). If there are 35 clinic in the 'June' WSheet, the second part of the search starts looking at row 35 on WSheet 'JuneFlu'.

    How can I get it to start at the first row on each WSheet? I attempted to use SMALL but could not get it to work.

    Thanks everyone.

    Attached Files:

    Last edited: Apr 20, 2017
  2. Mike86

    Mike86 Member

    Messages:
    162
    You need to correct for the number of lines in "June" and subtract the current row count (row()). By modifying your function slightly, this can be done. For example (from your cell Schedule!A72):

    =IF(ISBLANK(INDIRECT(""&$B$2&"!$A"&ROW()-1)),IF(ISBLANK(INDIRECT(""&$B$2&"Flu"&"!$B"&ROW()-COUNTA(June!A:A))),"",INDIRECT(""&$B$2&"Flu"&"!$B"&ROW()-COUNTA(June!A:A))),INDIRECT(""&$B$2&"!$A"&ROW()-1))

    I've changed the "row()-row()+3" to "ROW()-COUNTA(June!A:A)". This adjusts for the current row in Schedule and takes you to the first row of JuneFlu. The function should now work correctly in all rows. You'll need to apply this idea to the other cells in Schedule.
  3. e_var

    e_var New Member

    Messages:
    20
    Thanks, it works!
    A tweak if I may... upon showing this to one other person who would be using the shared book... It will be easier if I copy all the clinic info from all WSheets onto the 'Schedule' WS. Can the code be adjusted to gather all the clinic ID's from all the WS, The other fields I need to include I can then Index Match to the ID.
    After The clinics are indexed on to the 'schedule' they can simply do an advance filter to show the upcoming clinic details they need to see

    I'm working on it now.... Thanks again
  4. Mike86

    Mike86 Member

    Messages:
    162
    @e_var The code can be adjusted using the same logic. Basically, you just keep nesting the IF statements. With your existing spreadsheet, it'd look something like this:

    =IF(ISBLANK(INDIRECT("June!$A"&ROW()-1)),IF(ISBLANK(INDIRECT("JuneFlu!$B"&ROW()-COUNTA(June!A:A))),IF(ISBLANK(INDIRECT("July!$B"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)+1)),IF(ISBLANK(INDIRECT("August!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)+3)),IF(ISBLANK(INDIRECT("Sept!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)+4)),IF(ISBLANK(INDIRECT("Oct!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)-COUNTA(Sept!A:A)+5)),IF(ISBLANK(INDIRECT("Nov!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)-COUNTA(Sept!A:A)-COUNTA(Oct!A:A)+6)),IF(ISBLANK(INDIRECT("Dec!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)-COUNTA(Sept!A:A)-COUNTA(Oct!A:A)-COUNTA(Nov!A:A)+7)),"",INDIRECT("Dec!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)-COUNTA(Sept!A:A)-COUNTA(Oct!A:A)-COUNTA(Nov!A:A)+7)),INDIRECT("Nov!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)-COUNTA(Sept!A:A)-COUNTA(Oct!A:A)+6)),INDIRECT("Oct!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)-COUNTA(Sept!A:A)+5)),INDIRECT("Sept!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)-COUNTA(August!A:A)+4)),INDIRECT("August!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)-COUNTA(July!A:A)+3)),INDIRECT("July!$A"&ROW()-COUNTA(June!A:A)-COUNTA(JuneFlu!B:B)+1)),INDIRECT("JuneFlu!$B"&ROW()-COUNTA(June!A:A))),INDIRECT("June!$A"&ROW()-1))

    Now, I don't know what you're ultimately trying to do with this, but I have a suggestion. Don't use tabs to separate the data for each month. You've already got a Date column. Put everything into one table and use filters or a PivotTable to pull out the information you want. I think you'd find that much easier.
  5. e_var

    e_var New Member

    Messages:
    20
    I was afraid it would be a long one... I tend to get lost in the code... miss a ")" or a comma...
    If you can follow along below.... It is easier to run the reports by month, related to the auto highlighting of what has changed in each cell or clinic added when the shared WB is merged.

    If you have any ideas on an easier way... PLEASE give them.

    Designing a workbook to schedule staff for 2 different types of clinics, clinic parameters (date, time in, time out, number of staff required) update frequently. The clinic schedules are generated by a corp sales team, I can run a query and export into excel, it contains columns/data that aren't needed, but is easier to keep and extract the info I will use.
    requirements:
    1. ability for more than one person to update partial data. (shared workbook, use compare and merge)
    2. highlight/track updated clinic info, and added clinics.
    My design:
    1. Create a Master shared workbook
      1. One WSheet for each month and clinic type = 24 (the two different clinic types are from different reports, no way to combine from the source)
      2. Master schedule WSheet; pull only required scheduling information from all clinics and add additional columns to enter staff names, confirmations, etc Create two copies from the Master shared workbook and rename them, keep them in a folder on a shared work drive. Use the Compare and Merge feature
      1. Active Clinic schedule.xls ~ Hide all WSheets except the 'Schedule' WS, include macro to save and close WBook at end of day to prevent staying open on personal computer and preventing merge of data.
      2. Clinic Schedule Update.xls ~ When the clinic reports are created from the corporate portal, I can copy the entire WSheet for the month for each type of clinic, and paste it in the Update WBook. When I use the compare and merge, it should update the Master and highlight/comment on what has changed
  6. Mike86

    Mike86 Member

    Messages:
    162
    @e_var Well, here's my copy of your file using the function. I'm not showing any errors on the Schedule tab after picking up data from all tabs. I added headers and a fake ID number to the Sept-Nov tabs to make sure I was hitting the marks, although I don't think that would make a difference.

    To your previous message, it's difficult for me to suggest much without more understanding (probably more than either of us want) of what you're doing and why. It sounds like you may be in a place where a nice VBA routine could help you considerably with saving the different versions and automatically copying/pasting sections. Using the data will still likely be easier if it's in one place as soon as possible.

    Attached Files:

  7. e_var

    e_var New Member

    Messages:
    20
    Sorry, I am not getting results from all the WS, when I was looking at the code it was only in the first cell, I'm examining the code where it stops adding values from other WS, so I can see where it deviates.
  8. e_var

    e_var New Member

    Messages:
    20
    Nevermind there must be a left over corruption of value/formula in my original duMmy WB.... Used your code there and was receiving missing clinic... downloaded the one you graciously modified and checked... EVERYTHING WORKS! :)

Share This Page