Select header if formula
 

I'm trying to find a formula that will

A B C D E F
W1 x x x
W2 x x x
W3 x x x x
W4 x

"Krista F" wrote in message
...
I'm trying to find a formula that will

A B C D E F
W1 x x x
W2 x x x
W3 x x x x
W4 x


I don't understand what you want to do. Can you please provide a verbal
explanation.

/Fredrik

Incomplete - sent by mistake...

I'm trying to find a formula that will give me the row header in column a if
there is an x in the column B1:B4.

A B C D E F
W1 x x x
W2 x x x
W3 x x x x
W4 x

I've tried =IF(B1:B4,"x",A1:A4) Probably not quite right...

My second problem is my workbook is beginning to get screwy. I will go to
enter a formula like the one above and it won't calculate it! I've checked -
auto calculate is on, I have no other workbooks open, ctrl+~ doesn't do
anything, ctrl+F9 minimizes the workbook, and under tools/options/calcs/ calc
sheet and calc now don't work. In fact, Ctrl N opened a new blank workbook.

This is a pretty complicated workbook with 8-10 pages of multiple linked
offset and vlookup formulas.... Am I overloading things?

If you want to test if there are x's in B1:B4 you can do this
=IF(B1&B2&B3&B4 = "xxxx", TRUE, FALSE)

What is the signifance of the other range, A1:A4?

CTRL+ALT+F9 will force a recalculation

Maybe you're overloading. I have had lots of problems with VLOOKUP. It seems
to interfere with the calculation engine

/Fredrik

"Krista F" wrote in message
...
Incomplete - sent by mistake...

I'm trying to find a formula that will give me the row header in column a
if
there is an x in the column B1:B4.

A B C D E F
W1 x x x
W2 x x x
W3 x x x x
W4 x

I've tried =IF(B1:B4,"x",A1:A4) Probably not quite right...



My second problem is my workbook is beginning to get screwy. I will go to
enter a formula like the one above and it won't calculate it! I've
checked -
auto calculate is on, I have no other workbooks open, ctrl+~ doesn't do
anything, ctrl+F9 minimizes the workbook, and under tools/options/calcs/
calc
sheet and calc now don't work. In fact, Ctrl N opened a new blank
workbook.

This is a pretty complicated workbook with 8-10 pages of multiple linked
offset and vlookup formulas.... Am I overloading things?

Actuallyif there is an x in B1:B4 I want it to list which rows they are in -
for the grid below querying Column C (poorly labeled B for example) it would
list W1, W2, W4.

I can't even test the formulas now... grr.... I knew vlookup was too good
to be true. Did you end up splitting the book into smaller pieces to solve
the problem or use a workaround statement?

"Fredrik Wahlgren" wrote:

If you want to test if there are x's in B1:B4 you can do this
=IF(B1&B2&B3&B4 = "xxxx", TRUE, FALSE)

What is the signifance of the other range, A1:A4?

CTRL+ALT+F9 will force a recalculation

Maybe you're overloading. I have had lots of problems with VLOOKUP. It seems
to interfere with the calculation engine

/Fredrik

"Krista F" wrote in message
...
Incomplete - sent by mistake...

I'm trying to find a formula that will give me the row header in column a
if
there is an x in the column B1:B4.

A B C D E F
W1 x x x
W2 x x x
W3 x x x x
W4 x

I've tried =IF(B1:B4,"x",A1:A4) Probably not quite right...



My second problem is my workbook is beginning to get screwy. I will go to
enter a formula like the one above and it won't calculate it! I've
checked -
auto calculate is on, I have no other workbooks open, ctrl+~ doesn't do
anything, ctrl+F9 minimizes the workbook, and under tools/options/calcs/
calc
sheet and calc now don't work. In fact, Ctrl N opened a new blank
workbook.

This is a pretty complicated workbook with 8-10 pages of multiple linked
offset and vlookup formulas.... Am I overloading things?

"Krista F" wrote in message
...
Actuallyif there is an x in B1:B4 I want it to list which rows they are
in -
for the grid below querying Column C (poorly labeled B for example) it
would
list W1, W2, W4.

I can't even test the formulas now... grr.... I knew vlookup was too
good
to be true. Did you end up splitting the book into smaller pieces to
solve
the problem or use a workaround statement?


I worked for a company that now has been acquired by Cognos. I made an
add-in to get general ledger data. Someone had made a formula where the
output of a vlookup function was used a parameter for the add-in. There were
only two values to choose from so I replaced it with an IF statement. I
think someone else used nested vlookups. It had some serious problems too.

