I am managing a waitlist spreadsheet with many tab that requires monthly data to be extracted e.g. number of referrals, wait from assessment to intervention in wks etc. People move from the referral tab, to assessment, to intervention, to discharge, with each one adding more relevant columns of info.
I have made several pivot table to bring this together but have encountered an issue I cannot seem to get around. Some of the 'weeks waiting' data in the discharge tab returns as either a -6000 and something or +6000 and something. this is due to them being discharge on referral and the subsequent dates (assessment date, intervention date) not being filled. I cannot seem to exclude just those cells/values from the pivot table, it removes the whole person which I do not want to do. I figured it would be easier to use an IF function and if the cell is +/-6000 it makes it blank, otherwise it leaves it as is. 2 problems here, if the cell is false it changes it to false rather than not changing the cell content. Issue 2, I cant add an extra column to this spreadsheet as it is one that collectively used. I therefore have to add a formula that applies to the cell its in, after the already existing formula of '=ROUNDDOWN((W13-I13)/7,0'.
Basically, I am stumped and think this spreadsheet might be my nemesis (I didn't make it, I'm picking it up from a colleague who left). Any suggestions welcome, even if its an alternative to using pivot tables! I just don't want to have to manually work out this data every 4-5 weeks!
