Complex spreadsheet with unique user view??

[kvar]

The spreadsheet I've attached contains a ton of calculated cells and linked data. The people who created and utilize it aren't necessarily interested in changing the way they use it or populate it each quarter. That being said, my task is to somehow create a way that it can be sent to/seen by each person differently. First I'll point out that the 3 columns that are blank normally contain names, I deleted them for obvious reasons. The first of those columns, B, contains unique names for each agent in the firm, these people need to ONLY see their own data. The second column of names are basically managers, they need to see the records for each agent that falls under them. The 3rd column of names is irrelevant for these purposes.
Now if all this data were in access, and everyone had access to it I could easily just make records visible or invisible based on user name and I wouldn't be here right now. But they can't and that's not an option. I honestly don't know if this should be done solely in Excel, in Access or a bit of both. Currently this spreadsheet gets emailed to each manager and they have to review the data with each agent.
The first tab, worksheet, is basically instructions. It would be nice if those were images on the page and then all of the data were below that so that the agents could see them together and understand it better. As I'm sure you'll see it's a pretty complicated system so they have a hard time understanding it.
Note: There are actually about 3,000 records, I've deleted most of them for size purposes.

Right now I'm just staring at it and am having a hard time figuring out where to even start or what direction to go so ANY and all ideas are greatly appreciated!!!
Thank you so much in advance!

 

[essaytee]

I can't answer your question directly but I am curious how in the past the agents received their copies.

I'm sure, with the use of a bit of VBA code, rows of data could be deleted, saved as a new spreadsheet and sent to the relevant agent.

How is the spreadsheet compiled in the first place? Maybe you could back-track a little and deal with the data prior to someone compiling an all-encompassing spreadsheet.

Is it not possible to import the data from the spreadsheet into Access and from there weave your magic and if the agents must have a spreadsheet export back to a spreadsheet for each agent.

Sorry, I'm not an Excel expert, or expert anything for that matter, all I can say is, "Damn Spreadsheets".

 

[Brianwarnock]

The fact that this is in the General rather than the Excel forum suggests that an ACCESS solution is acceptable.

However the solution in Excel is not that difficult, the fact that there are calculated cells and linked data may not be an issue if the result required is read only, copy and paste values and source formatting to a new sheet should resolve that.
let's think how you would do it manually, then code can be written.

sort col B then col A.
starting at the end split off into new sheets each manager, using cut and paste.
then repeat for each agent from the managers' sheets but using copy and paste.

haven't tried this as cols A and B haven't even got fictitious names.

Brian

 

[Brianwarnock]

Sorry, I'm not an Excel expert, or expert anything for that matter, all I can say is, "Damn Spreadsheets".

it's horses for courses.

Brian

 

[Gasman]

I would be looking to filter the data using data entered on a sheet in Excel (for ease of changing criteria), then selecting the data and pasting to a new Excel file, saving with some unique naming convention. All done in Excel.

If it was already in Access, I'd replicate the process and export via saved exports is 2007 or above.

Here is something I created for a colleague in work to identify certain rows and copy to a new sheet in the same file.

In my case I was looking for cells in a column with a valid date to identify what I was looking for.

This might get you started at least?

Sub Process_CINS()

Dim lnLastRow As Long, lnCopied As Long

Dim strIn as String, strOut As String

 

strIn = "Input"

strOut = "Output"

 

    Application.ScreenUpdating = False

    

    Worksheets(2).Activate

    ActiveSheet.UsedRange.Delete

    ActiveSheet.Name = strOut

    Range("A1").Select

    

    Worksheets(1).Activate

    ActiveSheet.Name = strIn

 

    lnLastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

    

    Range("A1").Select

 

    While ActiveCell.Row <= lnLastRow

 

        If IsDate(ActiveCell.Offset(0, 1)) Then

            ActiveCell.EntireRow.Copy

            Sheets(strOut).Activate

            ActiveCell.Offset(1, 0).Activate

            Sheets("Output").Paste

            lnCopied = lnCopied + 1

            Sheets("Input").Activate

            ActiveCell.Value = "Copied"

 

        End If

        ActiveCell.Offset(1, 0).Activate

 

    Wend

    

    Application.ScreenUpdating = True

 

    MsgBox "Sheet Processed, " & lnCopied & " rows of " & lnLastRow & " rows copied"

End Sub