Posts by etaf

    Re: Sumif wildcard help! (DAte/time)

    not sure about the wild card
    but you could try
    =SUMIFS(Data!Q:Q, Data!P:P ,">=5/4/13 00:00", Data!P:P ,"<=5/4/13 23:59:59")



    Re: Basic Yes/No Questionnaire Analysis.

    excellent - glad its worked out, do some dummy runs before next week, or pull in some old sheets and test the results

    any issues come back here

    as its a dashboard maybe you might want to add some conditional formatting for a RAG status (red amber green) on the sheets or on the summary

    Re: Basic Yes/No Questionnaire Analysis.

    THE COUNTIF function does not work across mulitple worksheets - without VBA or you can list all the sheets and use indirect - BUT the formula would need to be changedeach time a new sheet is added - BUT Sum does work across multiple sheets and if we put the sheets between two known sheet names then as sheets are added inbetween it will update automatically

    So in order to do this ,i have named two sheets start and end - now any sheets added inbetween those two sheets will be used in a sum


    will add up all the totals

    inorder to get the YES, NO and N/A - I have just added some helper columns in AA9 , AB9, AC9 and down the rows
    and we use a simple IF statement to change the YES, NO,N/A into a 1
    then we can sum on the summary sheet

    so as new audits are carried out - add a new worksheet, - doesn't matter what you call it - BUT IT MUST be between the Start and End sheet

    i would suggest by copying a master sheet - keep the master sheet outside of the Start/End sheets - can be hidden

    you could modify to add a dropdown so only blank and the required response Yes, No, N/A are available

    Otherwise a bit of VBA to add the sheets to then use indirect fuunction - which is probably outside my skill set at the moment

    see attached

    would that work for you ?

    see attached

    There is a morefunc add in , which will add countIF.3d , but i could not find the download , and its quite old . so not sure about later versions of excel and windows

    Re: Basic Yes/No Questionnaire Analysis.

    not sure how you would do this -


    Additional audits are likely to be separate work books

    without knowing the names of the books - also they may need to be open to extract data

    so where would the files be kept directory and what would you name them ?

    Re: Basic Yes/No Questionnaire Analysis.

    how will these additional audits be recorded

    you can count yes and nos - in fact they could be in the same column
    so you could have a column with a drop down for


    "Yes" / "No" / "N/A" results for each question in one place

    will the additional audits be on the same row

    to count

    so for range you would put the row reference


    The point where i'm struggling is that there are to be maybe two or three audits done per day and i don't want to have to alter each formula to include the latest audit.

    So we need to know how that will be laid out for additional audits - before I make any examples

    Re: How to input a value and let a formula calculate the sum of previous entries...


    1. What I want is a formula that sums up all entries of the "total D" column from 1 - 7 automatically? Ofc, doing it maunually I'd use =SUM() for the specific range and come to the result of 707, however i need it to be flexible. For example, in order to see what the "total D" is if I key in 29, etc.. I can't always do sums for the specific range that I need. On the other hand the range will always start from the first cell below the "total D" and should therefore be fixed.

    you could use
    where J2 is the level number - then it will add up everything below or equal to J2 level

    not sure about point 2

    Re: Look up a value in a table and return the value from the same column different ro

    Ok - guest left earlier then expected

    so I have used

    as an array formula - use Control+Shift+Enter to get {} around the formula
    this then uses the table of Agents to lookup the Postcode and return the name into the zone table lists
    if the postcode does not exist - then a #value error is created - which I then use in the next formula - so its ok to have the error in the zones list

    Then on the Map allocations sheet
    I have used

    this looks up the postcode in the zones list and if no #value found ie no error
    then it will return the value in zones column for the name assigned
    if there is an error - then is uses the 2nd vlookup and returns the zone ID

    I have added the formula on the allocations sheet map, but only in the mauve coloured section
    I have also changed / added a couple of those postcodes onto the names list agents sheet
    to make sure it populated into the zone sheet and onto the allocations sheet OK
    see the darker mauve fill

    see attached file- due to size limits , i have zipped the file up

    does that work for you OK

    Re: Look up a value in a table and return the value from the same column different ro

    you could use just the zones and put the names against each postcode - then use vlookup to find the name and if no name returned return the zone

    if you put a zero in to places with no names

    I'll work on populating the zone table from your list of names sheets automatically - that should be able to work - using an offset formula to get the name listed and then a vlookup with an IF to identify when "no match" returned for a name - the return the zone instead - but maybe tomorrow as commitments to meet right now

    Re: Look up a value in a table and return the value from the same column different ro

    lookup needs to have the data sorted ascending

    are you just looking up the postcode to get the zone ?
    that appears to be the result of in allocation

    you could just use vlookup (postcode, zonelist, 2, false)

    but i suspect you are doing more than that

    you could then also add the names to the postcode on the zonesheet and return names in the allocation