Column A contains numbers that I want to SUM. But only if they correspond to cells from EITHER of any other columns that contain a word.

For instance

A1 = 100
A2 = 50
A3 = 25
A100 = 5

B1 = max
B2 = (empty)
B3 = (empty)
B100 = (empty)

C1 = (empty)
C2 = maxwell
C3 = (empty)
C100 = the max

How do I make a formula so that IF EITHER columns B OR C contain "max" in any way, that the corresponding cells in column A will be summed ? And just incase, what if I wanted to include multiple other columns, column B,C,D for instance.

having trouble with foruma for last colum ( Axis) the maximum value of the Axis can only be 180. If 100 degrees is added then the vaule will be 80.

I have steps 1 and 2 which are simple adding forumla but I cant work out the formula for step 3 as shown below

Transpose -3.00 +2.00 x 30 

Hey everyone,

I am working on a VBA Code that is supposed to track the last 5 changes made to cells and displaying them as comments (without having to share the workbook).
I already found a really good Code that displays the last 5 changes made to a cell.

Nevertheless if I open the file again after having saved my changes, the previous comment content is displayed at first, but deleted completely if I edit the cell.

Is there a way Excel always displays the last 5 changes made to the cell without deleting the complete comment content in case someone else makes a change ?

I am struggling with a formula I am currently trying to create.

Worksheet 1 = S1
Worksheet 2 = S2
Cell 1 on sheet 1 = S1C1
Cell 2 on sheet 1 = S1C2
Cell 3 on sheet 1 = S1C3
Cell 4 on sheet 2 = S2C4
Column 1 on sheet 2 = S2Co1

My formulat got to look if S1C1 exist in S2Co1, and if S1C2=WELL or S1C3=TOO, it got to return me the content of the S2C4

I have tried with IF AND and Vlookup, and got totally lost.

Can anybody explain me how I am supposed to do this?

Thanks a lot.

Hello,

Attached is my spreadsheet (SHIFT_COMPARE) which is supposed to plot the work hours that employees are scheduled to work vs the day they are working, on a weekly basis.

It consists of 3 worksheets: "Const", "Sched", & "Chart".

"Const" = a "back end" sheet containing constants & raw data which I used to create static & dynamic ranges

"Sched" = the data sheet where the manager will select the employee & choose that employee's start time & day(s) they are requested to work

I've got a spreadsheet which has a basic VBA macro which creates an Outlook appointment but have been asked to develop it much further and am completely out of my depth so am looking for help on this and hoping somebody may already have a script they can provide.

The spreadsheet has the following:
1. A name in column B
2. A date in column L
3. A note in column O

There are approximately 100 entries in the spreadsheet today but it changes daily with more entries added and some removed (items removed do not need any calendar entry altering).

What I need is:

Hello,

This is my first post.

Attached are two spreadsheets. One contains a list of records i.e. name, company name, title, address etc. The second shows how i would like to see these records (in a row format as opposed to a column format). Is there a way to program this? I have about 450-500 records in a column format.

Can anybody help me?

Thank you,
Kevin

I am currently using ADODB.connection to establish a link to Oracle and ADODB.recordset to store the returned data, then use Range.copyfromRecordset() to populate the result into a worksheet.

Hi,

I'm looking for hep with macro's excel 07. I manage a number of build managers that need to provide actual and forecast date info into report which is then highlighted to show the cells that have been updated. Then Admin checks the report and uploads the amended date information to a database.

Just togive you an idea of my knowledge on Excel, I am not an excel guru but can write small formulas and do vlookups, so could do with some help on how to approach this.

Start out with a column of randomly generated numbers. There should be 300 numbers in the column. You will need at least 4 arrays in your program, each holding 300 values.
1. Bring the values that you generated in the first column in your spreadsheet into an array named FirstCol.

2.Convert the values into integers and save them into an array named SecondCol.

3. Loop through the values in SecondCol.
a)If the value is less than 10 put that value in an array named Less10.
b)If a value is equal to or greater than 10 but less than 20 put that value into an array named LessTwenty

