Replies

To take up Yo’s challenge how about this:

=IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(TEXT(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0),”MMM”)=E$1,VLOOKUP($A3,INDIRECT(E$1&”!”&”A:j”),10,0),””))

To take up Yo’s challenge how about this:

=IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(TEXT(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0),”MMM”)=E$1,VLOOKUP($A3,INDIRECT(E$1&”!”&”A:j”),10,0),””))

After you have “selected a range including hidden rows and/or columns”
Just type Alt + ; (semicolon) and save a few clicks.

Ken

You’re close. Use Exit For

Put this formula in D1

=SUM(IF(A1>=90,100,A1),IF(B1>=10,20,B1),IF(C1>=3,5,C1))

Hope this is ok, I’m home sick today

Ray,

I’ve got no experience with the draglock feature or working with laptops. Sorry. Maybe someone else can help.

Ken

Try using the Index function with two Match functions to supply the row and column parameters, Thusly

Ray,

Oddly, my Excel (2000) started doing this exact behavior right after reading your post. Do you have a wheel mouse? FWIW, I got rid of the behavior by clicking the wheel. If that doesn’t work I’d look into reverting any customized mouse settings to defaults, or see if there is an updated driver for your mouse.

Try this custom format: @*.

The @ stands for text, the * tells excel to repeat the dot to the end of the cell

HTH
Ken

Here’s an example of finding the last column with an array formula.

First example puts the last column in a separate cell, the second integrates it into your own formulas in place of the match function.

HTH
Ken

You might consider changing all instances of plain alpha to alpha-0

Peter,

Here’s an alternative approach.
1) Open your workbook as read-only.
2) Copy and Paste-Values over the sheets you want to distribute.
3) Delete unwanted sheets (Can be done as a group)
4) Do a Save As

Ken

Ken

Thanks to all who answered my post. Adding an additional column is not an option that I wish to use because this example is cut down from a form that I use. To start adding column(s) would require a lot of reworking of macros and the form itself (although I suppose I could use a hidden column). Anyway Aladin nailed it for me. Thanks Aladin.

Ken

Keith,

Look at this method. I use it a lot in my job. It uses this formula:

[Budgeted Amount – Amount Spent To Date / Remaining number of months]

Just replace the formula each month with the actual spending and the subsequent months will
average out the remaining available balance.

HTH
Ken

Mac,

Thanks for the help. Seems simple enough, just not intuitive. At least to me, at least not yet.

Ken