Replies

What you need is :
=VLOOKUP(A3,INDIRECT(“testsheet.xls!”&A1),2)

but the disadvantage is that it will only work when testsheet.xls is open.

Glenn.

It sounds as if a dynamic defined name for your look-up range will do the trick.

In the worksheet, use menu command Insert/Name/Define, and choose a name for the range containing all the values, e.g. LookList, and then in the Refers To box type this formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5)
or something similar ( this formula is for sheet Sheet1, and for a look-up range containing only 5 columns ).

The COUNTA function counts all non-blank entries in column A, and then the OFFSET function creates a range the same length as the count, and a wide as is necessary ( 5 in this case ).

Then you can use the name LookList in your VLOOKUP, so that the correct range is used every time.

Hope that helps.
Glenn.

If my memory serves me correctly I think it’s

=GET.WORKBOOK(4)

that will do the trick.

Glenn.

You’ve already discovered the easiest way ( and, as far as I know the only way ) to do a waterfall chart.

Sorry, can’t really say any more than that

Glenn.

You’re going the wrong way through the list … forward!!! You should be going backwards ( from the end upwards ), otherwise when you delete a row and go to the next, the row shifting upward will be skipped by the processing. Change your IF block to be :

If Range(“A” & I+1).Value = “” Then
Range(“A” & I+1).EntireRow.Delete
End If

Cheers, Glenn.

use single quotes around the two words of the sheet name, as in
=’Master Source’!A1

That should fix it.
Glenn B

No, you’re not missing anything. The four arguments limit, and the fact that only one condition is allowed per argument means that it’s usefulness is quite limited, but when your needs can be met this way it’s a quick way to achieve something nifty very quickly.

Glenn Bumford

Thanks John, have edited my posting now, so should make more sense.

Anyway, the rules for conditions of your own in Custom Formats is very similar to the standard, except that when you create conditions the rules go something like
[condition 1]number format 1 ; [condition 2]number format 2;number format 3;text
where number format 3 is applied to cells that fail conditions 1 and 2.

Glenn Bumford

You can have more than one section with a clause in it. Try this
[=-100]”NA”;[ red ][<0](#,##0);#,##0_)
which will make your negatives red with brackets round. ( Leave the spaces out of [ red ], as I've done this to stop it being mistaken for a posting font control string. )

Glenn Bumford

Use a custom number format to display -100 as NA. Choose menu command Format/Cells and click the Number tab, and select Category “Custom” from the list on the left hand side, and then in the Type input box type this :
[=-100]”NA”;#0
and click the OK button, and that should do what you want.

Glenn Bumford

Hmm, you’ve said that maybe Application.WorksheetFunction.Round may only work in Excel 2000; well, in Excel 5 and Excel 95 the way to call this was Application.Round, so maybe that will work in Excel 97.

Glenn Bumford

See this attachment. It uses defined names for each category and month, and defined names for the cells containing the choices made by the drop down lists. And finally it uses a defined name formula of
=EVALUATE(Category&” “&Month) in a name of Result, to calculate the required value.

To define names for all categories and all months, select the block containing all of the data, including the row containing the month names, and including the column containing the category descriptions, and press Ctrl-Shift-F3 to create the required names ( it will display a prompt for how to create names, with tick-boxes, of which Top Row and Left Column are the boxes that need to be ticked before pressing OK ).
You define names for the drop down choice cells in a similar way, or use the menu command Insert/Name/Define to create names.

I think this method is more readable than Legare Coleman’s ( post 82496 ), although this method does not recalculate automatically, i.e. after a choice has been made and a figure is returned, if then the actual figure in data changes, the amount shown by the formula “=Result” doesn’t update.

Glenn Bumford

You can do this without sorting if you have 3 intermediate columns.

This example is using a list that extends to row 25.
Assuming that row 1 has text titles, and the data starts in row 2, then in column B from row 2 onwards have this formula
=MATCH(A2,$A$1:$A$25,0)
which calculates the row position of the first occurence of any animal.

And in column C from row 2 onwards have this
=IF(NOT(ISNA(INDEX($A$1:$A$25,MATCH(ROW(),$B$1:$B$25,0)))),”here”,”not”)
which singles out the first occurence of the results in column B.

And in column D have the number 1 in cell D1, and then also have this formula
=MATCH(“here”,OFFSET($C$1,D1,0,500,1),0)+D1
from row 2 onwards. Which collects the individual animal names row positions.

Then in columns E and F have the results, using these formulae. In column E have this formula
=IF(ISNA(D2),IF(COUNTIF($E$1:E1,”=”)>0,””,””),INDEX($A$1:$A$25,D2))
from row 2 onwards. Which creates the names of the animals in the cells at the top of the column,
together with a special entry for blanks. And then in column F have this formula
=IF(E2=””,COUNTBLANK($A$1:$A$25),IF(ISNA(D2),””,COUNTIF($A$1:$A$25,”=”&E2)))
which gives the count for each animal.

You can then group the columns B thru’ D, or hide them, as they are only necessary as intermediate steps.

Hope this is useful.
Glenn B