K
Krista F
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
A B C D E F
W1 x x x
W2 x x x
W3 x x x x
W4 x
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
Fredrik Wahlgren
Krista F said: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
K
Krista F
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?
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?
F
Fredrik Wahlgren
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
=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
K
Krista F
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?
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?
F
Fredrik Wahlgren
Krista F said: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
K
Krista F
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
K
Krista F
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
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
F
Fredrik Wahlgren
Krista F said: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
K
Krista F
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
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
F
Fredrik Wahlgren
Krista F said: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_Your_First_VBA_Function_in_Excel/631.html
http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=35991
Here's one that many people recommend
http://www.amazon.com/exec/obidos/A...2/sr=2-1/ref=pd_bbs_b_2_1/103-6841181-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