# 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

**3**answers

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

**0**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**3**answers

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

**0**answers

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

**3**answers

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

**2**answers

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

**3**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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 ...