I am working on a graph and the scale graph number shows as 2"500'000
but I need to edit it to show 2"500
the current custom number setting is [>=1000000]#''000'000;[<=-1000000]-#''000'000;#'000
i tried [>=1000000]#''000;[<=-1000000]-#''000;#'000 but it changes to 2500"000

Hi All,

I have two features for three products (A,B and C). These features are: Dist and Exp.

There are 6 spreadsheets that I want to combine by feature. So, what I would like the Macro in spreadsheet

1. 'Consolidate_exp': Copy and paste the contents from the three spreadsheets (A_exp, B_exp and C_exp) one after another so that I have all the data in a master spreadsheet 'Consolidate_exp'.

2. 'Consolidate_dist': This should create a new Sheet (Sheet1, say) and copy and paste all contents from the three spreadsheets (A_dist,B_dist and C_dist) in the Sheet1.

Hi

I am working with Excel for a research project that I am working on and have reached a roadblock. My goal is to get collegiate data from over 100 quarterbacks. I have created a web query that looks like this:

WEB
1
http://www.sports-reference.com/cfb/players/["name","EnterName"].html

Selection=passing
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

From my macro-enabled workbook, I want to open another workbook, and then use the "find" feature to locate a drug name. Part one, open the workbook works fine, Part two, find the data, was derived from "recording a macro", fails. What am I doing wrong?

'This coding opens the drug list workbook
Sub cmdDrugsRev_Click()
Path = ThisWorkbook.Path & "\"
Application.Workbooks.Open(Path & "HLDrugList.xlsx").Activate

'This coding asks for input, then finds the drug
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

I have a folder with around 100 excel files, all in the same format. I want to write a macro which can collect a heading from these files and also the data and save as a text file wiht pipe as delimiter. can someone help me please.

This is a bit urgent

Cell N2 is an automatically updating cell showing today's date
Cell K5 shows the date an individual began a certain status; (obviously, if the individual has not begun that status, K5 will be blank)
I want cell Q5 to show "0" if the individual has not begun that status (if K5 is blank) OR to show the number of days since he began that status to present (N2-K5).

I'm not clear on how to write this logical argument

I want calculate hours bet two dates with conditions i want result as two dimensions.
I have enclosed excel sheet please find the attachment

activity start date activity end date
4/5/2013 9:01 4/5/2013 23:00
4/6/2013 10:01 4/7/2013 23:10

if activity start time less than <10:00 am
if activity start time greater than >10:00 am

activity end date time should be completed same day 11:00 pm (23:00:00)
activity end date time should be completed next day before 11:00 pm(23:00:00)

if
one result wants calculation

Hi,

I have series of sheets in a workbook and i want the data to export to csv format. can someone help me. the data given is in a template given below. there are around 10 such sheets, if i save as csv format, i get some junk character and the format changes. also csv format doesnt accepts multiple sheet. need help. Also can someone let me know the code to pick up only the entered data from the template below and not the headings. bcoz the headings are not in one row

Entity ID
adasdasdas

Entity (Account) Name
sdfsfsfsd

Submitted by Name
dfsdsfsfsd

I have created a python script to create a text file which has the Tag:Value of Tag read from thousands of xml datasets. Then I created an excel form to allow me to view the data in individual columns. Unfortunately as the xml's are not structured the same, a simple excel clean up routine does not work.

I want to create a macro or formulae (maybe with vlookup) that will first run through the whole dataset and then create a unique list of tags. It should then run through the text file and populate the value of each tag in to the relevant tag column.

Can anyone help?

I have attached.

I have created a shared workbook to update certain entries by my colleagues. I have noticed that some of the entries were deleted after some time. I want to know if I can share the work with the following feature:-

As soon as somebody enters data to a cell, it should be automatically locked and should not allow anyone to delete or edit the cell.

How do I do it? Please help me. Thanks in advance.

