Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [microsoft-excel-2007]

Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft. Use this tag for problems specific to Excel 2007 for Windows.

0
votes
3answers
58 views

Sum rows based on criteria in columns

I'm stuck with a problem, I have two sets of data like below: How can I find if the sums of column "a" based on ID in Table 1 are equal to column "b" based on ID? I'm assuming i need a Sumif and ...
0
votes
0answers
13 views

Incorrect pivot sums in excel pivot table

my data source in around 10K rows. I build a pivot and see that totals in pivot are 95.66 points different from totals from table (=SUM()): 1,161,401 (source) vs 1,161,497(pivot). When I double click ...
-1
votes
1answer
39 views

What is Excel #N/A error abbreviation, looking for?

In MS-Excel we have below functions return errors: #N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM! I think about what is the complete error massage, which each one are represents? Of ...
0
votes
2answers
88 views

Keep only one copy of the duplicated data

I am trying to clear out a list of content that appears 2,3,4 or more times repeatedly. I have two columns and I would like to eliminate the whole row of any repeatedly entry of column B. My ...
1
vote
1answer
44 views

Cell conditional formatting, based on the format of the same row's cells of another column

I've highlighted my duplicate text in column B, using Conditional Formating. No I want to highlight the same field on column A (and afterwards delete all non highlighted texts), but I am not sure how ...
0
votes
0answers
18 views

Include non-existent data elements in pivot chart WITHOUT dummy data

This is similar to this question here: Excel Pivot chart - displaying values not in the source data However, that answer suggests to provide dummy data which I do not want to do due to the large set ...
0
votes
0answers
17 views

How to change a color of text in a column based off of another column's text?

I'm building a Excel spreadsheet for my expenses. And I'm doing a copy and paste method from my online banking expenses. The trouble I'm having is based off a column, named "Payment", it shows the ...
0
votes
1answer
26 views

How can I create an automatic calorie counter in excel with a reference database?

I have a small database of nutritional facts of the foods I eat on a daily basis. Right now, to calculate daily intake, I put the information in individually for each unit of food consumed. I'm ...
0
votes
1answer
42 views

Older version of Excel will not open XLSX files

I've been using an old version of Office 97 (with Macros disabled for security) for some time. Recently I got a new Windows 10 v1803 PC and re-installed Office 97. With the Office 2007 Compatibility ...
0
votes
1answer
21 views

Power Query with Additional Column

I am using a power query to read rows indexes by an item number from one file I receive to one file I am maintaining. I need to add some columns that are editable by me (cell editing), in a way that ...
0
votes
1answer
27 views

Keeping a fixed constant (like letter “M”) and a variable (like a number “100”) in the same cell in excel but I only need to type the variable?

In excel, is there a method where a constant term can be fixed in the cell and I only need to input the variable term. (this is difficult to put in words) example: So, I have different values, like ...
0
votes
0answers
39 views

Splitting Workbook using filter

Suppose I have Workbook(Report.xlsx) and in that workbook I have different worksheet. In every sheet I have a column name B_Code. Using filter in B_Code I want a separate workbook with every sheet in ...
-1
votes
1answer
66 views

Excel VBA - Returning a list of values not found in one column

I want to get Excel VBA to return, in a messagebox prompt, all items which are in column A of worksheet Mastersheet but are not in column A of worksheet DeliverySheet. Having a lot of trouble ...
1
vote
1answer
35 views

Combining every 15 rows into one across numerous rows

I have data spreading out in 45000 rows (single column). I need to combine every 15 rows into single row. So in the end I need to have 3000 rows with combined data from every 15 rows. from: row 1 ...
0
votes
1answer
64 views

Excel - Compare the value of two fields and change color depending on results

I am trying to compare two fields in an Excel Spreadsheet and if a number is higher then change the background of that cell and if it is lower change it also. For a further explanation I have created ...
0
votes
1answer
141 views

Change loop to write results in additional cells

I have been using the following code for awhile but need some help in making one change. I’ve tried and tried but it won’t work. If someone could kindly take a look, my research says I’m looking for a ...
1
vote
1answer
38 views

Subtraction + modified result if negative

How do I set up a formula which will subtract one cell from another (ex: B2-B3) and return the usual result, UNLESS the result is negative (basically < zero), in which case I need it to return "$0....
0
votes
1answer
34 views

