Array Formula to Retrieve a Text Value
Thanks to RonR I have this formula which retrieves numerical data from "DataSheet" for use in another worksheet called "Last 3 Events".:
=VLOOKUP(B9,OFFSET(DataSheet!$E$18:$E$962, LOOKUP(2,1/(LEN(DataSheet!$E$18:DataSheet!$E$962)>0), ROW(INDIRECT("1:945")))-30,0,15,19),19,FALSE)
I need a formula that will do the same thing but retrieve text to place a title for each of three tables in "Last 3 Events".
The formula above reaches up for past data as data is added to the bottom of the data area in "DataSheet".
In this way, the data
is automatically updated in the tables.
Now I need the titles of the tables to update with the text values.
The titles will be a text value found in column B of "DataSheet" in the same line as in the above formula example.
However, there will be three formulas
If I can get one, I can adapt the others.
Here are the specs:
-Overall data range is 18:962 of "DataSheet"
-Data is arranged in sets of 15 lines of data.
A new set of data is added each week beginning at line 243.
The data above 243 is from the last 15 weeks of the previous calendar year.
The formula should reach into this area during the first three weeks
of the new year but there is never a risk the formula will reach outside the data area.
-Data to be retrieved is in column B of "DataSheet"
-I am seeking to retrieve the titles for three tables, one column B value in "DataSheet" from each of the following lines above the first empty row of data in "DataSheet", 15, 30, and 45
-I need the column B value of "DataSheet" of the line 15 lines above the first empty data row in "DataSheet" in the range 18:962 that is located in the same row as the column E value in "DataSheet" that matches B9 of "Last 3 Events".
Then also up 30 and
45 lines for H9 and N9 (I can adapt these)...
Keys to the Problem Array Formula to Retrieve a Text Value
My formula logic seems to match yours. Just make a small change in the range of the formula in cell B7
Does this help?
Using Windows troubleshooter:
- Click on Search on Charm bar and type Troubleshooting. Click on the very first option.
- A new window will open. Click View all from the left pane.
- Scroll down to the bottom and click on Windows update.
- Troubleshooter window for Windows update will pop up. Click on NEXT. It may ask you for administrative permission.
- The troubleshooter will automatically detect problems and fix it.
- Now, close this window and try download/install the app which was previously showing error.
Another Safe way to Repair the Problem: Array Formula to Retrieve a Text Value:
How to Fix Array Formula to Retrieve a Text Value with SmartPCFixer?
1. Click the button to download SmartPCFixer . Install it on your computer. Open it, and it will scan your system. The errors will be shown in the list.
2. After the scan is finished, you can see the errors and problems which need to be fixed.
3. When the Fixing part is finished, your computer has been speeded up and the errors have been fixed
Related: AMD Radeon HD 7800M Win8 not working [Anwsered],I can access the internet, get on facebook and get to hotmail, but I can't play games on facebook and I can't open or respond to my e-mails,I keep getting this Media Player error when I log on my computer. [Anwsered],[Anwsered] System Hanging on shutdown and restart,Unable to get the Vlookup property of the WorksheetFunction class,Solution to Error: Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive.
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,I'm always being signed off so annoying Tech Support
,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.
,Solution to Problem: Referencing data in another file
,Troubleshoot:Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive. Error,External Hard Drive not listed in Windows 7 backup wizard Tech Support,Tech Support: I'm always being signed off so annoying,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.,Referencing data in Access using Excel [Anwsered],Need Best Way To Present Data [Anwsered],Same question but for windows 7 home edition,sometimes fullscreen won't activate [Solved],Solution to Error: We bought a new computer with windows 7 and it is constantly freezing. How do we fix this?,Solution to Error: Windows 8 update crash (2013-07-22)
Read More: How to Fix Problem - Auto updates\/scheduled maintenance?,Auto play does not function in spite of changing the settings. [Solved],How Can You Fix - Atapi & Ataport.sys Error --please help!!?,Solution to Error: Automatic Numbering Of Equations in Word 2010,Troubleshoot:AutoFormat: Set only Left indent? Error,application not found error,any problems in a team where one has Windows XP and the other has Windows 7?,Application/Object-Defined Error,An Excel formula question where hours are totalled and cumulating,Anyone know the hardware email?