Dear experts, My data is as follows, please suggest me how to plot summary in just one graph.
The graph should show for each region, in each month how many Project IDs were success (Pass), Fail, FD (Marked for future implementation), and are WIP (in progress).

My mind is not functioning as to how without copy paste we can use this data and convert into one graph. I tried pivot->graph but did not get desired results.

ProjIDS Region Month1 Month2 Month3 Month4
0.88 NA Pass FD Fail WIP
0.30 NA Fail FD Fail WIP
0.33 NA WIP FD Fail WIP
0.91 NA FD WIP Fail WIP

Hi Experts,

Following is my data, i need to present week wise project status for entire program hence project ids are not required. Earlier i was expected to give consolidated report (irrespective of region), hence it used to be easier. Now for all 4 weeks i have to make a common graph which will also indicate region so that region wise status is clear. Please advice how to fit all data in one graph yet highlight the region wise %.

Note: FD= project is planned to be tested in future weeks.
ProjIDS Region Week1 Week2 Week3 Week4
0.88 NA Pass FD Fail WIP
0.30 NA Fail FD Fail WIP

Here's the challenge I am having. I have a SUM array formula in one cell. One of the data elements I am trying to sum up in the array cell is pulling from another cell that has a SUM formula already in it. That second cell with the formula is not an array. I am unable to successfully pull that data from the second cell to my first SUM arrany cell.

What I did was to have the value in the second cell pull to a third cell using a simple copy formula =E5 However, the SUM array on the first cell wouldn't work with this scenario either.

Hi Experts,

i have only basic knowledge in VBA
please find below codes i have done.

Range("d23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("d2"))
Range("e23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("e2"))
Range("f23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("f2"))
Range("g23") =

I have got a sheet having below formula:

=VLOOKUP($A12,'C:\DOCUME~1\DSULLI~1\LOCALS~1\Temp\Temporary Directory 1 for GTO Black Book Feb 2013 Results_xlsx.zip\[BlackBook Download.xls]RR EI'!$B$9:$I$164,2,FALSE)/1000000

Strangely, the file does not exist on my machine but still I can see the result.
Not sure if this file is embedded inside the sheet?
Is there anyway I can open this file?

Note: If I change anything in the formula I get #NA which means the result is something to do with the file.

Hello,
I try to write a macro which will highlight the active row and column of the active cell.
I wrote a macro in an event in "This workbook" of Personal.xlsb file, but it work only on this file.
I would like the macro to work on any open file.
I look it up in Google, and understood that I have to use a class module.
I never use those so I don't know how to do it.
Can anyone help?
This is the code that I have used:

Private Sub Workbook_SelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False

Hello! I'm in need of some assistance. I have an excel spreadsheet that has numerous fields for data. One of the fields (column BJ) I need to set a limit to allow only one character of data and notify the user if they try to enter more. I’ve been able to set up a data validation rule that takes care of half of my problem. The other issue I’m facing is that some of the data is copied and pasted into the spreadsheet and some if it is data entered.

Dear Experts,
I'm truly beginner on spreadsheet, I want to make conditions on sheets, the worksheet background color should turns to red if the data on the sheet is uncompleted. If the data on the sheet is completed, the worksheet background color should turns to green.

I attach the example files regarding to my question.
Any responds from you would be appreciated.

Regards,
Saesaria

I have used some of VBA codes for importing data ranges from several workbooks but I failed to do the job I need.

I am using Excel 2010. I have around 2,000 workbooks with three worksheets each. They are identical in their formatting. Some of the cells include text, dates or amounts.

Hi. I need a code that does the following please. Copy and pastes a row from tab 1 (“April”) to tab 2 (“May”). However if there’s a ‘0’ in column H then I only want it to paste the row from column B onwards. If there’s a ‘1’ in column H, I need it to copy and paste the entire row. I’d also like it to paste in the row same on the 2nd tab. For example if its in row 5 on tab 1 I’d like it to appear in row 5 on tab 2. I’d like it also to appear automatically in the 2nd tab after inputting the number 0 or 1 into the column H. Hope this all makes sense.