Posts by kimberly

Re: multiple criteria match showing mixed results

Thanks Dave, for trying. The data changed because I can't use real names, etc from my company online and I am at a different computer than previous posts so I had to create new examples. In my first post I was trying to show you where the data needed to end up - on mailing labels. I shouldn't have it confused the issue, I only need to get Excel to format it for use in a mail merge with Word and then I can get the mailing labels from there. Pivot Tables would be great if I was just collecting info, but I can't see how it could work for printing over a thousand labels at one time - in your example I could see the data I needed, but it only displayed one at a time and only showed one seat.
Thanks again for trying. If I figure this out..and I have to lol...I will post an example.
Thanks!

Re: multiple criteria match showing mixed results

I need to take the data on the first sheet (changes daily, it comes from the reservation system just as it is except for the formula in the last column; that I am going to move to a different worksheet that is hidden so it isn't overwritten daily). The average day will be between 1000 and 1500 rows. (Matinee days will be double that.)
If the data is formatted like the second worksheet, I can do a mail merge to print the table labels.
What is throwing me is the seating. There aren't really any formula's on this example, I threw it together on the fly as I have two other spreadsheets due tomorrow that I am just starting, so sorry but I think you can better see where I am going with this.
Sorry also, it took so long to answer your post, I was too busy at work to check back, and have divided my time since between trying every function that MAY work and tossing pencils at my monitor.
Any help you can give will be greatly appreciated.
Thanks :?

Re: multiple criteria match showing mixed results

Looking at the table...did I mention that I have to print up to a thousand labels a performance, 8 performances a week? the pivot table gives me the information I want, but I don't know how to use it in the format I need without selecting each page individually. Am I missing something?

Re: multiple criteria match showing mixed results

I love pivot tables! It is my boss that is more difficult. Can I use a pivot table to print labels from though? I have tried about every combination of match and vlookup and AND and IF and DGET.... all I have so far is a headache. Going to take some aspirin and check out your example.
Thanks Dave, you're the best.

I have a spreadsheet that I am trying to create using the Database functions, that I am apparantly not understanding. There is a lot of raw data that I need to combine into "labels" (like mailing labels, not used for that but same kind so if I can just get the data into a mail merge format I can make them in Word). The data is already sorted. I am trying to stay away from array formulas, but I am not married to the idea.
In column C-Performance
In Column D-Row
in Column E - Seat
In Column F -Section
In Column G - Guest name
The labels must show the Guest name on the top
then the SECTION, Row and Seat(s)
then the performance.
My biggest hang up is the guest name can be repeated (if the group is large they can/do sit at different tables, rows, sections. And there can be other guests in the middle -- guest one at A 11 seat A and guest two at A 11 Seat B-C and guest one at A 11 D-E-F)
How can I make Excel pull data based on 3 criteria and then show all the seats used in each label?
realizing this makes little (no) sense I have attached an example. Sorry to be so eclectic. (also sorry I didn't take this week for my vacation)

Re: sumif based on dates ignoring totals

The blanks are there because my boss will print this daily and his goal is to match the look of the reports we got from our old reservation system so the conversion to the new system will be better received. This report now takes about 20 seconds to recalculate and twice Excel has said "not responding" but if you wait for about another 20 -30 seconds it starts up again. (I am trying to master the DSUM really fast now, so I may be back :? )
Thanks!

Re: Concatenate

if what you meant is
Cell A = 2004
cell B = Nov
Cell C = 19
and you want one cell containing "19 Nov 2004"
Then you can do this: =concatenate(cellC," ",cell B," ",cellA)
the " " leave a blank space between the cells that you are putting together.

Cell A = GL
Cell B =1001
Cell C = Sales
then: =concatenate(CellA," ",CellB," -",CellC) will result in "GL 1001 - Sales "

Is that what you meant?

Re: sumif based on dates ignoring totals

AWESOME! thanks so so much!
(On the humorous side, when I got on here tonight and started the next course of your "download training" I found I am on level3--- :drum: which of course covers the Dfunctions..yea, that's me alright, always a day late and a dollar short!)
I am rebuilding the workbook, if they won't let me use the pivot table I want it to be as stable as possible.
Thanks again!

Re: sumif based on dates ignoring totals

OK, I did create the Dynamic named ranges in the raw data (easier than I thought it would be, thanks for the link).
I tried to adapt your DSUM formula to my workbook and despite the fact that I have a way better understanding of it than before it isn't coming up with the correct totals. It is actually giving me totals that are less than one week for the entire month. Not sure what I did wrong there.
I am trying to apply the DSUM in place of SUMPRODUCT on my sheet and I can't figure out how to use the Dynamic Named Ranges in it. I have 3 dynamic named ranges, 1 each in Columns A, B and C. (Sorry, reading the help file is on my agenda but unfortunately it is after working 2 jobs and home schooling 4 kids, so that puts me at about 2 AM here when my grasp of this is marginal at best) How do I make it recognize all 3 ranges?
Thanks again, you're the best!

Re: sumif based on dates ignoring totals

ok, here it is. I didn't include any of the rough data on sheet1. It is huge.
(I also changed the number values used in this example)
the formulas on sheet two columns E through L are sumproduct formulas like this one =SUMPRODUCT((perf=$A7)*(type="C")*(fill="Dinner and Show"))+SUMPRODUCT((perf=$A7)*(type="D")*(fill="Dinner and Show"))+SUMPRODUCT((perf=$A7)*(type="K")*(fill="Dinner and Show"))+SUMPRODUCT((perf=$A7)*(type="N")*(fill="Dinner and Show"))
but I have shown a numeric value in place of the formulas.
Thanks so much for helping.

Hi. Sorry to bother you guys again!
I have this workbook in which Worksheet1 has around 25,000 rows of data that I have put in named ranges.
Worksheet2 pulls that information and consolidates it into the name, date, count and other data (in columns). The rows are performances, with a total of each column after the eighth performance. The totals are directly beneath the data for the week...like, Column E is full paying reservations with a numeric value in rows 1 through 8 and a total of them in row 9.
then, starting in row 11 the process is repeated for the next eight performances, with a total in row 19. This sheet has to remain formatted in that way.
Worksheet3 pulls data into simply weekly totals. (easy)
but I also would like to break the totals into a monthly value. I have tried many many approaches (many from this forum and may I interject that I love the new forum but I have run across some old posts where the workbook examples are no longer attached, and I understand that is just collateral damage, I only mention it now in my defense of not being able to figure this out)
I just can't get around the "total rows" in anything I have tried; so when a month crosses from one week of performances to the next, it adds in the "totals" giving an inflated value for the month. OR I had this fun thing where it began adding again, but wiped out the previous month.
Any ideas? I am VBA challanged and others viewing this info prefer to not use pivot tables.
OF COURSE the data changes daily and the number of weeks/months will change every 6 to 10 weeks depending on the run of the show.
(We use Excel 2003 on Win XP)
Thanks to any who can help, and thanks for this forum, though this may not show it, I have learned a great deal!

Sorry, I am still researching this. The division IS part of the vendor number, so if the Vendor Master is only recognizing the division of "00" that explains why it is attaching that division to the account numbers for division "30" and the rest of them. I am still trying to figure this out, sorry.

Sorry it took so long to reply. I found something distrubingly interesting. In the fields of the Vendor Master, "Division" is listed...but when you try use the select expert, only the division "00" is there. Now I am at a total loss!

Altered the join and got this error code:
ODBC error: [Providex][ODBC Driver] Expected lexical element not found: )

followed by the ever popular: Error detected by database DLL.

(I am able to recognize that as English, but as to the meaning of the words.....sigh) Any ideas?

Yes the Vendor Master is the one that holds all the information relating to each vendor, including the last check, invoice, current balance. But it doesn't show history. That is in the APH_JobDistDetail and several other tables.
I am trying to locate some indexing documentation, so far just finding the correct table has been trial and error. I will update this as soon as I find the info. THANK you for helping this forum is the best.

Hi! I am in full blown gray matter gridlock!
I have this report I created in Crystal Reporting (V. 8.5.0299) that I use with Mas90. The problem is (Mas90) (stinketh mightily) (sorry) the fields for the Division, Vendor Number and Vendor Name. The Division and Vendor Number are seperate entities in the area of Mas90 that I am pulling totals from. However, they are combined in other areas of Mas90 that I am referencing to get the Vendor Name...I think.
For Example two vendors: Vendor 30-001172 and Vendor 00-001172. the "30" and the "00" are the Division and sometimes the rest of the number is identical. When I run my report, it shows the correct division "30" and correct Number following, but rather than give me the actual vendor name associated with the information given, it will print the one that starts with the "00". Since all the information that I need for my report is in one field of Mas90 EXCEPT the name, how can I get Crystal to look at the "VendorMaster" and return the correct name for each particular invoice?

Not sure if that makes sense to someone not looking at it, and I apologize. Here is the SQL Query it it will help:
SELECT
APH_JobDistDetail."Division", APH_JobDistDetail."VendorNumber", APH_JobDistDetail."InvoiceNumber", APH_JobDistDetail."JobNumber", APH_JobDistDetail."CostCode", APH_JobDistDetail."CostType", APH_JobDistDetail."DistributionAmount",
AP1_VendorMaster."VendorName"
FROM
"APH_JobDistDetail" APH_JobDistDetail,
"AP1_VendorMaster" AP1_VendorMaster
WHERE
APH_JobDistDetail."VendorNumber" = AP1_VendorMaster."VendorNumber" AND
APH_JobDistDetail."JobNumber" = 'CATS'
ORDER BY
APH_JobDistDetail."CostCode" ASC

any help will be greatly appreciated! Thanks

I was told that Quattro Pro uses the older Lotus syntax (which Excel will also support) ..also read it in an article online. If that is true then go here:

http://www.microsoft.com/offic…LN=EN-US&gssnb=1#ORK018C2
There is a great comparrison of Lotus syntax to Excel, with lots of information to help you match function names, keyboard shortcuts and more.

Hope that helps.

I must be missing something. I can't see any change from what you gave me before. Sorry.
Maybe I have typed it in wrong, I will try to cut n paste it, but so far it does nothing different.
Could you highlight the change? Sorry, I am just trying to understand.
Thanks

Win xp, Excel 03!

Simplicity is a beautiful thing! Thanks so much!

Hi Dave! Thanks for the code!
I followed your directions and it does what I wanted :thanx: . It does protect the worksheet as soon as I use it though. I can unprotect it, and sometimes it works, and sometimes it stays protected and I cannot add or clear any data.
Any ideas on why?
I tried to write the code on my own this morning, and after looking over yours I wasn't that far off...well far enough that it did ...well nothing lol...but I was on the right track!

(on another note, love the Excel Hacks book! I am getting ready to buy my third copy! Seems the guys at work love it to, and it keeps getting borrowed...permanetly! lol)