• 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...

  • 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.

e_var

New Member
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.
 

Attachments

  • dmy Clinic Schedule Master.xlsx
    81.8 KB · Views: 5
Last edited:
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.
 
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
 
@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.
 
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
 
@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.
 

Attachments

  • dmy Clinic Schedule Master.xlsx
    90.8 KB · Views: 6
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.
 
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! :)
 
Back
Top