Reflect contents of current row

MaxRahder

New Member
Joined
Jan 26, 2005
Messages
10
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jdc

Active Member
Joined
Nov 23, 2004
Messages
263
Hi Max

I think you may need to rephrase your question, or give some more details
ie, what do you mean by "cursor down the rows" and "reflect the current row"

Regards

John
 

MaxRahder

New Member
Joined
Jan 26, 2005
Messages
10
By cursor down I mean using the down arrow key to change the active row. As example of the kind off thing I'd like to do, picture a spreadsheet with three rows. Each row has three columns, containing a person's first, middle, and last name.

John Alan Smith
Fred Michael Jones
Mary Jane Johnson

Assume this data is in cells A1, B1, C1, A2, B2, C2, and A3, B3, C3.

Somewhere else, like on another sheet, or another book, I'd like to show a piece of information from the active cell's row, such as the last name. So if cell A1 were active, the summary page would have a cell showing "Smith". If the user were to press the down button, making B1 the active cell, then the summary page would show "Jones", and so forth.

Actually, I'd like the summary sheet to contain formulas and expressions. For example, maybe it would put the names together so that if the active cell were A1, the summary page would show "Smith, John Alan". As the user presses the down button on his keyboard, the summary page would change to "Jones, Fred Michael" then "Johnson, Mary Jane".

Thanks for helping!
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi, welcome to the board!

This is a little difficult, as there is only one ActiveCell per workbook with Excel. When you switch sheets, the previous sheet will not contain ANY activecell. It moves with the activesheet/book. You would need to put a worksheet selectionChange event in each worksheet that you want to transfer data to the Summary sheet.

Does it have to be the last activecell in each sheet? How many sheets are we talking? And are we ACTUALLY talking about only three columns? Don't sugar coat anything for us. :)
 

MaxRahder

New Member
Joined
Jan 26, 2005
Messages
10

ADVERTISEMENT

The summary information could be on the same page, if that makes it easier...

Ok, here's the un-sugar-coated version. I have data in XML. Basically, there's an element that repeats hundreds of times, and I import it into Excel as one row per repeating group. This works fine. Each of these rows contains data relating to a real-estate transaction. In other words, there is a column for buyer's name, seller's name, propery physical address, sales price, and lots more. Each row represents a separate sales transaction.

Now, there are three categories of users. No single user type needs to see all the data, and in any case, no one wants to try to scan all the data in one of these rows. One user needs to see only five or six values from each row. Another needs to see lots of values, but would like to see it formatted suitable for printing. Another user wants to see just the total row count and the sum for one column.

So, we'd like to say: ok users, here's the xml with raw data, and here are some nice pre-formatted sheets (or books, or macros, or whatever we need to create) that lets each of you see the data in the way you'd like.

In the short term, I just need the simplist example as a proof of concept. We'll then enhance it over the next few weeks.

Again, thanks for all your help!
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
It sounds like you need an AutoFilter solution. Can you post a representative sample of your data? We need to number of columns, where they start, which cols have headings, etc. All data structure information. Then, do you want each sheet to show their (users) data? What will the sheets be named? Is it relevant? What is their criteria? (Exactly)
 

MaxRahder

New Member
Joined
Jan 26, 2005
Messages
10

ADVERTISEMENT

For one grasping-at-straws approach I tired to code a user-defined function:
Code:
Function ar()
    ar = Application.ActiveCell.Row
End Function
When I type =ar() in a cell, it shows the row number for that cell's row (i.e., since that was the active cell when I entered the formula, it uses that value). However, when I press arrow keys to move around the spreadsheet, the value does not change. If I press F9, thinking it will re-evaluate the function, nothing changes. They always reflect their original value.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Yes. You don't want to use Activecell. Remember what I said, it will change with every sheet. THERE IS ONLY ONE ACTIVECELL IN AN EXCEL INSTANCE! So you cannot say, activecell of sheet one, AND activecell of sheet two. It's impossible. (There are workarounds though.)

For your function, use ..
Code:
Function ar()
    ar = Application.Caller.Row
End Function
Now it will work for you. Also, if you want it to change with each worksheet change, use the "Application.Volatile" command as line 2 in your function, right above "ar = App..".

I'd still suggest trying for an AutoFilter route. If you desire this, please answer the above questions from my post.
 

MaxRahder

New Member
Joined
Jan 26, 2005
Messages
10
Keep in mind that my users are not trying to limit rows. All users want to see all rows. Instead, they are trying to summarize what they see for a given row. One user wants certain columns, and another want other columns. I don't know much about AutoFilter (yet), but my initial impression is that it filters rows.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Yes, it does. But you still have not given any user criteria. And you have not posted a representative sample of your worksheet. These things must be done in order for you to receive a full solution.

Will each user be viewing the same sheet? Will they each have their own?
 

Forum statistics

Threads
1,148,050
Messages
5,744,513
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top