Posts by AAE

Re: VBA to force drop down

Matt,

Welcome to Ozgrid.

Did you read the forum rules? Seems not.
You cross posted this same question at MrExcel without providing a link.

All Excel forums have the same rule about cross posting and for good read, all of which is clearly explain in Message to Cross Posters (click link in my signature to read).

We're glad to help . . . but you did agree to abide the rules when you joined - please have a read.

Re: Need Sheet Formula to pull key data from string

Brian,

Expanding on StephenR' post (#3) regarding thread titles:

If you are not aware, thread titles are used in searching the forum. A thoughtful, well-written title that accurately defines the thread will return much more relevant search results. Avoid useless verbiage/commentary/personal references; be as succinct as possible without being ambiguous.

Thanks.

Re: VBAor Conditional Formattng to create a FAUX Gantt Chart

Welcome to Ozgrid.

It is probably best to upload a sample workbook (dummy data) that illustrates your needs so that we have a clear understanding.

At minimum, provide samples of the Mon/Year data string so those helping know if you are using text headers or true dates formatted to show Mon/Year.

Re: Day of any given date

Or, you could just change the date format to display the day & date

dddd m/d/yyyy;@

No formula needed

If you need to retain the original format, then just use =A1 in the formula cell and set the cell format per above

Re: To sum if it matches the text

I think you must provide the forum with a clear explanation of your logic for when like values are to be summed.
For example, in your workbook you sum "ABC Inc 8.25%" and ABC Inc 5%" but exclude "ABC Inc".

Re: Data Validation using same key words but different list

GARCIAM10,

Please do not include comments such as "see thread 4 more details" as part of the thread title.
Members are going to read the thread without the need to be informed to do so.

Most importantly, though, thread titles are used in searching the forum and unnecessary text in the title dilutes the search results.
Accurate, well-written titles will yield more relevant results.

I've revised your title to remove the commentary.

Re: Unable apply data validation in pivot

Why do you need to apply data validation in a Pivot Table?
Data validation is designed to help enforce data entry not as a tool to manipulate a Pivot Table.

Pivot Table are designed to analyze the source data. Attempting to apply data validation toa PT is a misapplication of this feature.

You should just be using only the Report Filter and other features to configure the Pivot Table to obtain the analyses you need.

Re: Subtotal function

Thank you! :smile:

Re: Use a drop-down list to choose what kind of calculation to apply to certain cells

You might use this formula approach. Keep the original values in a hidden range or worksheet. This example uses column-P

=IF(A3="show original value",P3,P3/(1*MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),LEN(A3)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+1)))

This assumes you only ever want to divide. If you are going to use other operators you will need a different solution.

If cell A1 is used as a drop down to select the math operator (add, subtract, multiply, divide),
The item list in column-I would be changed to =$A$1&" by 2", etc. (or use =$A$1&" by "&ROW(A2) and drag the formula down to automatically increment the number value).

The formula to calculate per the chosen operator would be

=EVALUATE(P3&LOOKUP(A1,{"Add","Divide","Multiply","Subtract"},{"+","/","*","-"})&1*MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),LEN(A3)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+1))

It would simply the formula if you just used A1 to select the operator type and let the A3 drop down be a list of numbers only. Then we could just use:

=IF(A3="show original value",P3,P3/A3)

Re: Prevent user from taking photo of the screen

I'm not aware of any method directly available in Excel.
Maybe using a privacy screen on the display will work for you.

Re: cross lines on graph

Thanks for letting us know you found the solution. :smile:

Re: Create macro code that autofill cell with formula

Yes. It will work as is. You don't have to use structured references in Tables, cell references are fine.

Re: Subtotal function

OK . . . it is solved. Great!

Since this forum is all about sharing solutions care to contribute back to the community with yours?

Re: Identify activecell of a Shape

Shapes are separate objects that "float" above the work sheet. Placing them over a cell DOES NOT make them part of nor link them to a cell. In short, they are independent entities with their own properties.

Perhaps if you stated what you are trying to accomplish with the shape someone may be able to offer a solution.

Re: Automatically update other sheets as changes are made in master sheet

Cleburn,

Welcome to Ozgrid.

Please note the change to your thread title. I think you'll agree it is more descriptive of your thread than "REALLY need help building my schedule". Thread title are used in searching the forum, meaning a well-written title that accurately describes the thread will return more relevant search results. In future thread, give more thought to developing a proper title. If the current title is not right, feel free to make further changes.

Tip: your thread, as originally posted, was an eye chart (hard to read). I've broken it up to make it more readable. It is best to be as succinct as possible and use proper grammar and paragraphs, etc. Concisely stated thread are more likely to gain quick interest and help in the forum.

Thanks.

Re: Import Multiple Text Files into Excel

Wolfemann,

Welcome to Ozgrid and thanks for weighing in on this thread.
However, did you notice it is about three years old? Since this issue is resolved, the thread is being closed.

Please note: if you take interest in more recent threads, feel free to contribute toward a solution, but if you are going to ask a question be sure to start your own thread.

Thanks.

Re: Conditional count based on date and time criteria

Hello kitrak2,

Welcome to Ozgrid. :smile:

Please take notice of the new thread title I've given to your thread - it is far more accurate and descriptive of your thread than "Countifs/Sumifs".

Thread titles are used in searching the forum, so well-written thread titles will return much more relevant results.
In future threads give more thought in developing a helpful title that facilitates searching.

BTW: I got the wording for the new title right from the second sentence in your post. :wink:

Thanks

Re: If text is entered incorrectly, the cell turns red

To change the cell color when a typed entry does not match an item in your list requires use of Conditional formatting.
Conditional Formatting, using the formula option, requires the formula to evaluate to TRUE or FALSE.

You would need something along these lines, given only as an example (haven't looked at your file):

=COUNTIF(A1,Sheet2!$A$1:$A$100)<1

or

=ISERROR(MATCH(A1,Sheet2!$A$1:$A$100,0))

Search on Conditional Formatting and read up on it if you are unfamiliar with it.

Re: Display Alert Message based on due date

Paul51,

"Alert" is not a descriptive thread title.
Take note of the revised title I've given your thread: it is accurate and descriptive.
Thread titles are used in forum searches, thus well-defined titles will yield more relevant search results.

Please make a better effort with title in any future threads.

Re: Inventory list

paul51,

Welcome to Ozgrid

I'd say Ingo is referring to your post which is very poorly punctuated.
Your much more likely to get help quickly by using proper sentence structure and punctuation - it makes it much easier to read and understand your needs. I tend it ignore threads wherein users don't bother to take the time to clearly state their needs.