Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-19-2017, 10:52 AM
mreynolds1775 mreynolds1775 is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default Charting variable ranges

Quote:
Originally Posted by Pecoflyer View Post
Have a look here to understand this technique http://www.xldynamic.com/source/xld.LastValue.html
Be aware that OFFSET and INDIRECT are volatile functions
great stuff peco.



again, this opens a new question. Lets say I want to copy the last 7 transactions, and paste them on another sheet so that I can chart them?

I'm assuming I could use a formula that looks to the last cell in that row, then the last cell in that row minus one cell position (to the left), then minus two cell positions, etc.?

I've got the "find the data from the last cell containing a number" thing by using the following lookup: (the desired result for this box is 6, and it returns 6)

=Lookup(9.99999999E307,'Sales'!198:198)

How can I get the cell directly to the left of it? I've tried =Lookup(9.99999999E307,'Sales'!198:198) -1 but that returns the value of 6-1. It should be 9.

Edit mod : post related to http://www.msofficeforums.com/excel/...a-columns.html

Last edited by Pecoflyer; 09-19-2017 at 11:29 PM. Reason: Add link
Reply With Quote
  #2  
Old 09-19-2017, 11:39 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Perhaps using a named range referring to
Code:
=INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3)-3):INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3))
Replace the 3 with any value or, better , with a cell reference. This will give you more flexibility[/code]Then use the named range as data source for your graph

( I hope I got the brackets right )
Reply With Quote
  #3  
Old 09-20-2017, 08:32 AM
mreynolds1775 mreynolds1775 is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Perhaps using a named range referring to
Code:
=INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3)-3):INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3))
Replace the 3 with any value or, better , with a cell reference. This will give you more flexibility[/code]Then use the named range as data source for your graph

( I hope I got the brackets right )
Code:
=INDEX('Sales'!$M198:$HR198,MATCH(9.99E+307,'Sales'!$M198:$HR198)-30):INDEX('Sales'!$M198:$HR198,MATCH(9.99E+307,'Sales'!$M198:$HR198))
returns a #Value.... any assumptions?
Reply With Quote
  #4  
Old 09-20-2017, 11:49 PM
mreynolds1775 mreynolds1775 is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Thumbs up

Peco, et al.,

Got it figured out. I only needed the first half of that formula as I am not looking for a range, but a specific cell in the index.

=INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3)-1)
=INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3)-2)
=INDEX($E3:$Z3,MATCH(9.99E+307,$E3:$Z3)-3)

etc...
Reply With Quote
  #5  
Old 09-21-2017, 12:26 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Quote:
Originally Posted by mreynolds1775 View Post
Code:
=INDEX('Sales'!$M198:$HR198,MATCH(9.99E+307,'Sales'!$M198:$HR198)-30):INDEX('Sales'!$M198:$HR198,MATCH(9.99E+307,'Sales'!$M198:$HR198))
returns a #Value.... any assumptions?
Returning an error as named range?
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable shimulsiddiquee Excel 1 05-16-2017 07:39 AM
Take String of numbers, expand ranges, sort, then compress back into ranges AustinBrister Word VBA 19 08-22-2016 05:18 PM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge Berryblue Mail Merge 1 11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set JUST ME Word VBA 4 03-25-2014 06:56 AM
Run-time error '91': Object variable or With block variable not set tinfanide Excel Programming 2 06-10-2012 10:17 AM


All times are GMT -7. The time now is 09:31 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft