Reflect contents of current row

[MaxRahder]

This seems like it should be simple, but I don't know where to start.

I have rows and rows of data. I want to create a "row summary" sheet that shows a few columns from the current row. The user would be able to cursor down the rows, and see the summary page change to reflect the current row.

Is there an easy way to do this? Can someone point me in the right direction?

Thanks!

-Max

 

[MaxRahder]

Thanks for your help.

I'd like to stick with my name example. If I could get an answer to that question I think I could apply it to my situation. Please re-read that example. Actually, I'd like to make it simpler. Pretend there is a single user. That user simply wants to have a separate part of the spreadsheet show data from the active cell. I.e., as the user presses down and up arrow, this other region of the spreadsheet would automatically show just the last name, or perhaps, the concatination of all three names. How can this be done? Am I limited to showing the summary data on the same sheet, or can I display it in a separate sheet or separate book?

Thanks again for everyone's help.

 

[Zack Barresse]

You can show it in another sheet, or even another workbook. It would seem there are many ways to skin this cat. Maybe you'd be interested in hiding rows/columns? A different sheet for each different user? It's hard to get any more in-depth; speaking in generalities is about like stepping out on a very long limb. Quite uncertain.

 

[MaxRahder]

Ok, after doing a little digging here's the approach I'll take. I discovered a function "CELL" which will tells me the value of the active cell. In my test sample I put the data on sheet1, and the "view" on sheet2. Sheet2 has a cell with "=CELL("row")" to give me the value of the active row. In my test spreadsheet I put this on sheet2 at B1. I then use "=INDIRECT("sheet1!C"&$B$1) to show the value of column three from the active row on sheet1. I'll continue to elaborate that example to pluck and process other information from the current row. One other thing, since I didn't want the user to press F9 all the time, I created this macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets("sheet2").Calculate
End Sub

In this way, as the user presses down and up arrow, sheet2 automatically displays the value. (How can I do a re-calc for all sheets, instead of having to hard-code the name of a specific sheet?)

The bottom line is that it all behaves naturally to the user, and the formulas and references are pretty simple.

 

[Zack Barresse]

Ctrl + Alt + F9 = full recalc

Your CELL function will not work. The same function (generally) would be =ROW(). But this will NOT GIVE YOU THE ACTIVEROW. Unless you put this formula in every cell, every time you leave that sheet, then update your INDIRECT formula every time you activate your Summary sheet. I would not recommend such a course. And there is nothing natural about it, really.

If you really want a solution, a working/workable solution, please post a sample of your spreadsheet. This is going nowhere until you do.

 

[MaxRahder]

I have already implemented everything I described and it works great. As I press down arrow on one sheet, the other sheet automatically shows the concatenated name of the active row. In my case, I have 200 rows of data. First and last names are in two of the columns buried deep within the row. As I fly through the data rows via down-arrow, the sheet on the right automatically shows the re-formatted name for the active row. This changes as fast as I change rows on the left.

This was all accomplished using the function "CELL" a single time and "INDIRECT" as needed to pull data from the row.

Here are the details. I named the sheet with the data "data". The viewing sheet is "sheet2".

sheet2 has this formula in cell B1:
=CELL("row")

sheet2 has this formula to show the concatinated name:
=TRIM(INDIRECT("data!Q"&$B$1) & " " & INDIRECT("data!P"&$B$1))

In this example, I am concatenating first name (column Q) and last name (column P). Except for the macro to automatically re-calc, that's it! To show more data I'll do something similar to the expression that concatenates the names.

Problem solved!