Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Ms Excel


  • Please log in to reply
1 reply to this topic

#1 triple h

triple h

  • Members
  • 1 posts
  • OFFLINE
  •  
  • Local time:10:03 AM

Posted 28 March 2007 - 04:24 AM

Im working on a spread sheet for bills and such.

What I have is multiple worksheets that track different accounts. In the end I have a summary worksheet, such that I pull some random info from the other worksheets into that one.

On a spreadsheet for a Credit Card, for instance, I make entries that keep scrolling down (have the rows frozen just about the headings).

Different accounts will have a different # of entries, so there is no static cell that I can pull the most current info from a certain sheet to the status sheet. So what I want to do is setup a status 'row' in the current sheet, that will always take the furtherest value down in its column. For instance, I'll keep row 5 as my status column in a certain sheet. This means, I want to keep the most current (furthest down) values copied into this row.

Row 5 - xxx yyy zzzz
Row 6 -
Row 7 - 111 111 1111
Row 8 - 222 222 2222
Row 9 - xxx 333 3333
Row 10 - 444 4444
Row 11 - yyy 5555
Row 12 - zzzz

The values in row 5 will always change to whatever the last cell in its respective column has, as long as its not blank. I will then take row 5 and reference it from my status sheet to pull the most current values to that sheet.

Is this possible? Do I need to explain it further?

[edit - this forum deleted my white spaces. There is no value for column 1 after row 9. No value for column 2 after row 11. Only column 3 has a value in row 12]

Thanks

Edited by triple h, 28 March 2007 - 04:25 AM.


BC AdBot (Login to Remove)

 


#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:11:03 AM

Posted 02 April 2007 - 03:12 PM

I may be misunderstanding, because your example looks inconsistent with the sample data
Row 5 - xxx yyy zzzz
yet this looks like the last data item in the column
Row 12 - zzzz

I'm answering this requirement: "So what I want to do is setup a status 'row' in the current sheet, that will always take the furtherest value down in its column"

Key difficulty is not knowing where the data ends, unless you have named a range and new data is inserted (the easiest way), but I have a feeling you don't want that.

Assuming you have no blank cells in a column, using the below formulas, MATCH returns relative row number, OFFSET returns a value given current position and the offset of rows returned by Match, and zero columns offset.
Here are examples for 3 columns, so you can generalize. Substitute for "20" some higher, but reasonable number to span a larger range, like B100, C100 and so on.

In B5 create or just paste this formula =OFFSET(B5,MATCH("",B6:B20,-1),0)
In C5 create or just paste this formula =OFFSET(C5,MATCH("",C6:C20,-1),0)
In D5 create or just paste this formula =OFFSET(D5,MATCH("",D6:D20,-1),0)

The simplest way is to just paste the formula into B5, and drag it across other columns. So long as you don't put $ on the reference, columns will get adjusted.

Please do not have a blank row 6. Increase the height of row5 if it looks better, and/or freeze panes below row 5 if you need to scroll to the bottom a lot.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users