Hi everyone,
This has been bugging me for a while now and i cant think of a solution. i have a excel sheet with a lot of information on that shows checks completed and any issues weve had. i would like a tab that lists the issues instead of having to search through all the data.
I have attached a copy of the document and i have created a tab with headings for the information id like it to show and an example of the data i want to see.
Thank you in advance for your help
Martin
Your question is ambiguous. Do you want ALL issues reported... or just those for a selected week? All may be rather messy, a selected week, quite simple.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
every week, so every time there is an issue i would like it to go straight into that tab
I wonder if you'd be able to present this list in a table and structure it so the weeks go down the page. I've attached an example of what I'm thinking. To get what you want, you just need to filter the Issue column so it doesn't show blanks. This allows you to sort and filter to get any information you may want.
I've assumed that some columns are the same depending on the store name (that may be incorrect, but may give you options).
I moved the Store column to the right so as you enter new records, you just need to type in the Store name, and everything else to the left fills in automatically. Some of that may need to be changed depending on how you get those values.
Would this work for you?
Last edited by Gregb11; 07-14-2019 at 04:51 PM. Reason: attachment didn't upload
Greg didn't copy across enough columns... and you'll need an extra column for week number, but his suggestion is the only practical way to go. Sorting out 52 different data blocks would be pretty much impossible for a formula to cope with. One big long table will work just fine, with filters.
Column G is the week #.
is there no other way?
i had a thought about filtering the issues fo that week so it doesnt show the ones that have no issues, selecting all the filtered information that week only and creating a button that runs a macro copy and pasting only the information higlighted to the end of another tab.
I feel this would work without having to change the document, the trouble is i dont know how i would add in the week number, again another thought would be creating a form asking the user what week they are copying over may work?
My knwledge of the coding for this is bare minimum i can create the form for the week number but unable to add code to it and I can create a macro to copy and paste however i wouldnt know the code to say copy and paste only the highlighted cells. and add the week number i selected at the start of every row.
There are a million ways to do anything pretty much in ExcelOriginally Posted by mtwa1990
What is the reason you don't want to do what I had suggested? It is a simplified answer and gives the user a lot of flexibility when using tables (with filtering and sorting). I'm not saying what I had suggested is correct because it may not be based on how you are using the spreadsheet, so I'm just trying to get more information so you can get a better answer.
And yes, you can create a macro that will take the records you want and copy them over to the other sheet, and that might be the way to go, but again, you need to balance complexity and simple with usefulness.
The document is used by 30 odd people and weve just got people using it properly. The actual document we use is a lot bigger than the one I have provided you to see and does a whole lot more so changing the layout would affect other documents that also look at this data which would mean they would need changing too.
This sol'n uses formulas, but it is very slow...
A1, Pick week from scrollbar
C1, Start column for the week: =MATCH("Week " & A1,Data!1:1,0)
D1, Last used row for the week: =COUNTIF(OFFSET(Data!A1,2,MATCH("week " &A1,Data!1:1,0)+1,300,1),"<>"&"")
E1, build week's range address: =ADDRESS(3,C1,,,"data")&":"&ADDRESS(D1,C1)
-----------------------------
F3, Array* formula for non blank** 'issue' rows:
=IFERROR(INDEX(OFFSET(INDIRECT($E$1),0,2,$D$1,1),SMALL(IF((INDEX(OFFSET(INDIRECT($E$1),0,2,$D$1,1),0,1)<>"")*(INDEX(OFFSET(INDIRECT($E$1),0,2,$D$1,1),0,1)<>" "),ROW(INDEX(OFFSET(INDIRECT($E$1),0,2,$D$1,1),0,1))-2),ROW($A1)),1),"")
*basis for the remaining columns...
**Many/most of your "blank" cells actually have a single SPACE character. You also have unexpected spaces in many of the 'ext numbers'
Ben Van Johnson
Non-volatile ordinary (not array) formulas achieving the same - pretty quick.
For the store info:
=IFERROR(INDEX(Data!A:A,AGGREGATE(15,6,ROW(Data!$3:$300)/(TRIM(INDEX(Data!$A$3:$HF$3,,MATCH("Week "&$B$1,Data!$1:$1,0)+2):INDEX(Data!$A$10:$HF$300,,MATCH("Week "&$B$1,Data!$1:$1,0)+2))<>""),ROWS($1:1))),"")
copied across and down and for the fault info:
=IFERROR(INDEX(INDEX(Data!$A:$HF,,MATCH("Week "&$B$1,Data!$1:$1,0)+COLUMNS($E:E)-1),AGGREGATE(15,6,ROW(Data!$3:$300)/(TRIM(INDEX(Data!$A$3:$HF$3,,MATCH("Week "&$B$1,Data!$1:$1,0)+2):INDEX(Data!$A$10:$HF$300,,MATCH("Week "&$B$1,Data!$1:$1,0)+2))<>""),ROWS($1:1))),"")
copied across and down
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks