Hello all, I'm a new visitor to these forums, I'm here because I'm stuck trying to solve an excel problem.
created a workbook that functions as a log to record issues that I've had with companies I work with. Generally speaking it
is 3 tabs:The data page, which I call the "Issue log".A lists page, for validation lists "Validation
Lists".A issue summary page, formatted for the external companies to see what I've logged, called "Company
My concept was to enter data into the log, and then I've set up my company summary page so that outside of the print area I
select the company I want the summary to be for, and the date range (starting date & ending date) for the summary. Then
(theoretically) the summary would auto-populate with each of the individual issues that I've entered into the log that match
those criteria (company & date range).
My problems are:I can get it to pull the reference numbers for the
right company from the first occurance of the company's name to the last, but if an issue is in the middle of that data out
of order, it includes that issue as well.When I try to sort the data to fix that problem, it changes the reference number
(because it's counted based on order of entry), and in some cases the other company's already have that reference number.
I've created my first column in the log as a concatenate that is formatted as "2AA1", where '2' is the
total sequencial count for the log, "AA" would be a 2 letter abbreviation for the company in question, and
"1" is the issue count just for that company. The log essentially looks like this:
1AA1 - 6/1/2011
- Issue notes
2BB1 - 6/2/2011 - Issue notes
3AA2 - 6/15/2011 - Issue notes
4CC1 - 6/10/2011 - Issue notes
So basically on the next tab I would select something like:
Builder - *AA*
Start Date - *6/1/2011*
End Date - *6/31/2011*
And I'd like it to pull the specific reference numbers in that range that match my criteria
so I can vlookup the other data I need to populate the rest of the form.
I've tried SUMPRODUCT, VLOOKUP, arrays,
Pivot Tables, INDEX/MATCH, SUMIF, COUNTIF, etc. I have literally spent the last 12 hours consecutively trying to figure this
out on my own, and I just can't make it work. I know one of those methods (or more) can work, I just don't have the skills
to do it.
Does anyone have any advice for me? I really appreciate it.
Thanks so much...