#1
|
|||
|
|||
Charting variable ranges
Quote:
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 |
#2
|
||||
|
||||
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)) ( 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 |
#3
|
|||
|
|||
Quote:
Code:
=INDEX('Sales'!$M198:$HR198,MATCH(9.99E+307,'Sales'!$M198:$HR198)-30):INDEX('Sales'!$M198:$HR198,MATCH(9.99E+307,'Sales'!$M198:$HR198)) |
#4
|
|||
|
|||
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... |
#5
|
||||
|
||||
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 |
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 |