I have a spreadsheet that has multiple sheets and one of the sheets titled "data" is my data source. I have a sheet called "January" that has a list of customers, what I need it to take from the "data" sheet how many times that customer is listed and how many times that customer is listed from January. I have attached a sample
This will work for you, copied down...
=SUMPRODUCT(--(TRIM(Data!$B$2:$B$34486)=January!A2),--(MONTH(Data!$A$2:$A$34486)=1))
You will need to change the 1 at the end for each sheet (Im still working on that part though)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
duh, major brain phart lol
In E1 put this (on each sheet)...
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
then use this, copied down
=SUMPRODUCT(--(TRIM(Data!$B$2:$B$34486)=January!A2),--(TEXT(Data!$A$2:$A$34486,"mmmm")=$E$1))
@DFibbins, thank you for the help...where you have filename what do you mean by that? what file name?
@DFibbins, I put the sum product formula in the sheet and it worked perfectly, im not sure what you want me to do with the MID formula and why?
Hi,
I have observed the data discipline in data sheet and to other sheets, as per my observation they are not matching, i mean we are able to search but the length of the string is varying which is added by spaces etc.please clear that and check with the solution which i have provided in the attachment.
Punnam
@ Utahguy, that formula pulls inteh sheet name - which is then used as the month criteria. I put that in E1 to pull in the sheet name so that you dont have to hard-code the month name in the formula
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
@ Punnam, sometimes thats just how the data comes inThe TRIM() function generally takes care of leading and trailing spaces
Hi utahguy9384 ,
The file size is more than 1mb i am not having send it in Forum, if any mail id i will be able to send you a copy.
Punnam
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks