Creating a unique report/page per user ID

[cooker2k]

I have and excel spreadsheet with two tabs: Tab 1) an array of data which has a unique ID per person and each person can have 1-20 rows of data. Tab 2) a one page report for which an end-user enters the unique ID and then the report will pre-populate with data from the data tab.

I can't figure out how to enter an ID in tab 1 and have it populate the values for the information from the data tab.

 

[alx7000]

Hi cooker, welcome to the board!

To clarify, do you want to create a 'Tab 2' for every unique ID on 'Tab 1' or just populate 'Tab 2' with the most recent ID entered on 'Tab 1'?

Cheers,
alx7000

 

[cooker2k]

Thanks for the reply! Populate Tab 2 with the most recent data in Tab 1. Here's how I would describe it.
Tab 2
ID111, data field 1,
ID111, data field 2,
ID111, data field 3,
ID111, data field 4
ID222, data field 1,
ID222, data field 2,
ID222, data field 3,
ID222, data field 4
ID333, data field 1,
ID333, data field 2,
ID444, data field 1,
ID444, data field 2,
ID444, data field 3,
Tab 1
Report Page
Representative ID; ID111
Your results for product 1: data field 1
Your results for product 2: data field 2
Your results for product 3: data field 3
Your results for product 4: data field 4

 

[alx7000]

Hey,

Given the sample data, the following code should work:

You would need to select the 'Representative ID; ID111' cell on Tab1 then run the macro, it will copy the rows below it into 'Tab2' in the form your sample data shows:

Option Explicit

Sub CopyToSummary()

Dim idNumber As String

'set id of current selection
idNumber = Mid(ActiveCell, 20, 255)

'copy record below selection
Do Until ActiveCell.Offset(1, 0).Value = ""
ActiveCell.Offset(1, 0).Select
Sheets("Tab2").Cells(Sheets("Tab2").Rows.Count, "A").End(xlUp).Offset(1, 0).Value _
= idNumber & ", " & Mid(ActiveCell.Value, 29, 255)
Loop

End Sub

Any questions please let me know.

Cheers,
alx7000

 

[cooker2k]

Thanks - if there's a way I can send you my spreadsheet so that you can see the report, it would be helpful. I tried the macro, but it didn't run end-to-end. I'm new to the site, so thanks in advance for coaching you can help provide.

 

[alx7000]

All good, if you're happy for the workbook to be public you can upload it to http://ge.tt/ and then share the link to it?

Cheers,
alx7000

 

[cooker2k]

Thanks for direction on where to place the file. Here's the link to the file. Thanks a ton for the help! I'm learning a lot! http://ge.tt/7Ekxfxe

 

[alx7000]

No problem, have a look at this:
http://ge.tt/2zKDfyq

Have updated the formulas in the summary sheet and made a macro to print all clients to pdf. Current pdf save location is C:\Test.pdf but you can change this in the macro.

Any questions please let me know.

Cheers,
alx7000.