Formula to ignore or treat as empty a cell that contains another formula with no result?

I'm using Excel 2007 I have a Cell B14 which contains a formula getting data from a different worksheet and sometimes this formula may have no result. I need a formula to ignore Cell B14 or treat it ...
0
votes
1answer
62 views

How to multiply a row of values to a table with the same number of fields?

I have a row of value from A2:H2 and then I have a table of values from B7:E8 (both the table and row have 8 data points). How do I go about multiplying them? Please see the bellow illustration: +---...
0
votes
1answer
41 views

How to reference a shape by name and what else can be used in .Select?

I use MS Excel 2007 My question is in two parts I have a form option button called "Option1" How do I refer to that button in other macros? I know that I can select it using: ActiveSheet.Shapes("...
0
votes
2answers
87 views

Tell a macro to check if object exists and if yes do A. but if no then do B.?

I use MS Excel 2007. I have the following code in a macro that creates two Option Buttons (not active X) into Cell B25. Range("B25").Select ActiveSheet.OptionButtons.Add(129.75, 540, 24, 20.25)....
0
votes
1answer
36 views

Macro/VBA Code to List and print names and code of all macros in a workbook

I am using Excel 2007 For this question the name of my workbook is PrintCode.xlsm Is there a Macro or VBA Code that will print all of the macro names and code within the open workbook? I have found ...
0
votes
1answer
36 views

Exactly position new option button in VBA

Is there a way of exactly positioning and sizing a new option button in VBA or a macro? The code I am using inserts a new Option button in which ever cell is currently selected within a worksheet. ...
1
vote
1answer
87 views

Excel 2007 - Can't combine these three formulas into one cell

I have three separate formulas each in it's own cell. Each formula has multiple criteria looking at data within a workbook. The way the formulas work is that only one should produce a result and ...
0
votes
2answers
53 views

Microsoft Excel 2007 - Formula To Populate Cell C1 based on Cell A1 Without A List?

I am trying to write a formula make excel place data in a cell based on the contents of a different cell BUT the formula cannot be in the cell where excel places the data. If we take the below simple ...
0
votes
2answers
60 views

Is there a quick way to do a common date-time format in Excel?

Can someone please help me find, built into Excel (any version) a ready-made format for Date/Time like: MM/dd/yyyy hh:mm AM/PM I can't imagine this is not a common format? 10/05/2018 05:03 PM I like ...
1
vote
1answer
53 views

Speed up Excel Index Match - First Instance

I'm using the below formula to look up the first instance of a name appearing in an email (Column L). I have around 40,000 rows and the formula is getting slower and slower as new rows (emails) come ...
0
votes
1answer
71 views

Replacement for 'sum_range' in SUMIFS formula that can match automatically with source data header

Need your help, through SUMIFS formula, I need the result to be like the one in D5 column. From the formula box in picture, we can see that I wanted to know the ad cost between July 6 to July 8 which ...
0
votes
1answer
26 views

compare 2 values within 2 ranges

I have 2 columns with both positive and negative values. Let the columns be A and B. I need to find a condition that matches both values of A and B. i.e. if -3.485 < a < +2.1254 value and -4....
1
vote
2answers
84 views

Filtering Excel Data by Column Value and Saving Columns to Individual Files

Many years back we had to come up with a solution to survey results that we were getting via CSV. Back then we'd get data where the first column was e-mails, and subsequent columns were 1's or null ...
0
votes
1answer
66 views

Excel: Is it possible to bind cells in a row together based on value?

I have recently used this solution to create a dynamically generated list of uniques. What it does is it takes cell values (here: list of names) and generates another list without repetition. Let's ...
0
votes
1answer
48 views

How to keep references unchanged after sorting

In the following spreadsheet, the references will change after sorting and break the calculations. Absolute referencing is not an option. The table has to be copied periodically to another sheet and ...
2
votes
2answers
44 views

Finding specific seconds in a cell with time

I have a column of times in hh:mm:ss format. I need to identify each cell in the column that ends in :01 (XX:XX:01). With Excel's time function, a =right(cell, 3) search doesn't find them. Any ...
3
votes
1answer
65 views

Trying to use the dates on the first column and have them separated into month, day and year in another column

Trying to use the dates on the first column and have them separated into Month, Day and Year in another column. The dates on the first column has random blank spaces in between and on the results ...
1
vote
1answer
46 views

Excel MIN with nested IF statement — Trying to ID a date closest to 30 days after a date

I have 16K plus records with an Auction Date. I need to find the date and amount that is closest to the Auction Date BUT more than 30 days later. I have calculated a row that shows the difference ...
1
vote
3answers
714 views

How to fix Excel and prevent it from converting copy-pasted or imported strings to dates even when cells are formatted as text

Every time I copy-paste some numerical data written as fractions or separated by hyhens into Excel, it always converts numbers that could possibly be dates into dates. There's no way of getting them ...
0
votes
0answers
101 views

Excel - How do I change the color of a data bar based on its value

I have a column in excel that has been formatted to display a progress bar based on the value in the cell 0 - 100. I did this by going to: Home > Conditional formatting > Data bars > More Rules: ...
-1
votes
3answers
84 views

Excel Formula IF/AND with MIN Calculating Due Dates

I'm creating a spreadsheet for payment plans. I am trying to create a formula to calculate the due date based on the $ amount and enrollment date. I want the formula to calculate for different tiers ...
0
votes
2answers
32 views

Excel offset from two different workbook, create a copy of one without affecting the other

I am using two different workbooks the first one [Excel Data only] created as a databse and the second one [Invoice Template] created to create an invoice. When creating an invoice i am using offset ...
0
votes
3answers
197 views

Excel Formula to Select only items from a list which are within a limit based on selected criteria

We are working with a table of hundreds of projects, with a total value well above our budget. In order to prioritise the projects, we have decided to select the oldest projects first, selecting all ...
0
votes
1answer
62 views

Is there a way to link workbook data without getting REF errors when a row is deleted?

I currently have two workbooks, workbook 1 and workbook 2. Workbook 1 - Sheet 1 is directly linking data to Workbook 2 - sheet 1. I am linking by inputting the below formula into workbook 2. ='[...
1
vote
2answers
107 views

Excel Formula Assistance

this is my first excel post ever so I hope you can help me on this. I'm stuck trying to figure out how to do this formula. In here, I have Form Input where the start date, end date and outlet are ...
0
votes
1answer
75 views

Using formulas in Excel 2007

I'm having a problem which I can't solve. I want to count number of occurrences of multiple cells fulfilling a given criteria. Its something like this- Da Ex Av Da Ex Av Da Ex Av 80 55 ...
0
votes
1answer
32 views

Copying cell from one sheet to another without formatting

probably a simple one to your geniuses however I need a fresh pair of eyes as I've been on this a while and can't see my error. I'm merely wanting to copy the contents of a cell from one cell to ...
0
votes
1answer
49 views

Copying only certain cells from a row to another worksheet

I have a workbook that has a large number of sheets designed to help my team manage their workload more efficiently. When someone requests a job to be done, a row is added to a sheet called 'pending ...
0
votes
1answer
39 views

Input data from one workbook form into another workbooks worksheet

What I am trying to achieve: I have one workbook with an application form to request additional training. A person fills out all the cells on the form and clicks submit. I then want all the data ...
1
vote
2answers
69 views

Separating comma delimited data from one cell to multiple cells when the entry is a live data link

My Excel model imports live data from some trading software. If I insert the following formula, I get the details of current tender offers, which are constantly changing: =RTD("rit2.rtd",, "...
1
vote
1answer
72 views

How do I conditionally format all rows in a table, to display a different colour based on a cells value

I have developed a table (as seen below) which is designed to show a persons duties based on priority. If a person clicks the toggle on/off button, this will toggle colours on the rows of the table, ...
0
votes
1answer
52 views

Extract distinct row values from a sheet to another sheet based on max dates per unique rows

I want to extract rows based on max date or max B per unique values of col A and col c. Doesn't matter if it is vba or excel formula. Help please. Old Sheet New Sheet [2
1
vote
2answers
63 views

Is there a way to use excel's VLOOKUP with multiple criteria?

I'm attempting to create an aggregate of data that looks at a list with a lot of duplication of items and compiles a total number that the duplication appears, and a unique value associated with the ...