Excel 2007 formula question 1

[ITALIAORIANA]

Hi All,

I have a 2007 excel spreadsheet that has several tabs with information. The last tab is a summary. I am able to pull information from all the tabs to the summary with no problem, except the class tab. I have a formula to pull info from the class tab --- (='Tab 4 - Class ID'!D7&"-"&'Tab 4 - Class ID'!D16). This returned CL01-CL10. Which is exactly how it needs to look on the summary tab for the class id's. The problem is, the information is variable per client. It always starts on D7 but it could end at D8 or the information can go to D40 etc....

My question is......Is there a way to tweak this formula to find the end of the data and then show that value? If the data ends at D16 then show D16 value, if it ends at D75 show D75 value.

Thanks
Deana

 

[SkipVought]

hi,

I would be very VERY suspicious of direct references to dirplay data in a range.

I most often use MS query to return data like this from some other sheet.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Hi Skip,

I must be doing something incorrectly because the ms query keeps giving me the values listed exactly the same way they are in the class tab. The class tab is set up in column form, but the clients want to see a summary tab that would show the first value a dash and last value. So the class tab has all values listed out in the column CL01, CL02 CL03......CL10, and on the summary the client only wants to see it displayed in one cell as CL01-CL10, or whatever the last value is in the series.


Thanks
Deana

 

[SkipVought]

For the data in your source table, if you use either Dynamic Named Ranges or Structured Tables (Excel 2007+) your dynamic column range can give you the first and last values, using INDEX, and COUNTA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Hi Skip,



LOL, and just think.....I was all proud of myself because I figured out how to use the MS Query from the FAQ you supplied. This is way past my knowledge level. Below is how I read your response



For the data in your source table, if you use either GREEK GREEK GREEK or GREEK GREEK (Excel 2007+) your GREEK GREEK GREEK can give you the first and last values, using GREEK, and COUNTA.


Deana

 

[SkipVought]

You have a very powerful tool at your disposal. You can choose to use it like a cheap Pinto or the Maserati that it could be.

faq68-5184

faq68-1331

Excel Help on Structured Tables

On this issue, first and last in a dynamic range, if you have a Named Range Name, then
[tt]
=INDEX(OFFSET(Name,0,0,COUNTA(Name),1),1,)&"-"&INDEX(OFFSET(Name,COUNTA(Name)-1,0,COUNTA(Name),1),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Hahahaha!! Looks like I have some studying to do because I prefer the Maserati! Thanks so much for your help.

 

[SkipVought]

And you, too, can become a cool geeky dude, tooling along in your tek-tip Maserati! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

If you know that the last piece of data is text, you can return it using LOOKUP:

=LOOKUP("zzzzz",'Tab 4 - Class ID'!D7:D100)        returns last piece of text in range D7:D100

So your overall formula could be:

='Tab 4 - Class ID'!D7 & "-" & LOOKUP("zzzzz",'Tab 4 - Class ID'!D7:D100)

LOOKUP is expecting your data to be sorted in ascending order. It will then search for the text "zzzzz" which won't be found in column D. It keeps looking until it finds data that alphabetizes after "zzzzz". Since it won't find any such data, LOOKUP then returns the last piece of text that it did find.

You can also use LOOKUP to find the last number (or date). Just give it a number that is bigger than any that LOOKUP will see in the range being searched.

Brad

 

[byundt]

Skip's dynamic named range is a great tool to master. Once you do, you'll find yourself using it in many places.

There is one limitation to the formula Skip suggested: there can be no blank cells until the end of your data. COUNTA will ignore the blank cells, so you may find your dynamic named range ending before the end of your data.

 

[SkipVought]

in which case, if that range has empty cells, COUNTA any column that does not have empty cells

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}