/Fredrik

I worked for a company that now has been acquired by Cognos. I made an
add-in to get general ledger data. Someone had made a formula where the
output of a vlookup function was used a parameter for the add-in. There were
only two values to choose from so I replaced it with an IF statement. I
think someone else used nested vlookups. It had some serious problems too.

Okay. Thanks - any further help on the first formula problem?

IE - getting a list of the Row A variables (WW1, WW2) when a box is checked
within a column?

Thanks Tons!! Krista

"Krista F" wrote in message
...

I worked for a company that now has been acquired by Cognos. I made an
add-in to get general ledger data. Someone had made a formula where the
output of a vlookup function was used a parameter for the add-in. There
were
only two values to choose from so I replaced it with an IF statement. I
think someone else used nested vlookups. It had some serious problems
too.

Okay. Thanks - any further help on the first formula problem?

IE - getting a list of the Row A variables (WW1, WW2) when a box is
checked
within a column?

Thanks Tons!! Krista

I have created a UDF which does what I think you want. I have included the
workbook
Here's the outcome

W1 x x
W2 x x x
W3 x x
W4 x

W2, W3 W1, W2 W2, W4 W1, W3


And here's the code

Public Function GetHeader(r As Range) As String
Dim Items As Long
Dim i As Long
Dim s As String
Dim r2 As Range

Items = r.Columns.Count * r.Rows.Count

If 0 = Items Then
GetHeader = ""
Exit Function
End If

For i = 1 To Items
If 0 = StrComp(r(i), "x", vbTextCompare) Then

If i 1 And 0 < Len(s) Then
s = s & ", "
End If

s = s & Range("A" & CStr(i)).Value
End If
Next i

GetHeader = s
End Function

/Fredrik

Fredrik,

That is amazing. I have never done any programming in excel and had no idea
how powerful it could be! This does what I need.

One last thing - is there any way to tell this UDF --getheader(a1:a6) -- to
place each resulting value in it's own cell vs. a string within one cell?

Thank you again! I am really looking forward to learning more about this
stuff.

Krista

"Krista F" wrote in message
...
Fredrik,

That is amazing. I have never done any programming in excel and had no
idea
how powerful it could be! This does what I need.

One last thing - is there any way to tell this UDF --getheader(a1:a6) --
to
place each resulting value in it's own cell vs. a string within one cell?

Thank you again! I am really looking forward to learning more about this
stuff.

Krista


Unfortunately no. Any kind of macro code that is called from a cell can't
put values in other cells. User Defined Functions (UDF's) can only return a
value from the cell it was called from. I'm not 1005 sure I understand what
you want, but I think you can achieve something similar by using this
function in different cells with different parameters.

UDF's are indeed powerful and useful. If you ever have managed to create a
really complicated formula with lots of neset functions that you use all
over you sheet, you're probably better off with a UDF. There are two
reasons:

1) UDF's are easier to understand since they are more structured.
2) They are also easier to maintain. If you find that you need to change
your formula, you may need to apply the change to hundreds or thousands of
cells. With a UDF, you only need to make a single change. You don't have to
worry about the possibility that you have missed one formula.

/Fredrik

Totally makes sense... Thanks.

Do you know a good reference site/book that teaches about UDF's? I'd like
to try and disect the one you provided so I uderstand what each command line
is doing... Then perhaps I can start tweaking it for other data I am
analyzing!

Krista

"Krista F" wrote in message
...
Totally makes sense... Thanks.

Do you know a good reference site/book that teaches about UDF's? I'd like
to try and disect the one you provided so I uderstand what each command
line
is doing... Then perhaps I can start tweaking it for other data I am
analyzing!

Krista

Here are a couple of links.
http://www.fontstuff.com/vba/vbatut01.htm
http://www.exceltip.com/st/Writing_Y...Excel/631.html
http://www.accountingweb.co.uk/cgi-b...m.cgi?id=35991

Here's one that many people recommend
http://www.amazon.com/exec/obidos/AS...841181-2022215

Since there are so many books on Excel, the best tip is to go to your local
Barnes&Noble and have a look at the books wich have the word VBA in their
title. You have to figure out which one seems right for you. If you do a lot
of work with Excel, you will find that a good book will pay itself in a
short period of time. Much to my surprise, I have met many accountants which
didn't know anything about UDF's. These guys came from all over the world.

/Fredrik