Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2017, 10:52 AM
mreynolds1775 mreynolds1775 is offline Charting variable ranges Windows 10 Charting variable ranges Office 2013
Novice
Charting variable ranges
 
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 https://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 Charting variable ranges Windows 7 64bit Charting variable ranges Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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 )
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 09-20-2017, 08:32 AM
mreynolds1775 mreynolds1775 is offline Charting variable ranges Windows 10 Charting variable ranges Office 2013
Novice
Charting variable ranges
 
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 Charting variable ranges Windows 10 Charting variable ranges Office 2013
Novice
Charting variable ranges
 
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 Charting variable ranges Windows 7 64bit Charting variable ranges Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

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
Charting variable ranges 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
Charting variable ranges Run-time error 91 object variable or with block variable not set JUST ME Word VBA 4 03-25-2014 06:56 AM
Charting variable ranges Run-time error '91': Object variable or With block variable not set tinfanide Excel Programming 2 06-10-2012 10:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft