| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data collation killing me!!
Ok...no answer to my last post so I will try again..... I have a data page (10 of them in fact identical except the tab names), each of these has Column A as a description, Column B as subtotals of each division and columns C through to Z as the date. I then have the unenviable task of trying to collate the data from dropdown boxes that the user selects the item number from, tabs to column C and types in the number purchased. Column B subtotals along the way. How oh HOW can I do this three dimensional task as all 10 sheets need to be collated into ONE report??????? Please anybody got any ideas?? I've tryed VLOOKUP, HLOOKUP, MATCH but they will only look at one single line...and the item may have been used between two dates......and they both need to be individual as at the month end, the report uses dates to and from to calculate the usage..... Gosh I'm even confusing myself now!!! Thanks Sandi -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Can you post an example ?? -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
umm...not sure how else to do it but ok... Column A5:A20 Item Code Column B21 Subtotal of items (changes as the dates changed and item used) Column C1 through to Column Z1 is where the user enters the date the item was used C100 Through to C150 is the NUMBER the user enters the times the item was used..... This is repeated on 10 different sheets for different Months I then need to report on the Item number and the number of times it was used between two dates. (a start and finish date.) Does this make more sense? Sandi -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
...As I still have a problem visualising the data, can you post a sample w/book?
What's in columns C2 to C99 for example (may not be relevant but the layout appears "strange" to me)? "rhani111" wrote: umm...not sure how else to do it but ok... Column A5:A20 Item Code Column B21 Subtotal of items (changes as the dates changed and item used) Column C1 through to Column Z1 is where the user enters the date the item was used C100 Through to C150 is the NUMBER the user enters the times the item was used..... This is repeated on 10 different sheets for different Months I then need to report on the Item number and the number of times it was used between two dates. (a start and finish date.) Does this make more sense? Sandi -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Ok...here's kinda what it looks like +-------------------------------------------------------------------+ |Filename: Testin.doc | |Download: http://www.excelforum.com/attachment.php?postid=5089 | +-------------------------------------------------------------------+ -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Sorry to be pedantic but I was hoping that you could give me (us!) a sample
WORKBOOK with good data coverage and (even better) include a sample of the expected output. "rhani111" wrote: Ok...here's kinda what it looks like +-------------------------------------------------------------------+ |Filename: Testin.doc | |Download: http://www.excelforum.com/attachment.php?postid=5089 | +-------------------------------------------------------------------+ -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
I did send a working example...just click on the table in the centre of the document and choose workbook open...it should then open the table in excel and work... -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Attached is my attempt at answering your requirements (which I was still not quite clear about). HTH +-------------------------------------------------------------------+ |Filename: testing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5091 | +-------------------------------------------------------------------+ -- Toppers ------------------------------------------------------------------------ Toppers's Profile: http://www.excelforum.com/member.php...o&userid=30076 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
..... sorry .. found an error: formula in Sheet1/3 should be as below:
=SUM(OFFSET($C$2,MATCH($A3,$A$3:$A$4,0),MATCH($A$9 ,$C$2:$Z$2,0)-1,1,MATCH($A$10,$C$2:$Z$2,0)-MATCH($A$9,$C$2:$Z$2,0)+1)) "Toppers" wrote: Attached is my attempt at answering your requirements (which I was still not quite clear about). HTH +-------------------------------------------------------------------+ |Filename: testing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5091 | +-------------------------------------------------------------------+ -- Toppers ------------------------------------------------------------------------ Toppers's Profile: http://www.excelforum.com/member.php...o&userid=30076 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
That is awesome and you are really close....here is the dilemma, the subtotals are NOT used on the individual sheets for the between dates, these are used for the whole month's total only. I have attached your sheet with what I need. I hope this is ok..... I need about a three dimensional data collation, from what I have sent you.. Thanks so much for your help so far....!!! -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
....no attachment! Please re-post.
"rhani111" wrote: That is awesome and you are really close....here is the dilemma, the subtotals are NOT used on the individual sheets for the between dates, these are used for the whole month's total only. I have attached your sheet with what I need. I hope this is ok..... I need about a three dimensional data collation, from what I have sent you.. Thanks so much for your help so far....!!! -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
oooPS...forgot to attach it.....  and can't seem to get it smallenough in zip to be uploaded.......what the???? -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
SHeeeesh here it is.... +-------------------------------------------------------------------+ |Filename: Testing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5092 | +-------------------------------------------------------------------+ -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
My brain hurts (still) trying to understand what is wanted!!!... so I'm going
to give it a rest. If you want to send a (partially) completed example of the summary table using the data in Testing then maybe I'll understand better: in particular expain how the dates are used in the Summary as there are no obvious dates being compared. You say "summing only items that were used on the same day"; if they weren't used, wouldn't value be zero anyway so does this mean ignore ITEM CODES with zero usage? ... even more criteria!) Foe example, what would you expect to see in summary table for "VK 8200 RAM" as the dates on Sheet2 and Sheet3 are different? You also mention another report ......... which sheet is it on? [If this was my own project, I'd (almost certainly) use VBA to solve it]. Sorry to be so dim. "rhani111" wrote: SHeeeesh here it is.... +-------------------------------------------------------------------+ |Filename: Testing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5092 | +-------------------------------------------------------------------+ -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
LOL...You aren't being dim, just I don't know how to explain it better. The report sheet I deleted ,but added it to the bottom of your summary sheet. The dates are actually on the top of the report (start and end dates that is)...so the dates on the worksheets themselves need to be recorded then collated somewhere or somehow so that I have formulas that can look up the Item code, the date and the number used to place into the report. Does that make better sense to you? You were on the right track with what you sent me, BUT the subtotals need to appear on the report NOT change on the Subtotal columns of each sheet. Usually I would have no problem with this but due to them using dropdown lists for their item codes (and these are repeated elsewhere...sighhhh)...this is what is causing the problems. I normally would have just had a column for the item code to be entered, the date and the number (all three placed into another data page vertically and used a sumif statement!!) but this won't work if there is 24 columns across with different dates added over the 10 sheets (representing different months)....then just used advanced filter...but I can only get it to report on either the date and the total ON that date, or the item code and the number used...but NOT all three: i.e. DATE (total on each day), item code (by day also) and the total used for the date. God now I've gone and done it again!!!!! I've confused myself. thinking that sending you the whole document would have been easier, but considering it's over 100KB it won't send this way....and it's industry specific and i could be in for it.....  -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
If it is all possible to send me a sub-set of your "production" worksheet
(presumably not all months are reqired to show what is required) then it MIGHT help! I am UK-based so if you are US-based it's unlikely I am going to divulge any company secrets (although I understand the concern). It's just I find it easier to work with the "actual" data so I can see how things are calculated. "rhani111" wrote: LOL...You aren't being dim, just I don't know how to explain it better. The report sheet I deleted ,but added it to the bottom of your summary sheet. The dates are actually on the top of the report (start and end dates that is)...so the dates on the worksheets themselves need to be recorded then collated somewhere or somehow so that I have formulas that can look up the Item code, the date and the number used to place into the report. Does that make better sense to you? You were on the right track with what you sent me, BUT the subtotals need to appear on the report NOT change on the Subtotal columns of each sheet. Usually I would have no problem with this but due to them using dropdown lists for their item codes (and these are repeated elsewhere...sighhhh)...this is what is causing the problems. I normally would have just had a column for the item code to be entered, the date and the number (all three placed into another data page vertically and used a sumif statement!!) but this won't work if there is 24 columns across with different dates added over the 10 sheets (representing different months)....then just used advanced filter...but I can only get it to report on either the date and the total ON that date, or the item code and the number used...but NOT all three: i.e. DATE (total on each day), item code (by day also) and the total used for the date. God now I've gone and done it again!!!!! I've confused myself. thinking that sending you the whole document would have been easier, but considering it's over 100KB it won't send this way....and it's industry specific and i could be in for it..... -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Hi, I am sending you copied and pasted data from the real workbook, with a description of what i need the formula to do. It's just too hard to explain, the dates on Sheets 1,2 & 3 are never consecutive as they work a 4 day on/off roster. The item number from the drop down boxes need to be summarised on the summary sheet with the total used etc.....the start and end dates are used to count the number of items used each day (total to be recorded on the summary sheet as ONE single figure between these dates)....this make sense??? I have used countif and that will count the occurences of the item number, but not ADD them. Adding them is imperative as each sheet is actually for different sites and they may use the same item number at more than one site on the same day.....making better sense??? Thanks Sandi +-------------------------------------------------------------------+ |Filename: Testing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5113 | +-------------------------------------------------------------------+ -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Hi, Attached is my solution. In each worksheet in column AA I have totalled the usage for each part for the given Start and End dates. The Start and End dates are now named ranges. In the summary sheet, I total the columns AA from each sheet for each part. The part number data validation is now a named range called Items. Hopefully you can now customise this to your needs. +-------------------------------------------------------------------+ |Filename: Copy of Testing.zip | |Download: http://www.excelforum.com/attachment.php?postid=5127 | +-------------------------------------------------------------------+ -- Toppers ------------------------------------------------------------------------ Toppers's Profile: http://www.excelforum.com/member.php...o&userid=30076 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Hi matey, I have been trying your formula as it would relate to my workbook, but the formula for the subtotals between start and end dates returns a zero unless the exact dates are used. Is there a way to total them if the are Greater than or equal, less than or equal to the start and end dates??? Thanx Rhani -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data collation killing me!!
Hi matey, I have been trying your formula as it would relate to my workbook, but the formula for the subtotals between start and end dates returns a zero unless the exact dates are used. Is there a way to total them if the are Greater than or equal, less than or equal to the start and end dates??? Thanx Rhani -- rhani111 ------------------------------------------------------------------------ rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940 View this thread: http://www.excelforum.com/showthread...hreadid=564594 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
| Importing Data | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
| Line Graph Data Recognition | Charts and Charting in Excel | |||
Linear Mode
