In the attached workbook, I need a formula that only takes into consideration the four most current weeks. On !Category Tables! AA47, I would need it to equal the sum of !Sales Entry! G4:J4. Obviously that formula is simple, but I would need the selection to move over a row each time a new week is filled in. Is this possible?
I can't see the "Week" in your worksheet. Can you explain where is it?
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
oh sorry, the bold numbers 1-52 along the top row of sheet !sales entry!
Are Category No. AND Description always linked (ie= Category 50 = Tires) ?
In other words, is this a 2-part look-up, or just either/or (ie, if I look for category '50', do I also need to look for 'Tires', or is category '50' = 'Tires', or in this case, if I check column W in 'Category Tables' against coumn A in 'Sales Entry', do I need to check if the matching column b in 'Sales Entry' is in column Y of 'Category Tables' or not) ?
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
The cat. numbers and descriptions are always linked, but for the purposes of this, treat it as either or if it makes it easier.
Try this in AA3:
Formula:
Please Login or Register to view this content.
Drag/Extend down
(see attached, note I Inserted 3 columns AB, AC, & AD) for testing solutions, so my formula is actually in AB column, but if you paste it into AA, it will work)
Hope this helps
ok that seems to be working and i can tweak it as necessary for the needed end result. Would you mind explaining the formula to me? I've never used the offset or match f(x)s. I need to get one that does just the most recent week and I'd like to be able to put it together myself so I don't have to waste other people's time asking how.
OK last thing I need is to fill in the formula for just the most recent week. I can't seem to adjust the formula correctly to make it work
Last edited by dropanddrive03; 04-28-2013 at 11:39 AM.
Try this to get the last filled in week,
Formula:Please Login or Register to view this content.
the first match returns which Row contains the Category #, (with the $A$3:$A$168 being the array to look in, so 50 in this case returns a 1), The second match tries to find the largest number available to excel, in the row returned by the first formula(note, the match within the match is exactly the same as the first match), if it doesn't find it, it returns (in this case) the # of the last cell that contains a number, giving an
offset within the 52 cells that it looks at, we add 1 to this number to get the right column number for the overall offsetusing the sample I uploaded)
=Offset($A$2,{row offset,so for 50}1,{column offset of last column, again, for 50}8 + 1)
=Offset($A$2,1,9) which would be the same as $J$3
Hope this helps
Wow this is brutal. I thought all was fine and dandy, everything worked right. Until...I entered the real data. because it starts with week 13 and not week 1, I have #div/0 errors in all the 4 weeks charts on sheet category tables. How can I move the starting point over to week 13. Or do I need to move the weeks around? I'd rather not do that because I'll have to retype all the info, but if i must, I will. I'll reattach because I made some format adjustments.
Last edited by dropanddrive03; 04-30-2013 at 12:48 AM.
I gave you a solution that worked with supplied data, there was no mention of a differing start week than than week 1 ???
the way around this is to use ANOTHER match to get the first week, OR change the column offset, assuming that it is going to be the same EVERY time? ie(it should start at week 13 EVERY time?)
AND; if so,HOW do you get the first 12 weeks results?
EDIT-
Also,as no division is taking place ANYWHERE in the formulas I supplied, the error I would expect is #NA, or #REF, definitely not #DIV/0
Last edited by dredwolf; 04-30-2013 at 01:22 AM.
Haha sorry. I suppose I assumed that it would automatically skip over to 13 if i didn't put information into the first 12. My store is now in week 14 so I have starting info that I had input and week 13 info. If I just change row offset to 13 instead of 1, will that work? As for the div/0, I misspoke/typed. That div/0 is in a column for a percentage that I needed to generate. If i can get the total columns (which currently show 0s) to have a number in them, all will be well.
Those #DIV/0 errors are because you are completely mis-using the formula...I know it is long, but that sheet never seems to stop trying to solve itself...which as far as i can see, it won't ( I shut it down once I noticed the screen flicker...SHOW me a sample with what YOU expect to see for a few cells, then maybe I fix this), But it looks like you drag/copied the formula to the left, expecting to calc the %, which would not work, as the cell references would change, the proper way to copy the formula is to get into the formula edit mode, highlight the whole formula, Cntrl+C, Esc, choose the next column/cell you want to use it in,get to the formula bar edit mode for that cell, Cntrl+V, then use the formulas on the returned value, drag/extend the value down the entire column...(phew...wore me out explaining it...I can see it being annoying experiencing it
And no, unfortunately, just changing the column offset probably won't help you (well, it will till you go back to week 1, then it all goes to heck again
Edit-
Give me a day or so, now that I think I know what you wan't, I am pretty sure I can modify the formula, Just my RL job is hectic at the moment as well
Last edited by dredwolf; 04-30-2013 at 01:50 AM.
So the basic formula for that comp column is (this year - last year)/last year. I was successful when the information was in week 1 column, but once it moved over, it all went to hell. I did exactly what you said to get the formula in column because I tried to drag over and that was completely useless. If changing the offset will work for now, then that's fine and once i get back to week one in february of next year, I'll just change it back and should be good going forward from there.
Read Edit in my last post...and I don't think you want to try that, let me see if I can come up with something a little less time consuming
I figured everything else out. Thank you very much for all your help!
Last edited by dropanddrive03; 05-06-2013 at 11:55 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks