Over 1 million tech questions and answers.

Create New Table Based on Cell Values

Q: Create New Table Based on Cell Values

I have a worksheet that contains a budget, some of the line items are identified as allowances in column C, by the "allow" designation. The description of the allowance is in column B.

I want to create an automatic table on a worksheet called "Allowances" which searches column C for "allow", and if found returns the description from column B in cell A and the amount of the allowance from column F, in cell B.

Any suggestions?


Read other answers
Preferred Solution: Create New Table Based on Cell Values

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)


I would like to populate an excel sheet with the values that are there in another adjoining sheet (The excel file is the same). The issue here is the data in the adjoining sheet table is pivoted. So what I want is:
Sheet 1 (Columns): Server_Name, Oracle - Category backup etc
Sheet 2 (Columns): Server_Name, Monitors only and the monitors here have different values for each server.

Sample for Sheet 2:

Here is an excerpt from the first pivoted data sheet. This is my sheet 2 (the pivoted table) and my first sheet is the same as above.
sDisplayName sMonitorTypeName sMonitorTypeDescription (Placeholder?)
AQUA3 AQUA3 - AOL Jobs Monitors the ETL jobs on AQUA3
RPC Service NT Service Monitor
SNMP Test availability of SNMP
AQUA4 Oracle - Category backup Active Script Monitor
Oracle - DataPump backup Active Script Monitor
Oracle - DBA account created Active Script Monitor
Oracle - Keep Cache Active Script Monitor
Oracle - locked accounts Active Script Monitor
Oracle - RMAN backup Active Script Monitor
OracleDBConsoleORCL NT Service Monitor
AQUAMARINE3 RPC Service NT Service Monitor
SNMP Test availability of SNMP
BASIL3 RPC Service NT Service Monitor
SNMP Test availability of SNMP
BERYLLIUM1 Beryllium 1 - Eon Incremental Monitors the ETL jobs on Beryllium 1
RPC Service NT Service Monitor
SNMP Test availability of SNMP
BERYLLIUM2 Oracle - DBA account created Active Script Monitor
Oracle - Keep Cache Active Script Monitor
Oracle - locked accounts Active Script Monitor
OracleDBConsoleCBORC... Read more

Read other answers

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub

All help is greatly appreciated!

A:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

Read other 16 answers


A pretty inexperienced VBA'er here.

I have 2 named ranges:

- LastStatus = "J8:J500"
- CurrentStatus = "K8:K500"

What I would like to do is create a loop to copy the cells in CurrentStatus over to the LastStatus column if and only if there is a value in CurrentStatus (column K). If there is no value in column K and there is a value in column J, I do not want change the value in column J.

Any help would be greatly appreciated.



A:Excel - Conditional Copy / Paste (Same Worksheet) based on cell values

You don't need a loop.

Sub test()
Range("LastStatus").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",RC[1],"""")"
Range("LastStatus").Value = Range("LastStatus").Value
End Sub

Read other 1 answers

I am trying to have a drop down box in a form determining in which table a new record will be added.
Here is a scenario:
I have three categories of inventory in our stock divided into three tables: “Office Equipment List”, “Medical Equipment List”, “Audio Materials List”. And the fields for each one of them are:
Table “Office Equipment List”: “Office equipment” field, “Allocated to” field, “Value” field

Table “Medical Equipment List”: ”Medical Equipment” field, “Allocated to” field, “Value” field

Table “Audio Materials List”: “Audio materials” field, “Allocated to” field, “Value” field

I have a form labeled “MAIN INVENTORY” with a drop down box storing all three of these categories. If I select “Medical Equipment” field from the drop down box, all the other fields in the form will be reflected on the “Medical Equipment List” table. If I select “Audio Materials” field on the drop down box, all the other fields that I fill out will be reflected on the “Audio Materials List” table. And it’s the same for the “Office Equipment List”.
Can anyone help me figure this out?
Thank you in advance!

Read other answers

It's difficult to describe my situation in words, so I'll just show you what I'm trying to do.

I have a table that looks like this:

A, B, C
Name, Hours, OT hours
Jane, 166, 0
Jack, 163, 17
Mike, 168, 0
Dot, 167, 24
Erin, 164, 8
Carmen 158,0

and I want to create a list that will have 2 rows if there are OT hours, and 1 if there are not.

A, B
Jane, 166
Jack, 163
Dot, 167
Dot, 24
Erin, 164
Carmen, 158

I can't use macros or VBA. It has to be formula driven. I can use a helper column or two. The starting table will change depending on who charged hours where. I've been trying to use IF and OFFSET and ROWS to do it, and it works until there's 2 consecutive rows that have OT hours.

Just to clarify, I just need to know how to get column A listed properly elsewhere in the spreadsheet - I can derive Column B using vlookup.

Thanks so much!

A:Solved: MS Excel: create list based on data in a simple table

2 Formulas - 1 for finding the name, the other for finding the appropriate value.
Note - must start on row 3 or greater.

Names (mine start in cell G3): =IF(G2<>"",IF(G1=G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1),IF(VLOOKUP(G2,$A$2:$C$7,3,FALSE)>0,G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1))),$A$2)

Hours (mine start in cell H3): =IF(G3=G2,VLOOKUP(G3,$A$2:$C$7,3,FALSE),VLOOKUP(G3,$A$2:$C$7,2,FALSE))

Read other 3 answers

I am hoping that someone can help me with what may be a complex question.

I have a spreadsheet, similar to the demonstration file I have attached. In it, there are a range of columns. One set of columns (A - D), shows a list of numbers that are references to items in a store, and it also shows their location in the store, so it acts as a floor-plan. The next column (E) lists those items again to show the dollar amount that those items have produced in total sales (F).

So, what I'd like to do is to add a conditional formatting to show me the "cold" and "hot" spots on the floor-plan so I can see where sales are most concentrated. I would assume that this would be done with a colour scale (using the range of values in column F to work itself out)?

Either way, the figures in column F would change weekly, so the relevant cells in columns A - D would need to automatically change when they're matching cells in column E - F change.

I hope this makes sense, as it's difficult to explain, but any help that someone could provide would be greatly appreciated.

A:Solved: Creating Cell "Cold" And "Hot" Spots Based On Values In Microsoft Excel 2007

Read other 16 answers

Hello Everyone,
(This is kind of long but I didn't know how else to explain everything)

There are two parts to my question below.

I have a spreadsheet with roughly 40 columns

An Example of some of the columns would be:


B1 = Client Name E1 = Travel F1 = Backup G1 = PC Install H1 = Data Recovery | X1 = Total | AE1 = Summary
B2 = Tony Smith E2 = $70.00 F2 = $0.00 G2 = $95.00 H2 = $0.00 | X2 = $165.00 | AE2 = (See Below)
B3 = Tom Daniel E3 = $70.00 F3 = $0.00 G3 = $0.00 H3 = $725.00 | X3 = $795.00 | AE3 = (See Below)
B4 = Rene Little E4 = $70.00 F4 = $125.00 G4 = $95.00 H4 = $995.00 | X4 = $1285.00 | AE4 = (See Below)

Currently this spreadsheet is being analyzed to be used to track field technician jobs and figure everything out for accounting.

I have been asked to please make a cell with a list of the information above so that accounting can just copy and paste from that cell into quickbooks in order to send these people out a receipt instead of having to retype everything into quickbooks. This list has to be based upon whether or not there is a value greater than 0 within that cell then it will grab both the heading cell in the first row and the value of that cell it was checking and place it as a line item in another cell.

I know how to do half of what they are asking and if I was dealing with 1 column I can do the other half.

I know how to list items in 1 cell using the c... Read more

A:Excel: Checking values on multiple cells and transferring values to 1 cell

Read other 16 answers

I have a sheet with 2 simple columns: Date and Price. I have imported the dates (##/##/####) and the prices ($###,###) by copy/pasting from the search results given to me by a niche database program I use. When the cells paste in, they all have the format "General".

When I try to format the "date" column into dates, it _does_ change the format as far as the cell is concerned, but the content of the cell doesn't adapt to the new format. For example, I have the date as 3/05/2001 and when I change it to a date format of MMM D, YYYY the content should change to March 5, 2001 but it doesn't. It is as if all the cells are forced to stay as text regardless of what the formatting is that I'm applying.

Same problem with the price column: if I change the format to include 2 decimal points, that format does apply to the cells, but the content of each cell remains without a decimal or anything following, as if the content is just text.

I have like 1000 rows in each column, and plan to do this analysis of the database's results frequently, so I'm hoping the answer isn't just to retype the data. There's got to be a way to copy/paste or export or something. Maybe I could copy/paste into notepad first to scrub out any formatting or locking from the niche database program?

A:Excel 2007 Cell Values Won't Take On Characteristics of Newly Applied Cell Format

Good news: Made some progress. In thinking that maybe each value had the textual single-quote forcing it to act like text, or maybe if I find/repaced all the dollar signs and commas that had been imported, I accidentally discovered that each and every value in my imported columns has a following space!

Bad news: Seems like Excel has a bug that thinks that if I say "Find=[singleSpace]" "Replace=[null]", then I should be given an error saying "Excel cannot find any data to replace". I think I'm doing the find/replace correctly because it worked on the dollar signs and commas.

Anybody know a workaround for the bug?

Read other 1 answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers


I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers


I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i

End Sub


A:Solved: Clearing Cell Contents Based off other Cell

try this variation;

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub

Read other 2 answers

Is there an easy way to only count instances where two columns have two different values?

Example: I want to count instances where Column A = "Test" AND Column B = "Evaluate". If either column is not equal to that value, it doesn't get counted, even if the other column is correct.

A:countif based on values of 2 columns

Read other 12 answers

(Don 't be intimidated by the length of this post; the essence is simple; I'm just a bit talky; I think it gives a clearer picture )

Hi guys. This is my first time here. I've been searching for almost an entire year now for a solution to this, and found nothing. I found your forum. Thank you so much for all the help you've given to people. I hope one of you can find a solution for this one.

I work for a huge multinational company with a massive number of employees. One of my tasks is to maintain a database containing the training certificates the employees are awarded. Regularly (a few times a daily), I get requests from different departments asking if the so and so employee has been awarded something/anything. They always send me excel sheets containing employee numbers and I have to compare their sheet to the master sheet I maintain.

Now here's the tricky part: I have two columns only, one for the certificate number, and the other for the employee numbers (dummy file attached). I put all the employee numbers in one cell, separated by space. Is there a way I can search for a value inside a cell, instead of just simply matching cells? This can easily be done using the search function, but often I get huge numbers of employee numbers, and using Ctrl+F in such cases consumes a ridiculous amount of time.

My question is easier to understand if you open the file.
Thanks in advance.

P.S. A secondary request is to suggest to me a better way... Read more

A:Solved: Tricky one: possible to look for a value in a **cell** with many values?

Read other 16 answers

I am using Excel 2000 and wish to show negative currency values in a cell as, say, (40.99) if the value is negative, but as 40.99 if positive - what cell format will do this? The cell still needs to retain its negative valus as it may be included in a summation.


A:cell formats for currency values

Look at the Excel command Format>Cell, Category: Number.
The selection you want should already be there.

Read other 2 answers

I am trying to figure out a formula for a specific cell that will:

Add all of the "1" values in a given range and multiply by 10
Add all of the "2" values in the same range and multiply by 9
... etc.... to add all the "10" values and multiply by 1 (or basically not multiply by anything)

Then add these new values together to come up with a final number.

Does anyone have some way to do this?

BTW, all of the "1", "2", etc... values are actually vlookup results. I don't know how that will mess up the formula.

A:Solved: Adding cell values

Read other 11 answers


I have the attached workbook where I want to count the number if instances of IEAC(CPI) from one row when the value of corresponding cell on another row is between 0 and 10%.

Range to count "IEAC(CPI)" from would be D2 to CA2 (default in my original workbook), based on the percentage in the range D1 to CA1. In cell C5 I would like the number of instances of IEAC(CPI) when the values of D1 to CA1 is between 0 and 0,1, in cell D5 I would like the same only for when D1 to CA1 is between 0,1 and 0,2.

Please, can somebody advise on how to write the function, or better yet, the macro?

Best regards

A:Solved: Count according to 2 different cell values


will do the less than or equal to 10% and IEAC(CPI)

can the percent be below 0%

if you need to be greater than = to 10% and 20%

Read other 2 answers


I built a large macro a while back that I need to reduce because with the recent additions I have made, I got "Procedure too large" as an error upon execution. I've already condensed most of my coding to something more elegant (I keep learning new things every time). Now I'd like to reduce the following code :

Do Until ActiveCell.Column = LastCol + 1
If ActiveCell.Value = "resRespondent" Or ActiveCell.Value = "ResLastCallDate" Or ActiveCell.Value = "LastCallStartTime" Or ActiveCell.Value = "resCallCount" _ ''And it goes on like this for many more values
ActiveCell.Offset(0, 1).Select
LastCol = LastCol - 1
End If

This is actually a big chunk of code and I have many more of these chunks in my macro. The problem is always having to repeat "Or ActiveCell.Value = " before each test. Something along the following idea would be great:

If ActiveCell.Value = "this", "that", "or this", "or that" Then


A:VBA - Test cell for multiple values

You can use SELECT CASE instead of IF, which allows such multiple values when comparing the same variable to many constants:

SELECT CASE ActiveCell.Value
CASE "resRespondent", "ResLastCallDate", "LastCallStartTime", "resCallCount"
ActiveCell.Offset(0, 1).Select
LastCol = LastCol - 1
Which is functionally equivalent to your example. Try it, as I've not actually tested it.

Read other 2 answers

Hi, I am using Access 2003 and I used wizard when I created the combo box by assigning the table and the field whose values are to be displayed in the box.
The values are displayed correctly on datasheet view.
But for some reason, when I selected Form View, the combo box is empty, any idea how to solve it?
Thanks in advance

A:Combo Box is not displaying values of table assigned

Read other 7 answers

Hey all, new here, I've been struggling with this for a little bit now, any help would be appreciated!

I have a bank statement(this is a mockup statement of course), and I want to create a function that searches through a list of words, finds rows matching those words and sums up the value in the column beside it. I was working with a combination of a SUM and a VLOOKUP but apparently vlookup only returns the first value it finds... so I'm at a dead end here. Heres what i've got so far...

On on G3 I have =SUM(VLOOKUP(A1:B19,G6:G10,2,TRUE)) trying to grab all those values below, search for them in the table specified (A1:B19) and return the sum of those values in column 2 of that table. Didn't work, so just trying only 1 value to search for in F3 I entered: =SUM(VLOOKUP(F6,A1:B19,2,TRUE)) just to try grabbing 'Metro' and searching for that, it gave me 6 for some reason... and now I'm stuck. Again any help would be appreciated! Thanks!
1---------------Grocceries-------Fast Food---------Gas----------Entertainment------------------------
2----Should be----79.5-------------25-------------31.5------------119------------------------------
5--------... Read more

A:Solved: Help Summing up similar values in a table!

Read other 8 answers

I have a series of numbers 1, 2, 3, 3, 3, 4, 5 that correspond to a customer inquiry in a given month...and if the number appears mulitple times, that means the customer called back against the same inquiry. I would like to count only the unique records for each month in a pivot.

A:Count unique values in Pivot table

Hi there, welcome to the board!

Without knowing anything about how your data is structured, or your Pivot Table, check out the GETPIVOTDATA() function. If your numbers are a certain field, you can use that as your criteria and you should be able to get unique counts.

Read other 2 answers


I have Dates in column A and values in B.
For some days(dates) i have 3 values, for some days i have 2 values and for others i have one value. (Example of my data given below)
1-Jan-2000 100
1-Jan-2000 150
1-Jan-2000 200
2-Jan-2000 400
2-Jan-2000 600
3-Jan-2000 150
6-Jan-2000 600
6-Jan-2000 650
6-Jan-2000 700
Now I want to Average the values on basis of date (regardless of the number of values/day) (required output should be as under)
1-Jan-2000 150
2-Jan-2000 500
3-Jan-2000 150
6-Jan-2000 650

Any idea how to do that?

A:Solved: Averaging values based on date

Read other 6 answers


I searched the forum but could not find what I am looking for.

Selection for row that contains either apple or banana
I have an excel (2007) with around 45k rows. I need to highlight entire rows based on the value in 'Item' column. If the row has either 'apple' or 'banana' in column A, the entire row should get highlighted.

Now i did try the conditional formatting -> new rule -> use a formula to determine which cells to format and it worked fine for a single value defined. However i want to input more than one value and all the rows with either of the values should get highlighted. is it possible? I have attached a sample file here.
Selection for row which contains 2014
Secondly, if the row has 2014 in one of the colums, say D (like in the sample sheet), the row should get highlighted. I am not using it with the above formatting i..e with apple/banana highlighting. This is for a separate task.

however i made both table in a single file (which is attached).

A:Solved: Highlight entire row based on 2 values

Read other 11 answers

I am Excel for my monthly budget. I have a check box in each cell that I check when the payment has been subtracted from my account. I want to be able to add the values of the checkbox cells to determine how much of my monthly bills have been paid so I can determine how much I actually have in my account.

The checkboxs are from the forms tool bar. For example
A1 $100
A2 $30
A3 $50
The $100 and $50 dollar payments have been subtracted from my account and I have put a checkbox in those cells. What can I use to add the amounts together if the checkboxes are checked?

I hope this isn't to confusing. Thanks for any help.


A:Adding values of a cell if a check box is checked


Read other 1 answers

Is it possible to use an IF statement to set the value of not only the cell the formula is in, but also other cells.

i.e. IF (G3="Yes" THEN Cell B3 = "xxx" and Cell D4="yyy" ELSE "False")

Or is there a way of doing this without the IF function, this would be very useful. Thanks.

A:Excel - Setting mulitple cell values with IF

Not unless you're "in" the other cells.. or using VBA. A formula in a cell cannot influence any other cell unless you're directly in that other cell (i.e. another formula).

Read other 1 answers

I am trying to add a series of cells that contain a mixture of positive and negative values.
Using =sum(b3:f3) is not providing the correct answer.
Can someone help please?

A:Solved: Excel addition of +ve & -ve cell values

Read other 6 answers

Is there a way to set the value of a cell to the name of that particular worksheet with a function???

sorry about the consecutive posts, i'm working on a project for work.

thanks for any help!

A:setting cell values to worksheet names


Try using the function =cell("filename") in any cell. It will return the complete path including the tab name of the spreadsheet.

I found this by going to help and looking up information functions.

Hope this helps.


Read other 3 answers

I have a formula that references values from a different worksheet. I am instead needing those values to actually be in there! Instead of just being referenced by a formula.

My formula is:
Click to expand...

A:EXCEL - Making values stick in a cell

Select the entire range of cells in question and then right and choose COPY. Then right click again and choose PASTE SPECIAL and then select VALUES as the paste type. FYI you can do this for a single cells, entire columns, or all the cells in the entire sheet at once.


Read other 1 answers

I have a function in a cell that needs to change the value of another cell depending on values of multiple other cells. What is the command to use within a function to change the value of another cell?

=IF((F1="No"),(need to set F3 to spaces),(IF(AND((F1="Yes"),(LEN(TRIM(F2))=0)),"N","Y")))

A:Need command for altering cell values within a function

"need to set F3 to spaces"

A function cannot change the content of a cell.

Read other 1 answers

Is there a formula where it will show the text within a range of data?
For example "a1" will show 1-10, "a2" will show 21-30.
a b c d e f g
Row1 0 1-10 0 0 0 0
Row2 0 0 21-30 0 0 0

A:Solved: Show values of non-blank cell

Read other 15 answers

I am having trouble doing the following:

I have an Excel workbook with two sheets. The first sheet contains two fields, one for first name and one for last name.

On the second sheet, I want to combine the two into one column called borrower name. Here is the formula I'm using:

='sheet1'!A1 & ", " & 'sheet2'!B1

this works on the same sheet as the source data (sheet1), but is ignored when on the second sheet.

Any ideas?

A:Combining cell values on different Excel sheets

HeavyBall said:

I am having trouble doing the following:

I have an Excel workbook with two sheets. The first sheet contains two fields, one for first name and one for last name.

On the second sheet, I want to combine the two into one column called borrower name. Here is the formula I'm using:

='sheet1'!A1 & ", " & 'sheet2'!B1

this works on the same sheet as the source data (sheet1), but is ignored when on the second sheet.

Any ideas?Click to expand...


If you are combining data from just one sheet then your second reference should be Sheet1 and not Sheet2 - or have I misunderstood?


Read other 1 answers

I have a list of forty cities that are to be ignored. I want to run a queury that gets all records from a table that are not in that list.


A:Access 200. Show all values where city is not in a lookup table.

Read other 8 answers

I need to count the unique values within a pivot table. For example, table columns are:

State, Customer,Month, Year, Sales Amount

I want to be able to find out how many Customers there are in each state. Since it does not count unique it counts every row that the customer comes up in I cannot do it within the Pivot table.

Any help would be appreciated!!

A:Excel Pivot Table - Count Unique Values

Does this help?

Read other 2 answers

I am looking to have an Age field in my table that automatically calculates the age using the Date of Birth field value. I tried putting DateDiff("yyyy",now(),[Date of Birth]) in the Default value of the age field but never worked. Any ideas?

A:Solved: Access Table fields default values

Ya I don't think you can do that. You need to try and use queries for you calculations, you bring the table into the query design and then add the calculated field. This query can then be used in forms and reports (with your new field).

I made up this expression, there could be shorter ways of doing it...but the way you had it, only gives you the Rounded Year value (the age they will become in the current year), which might be incorrect for what you are doing. When you are listing their AGE does it have to be exact?

eg if born Dec 31 1967, then when viewing their record on Jan 1 2008, they will appear to be 41 years old, but their birthday is not for another 364 days?

If you don't care then just use you expression in the query, otherwise use this one. I made it break down the Year and Months, but not days because it is variable (leap years).

Age: Int(DateDiff("m",[DOB],Now())/12) & " Year(s) " & (DateDiff("yyyy",[DOB],Now())*12)-DateDiff("m",[DOB],Now()) & " Month(s)"

Read other 3 answers

I am trying to use the following macro to copy cells from one worksheet to another.

My problem is that I only want to copy certain column values - is it possible to do this and how would I change the coding - I`m relatively new to programming macros!!

Sub Test2()
Dim PLimit As Long, ALimit As Long
Dim p As Long
PLimit = Sheets("Sales Enquiry").Cells(Rows.Count, 9).End(xlUp).Row
ALimit = Sheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row + 1
For p = PLimit To 1 Step -1
If Sheets("Sales Enquiry").Cells(p, 9) = "y" Then
Sheets("Sheet1").Range("A2:H2").Insert (xlDown)
Sheets("Sales Enquiry").Range("A" & p & ":H" & p).Copy
Sheets("Sheet1").Range("A2:H2").PasteSpecial (xlValues)
ALimit = ALimit + 1
End If
Next p
End Sub

Thanks for your help in advance,


A:Copying specific column values in a row based on a condition

Read other 15 answers


Got a few questions which tie in to what i am doing so i'll try to explain as best i can.

I want to be able to have excel automatically arrange the order in which it displays the information based on the value or information in a cell. Eg i have a list of things i sell online and I track their performance using the spreadsheet were i have a sheet for every product type which are linked to a master snapshop sheet which has the prices, weight and other info about the items. The product name on this main sheet is hyperlinked to the sheet containing the product and in turn the data in that sheet is formulated back to the main snapshot the aim being as little data input as possible to calculate everything.
I would like to be able to have the snapshop mainsheet automatically put for example the best performing product at the top of the list. At the moment a have to select the cell and expand the data range but this is erratic as i don't think i do it properly. Sometimes it messes all the data up. I am not that good with excel so i am probably going to be told why don't you do this or that etc but that is ok if anyone has an idea.
Anyone able to help?
Apologies if its difficult to follow but i will give more info if requested.
If someone has a link to a webpage that can help would be great also.


A:How do you Auto arrange cells in excel based on their values?

Read other 16 answers

I have a database that i'm creating that has 3 different tables. Its for safety violations at work. One table is the innitial one that has all the pasic information on it. I set it up so that it automatically assigns a number to each complaint filed. The other two are for follow up. One is for how the problem is going to be fixed and who is supposed to do it, And the other one is for verification to make sure that the problem has been fixed. My problem is that i want the two follow up tables to automatically import the complaint number when a new complaint is filed. I tried to set everything up myself, but had no luck. Can someone please help? Thanks.

A:Solved: update the column in one table based on the column of another table

Read other 16 answers

....A................. ........B ..........................C

1. AMDRG.SA ........AMDRG.CCM.......CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA';

I am not sure how good that is going to look once i post this, but here is a rundown of what it is.

Lets say those are 3 columns, A, B, and C. I would like to have a macro or script that will take what is in column B, which is also inside the text of column C, and change the text AMDRG.CCM to what is in column A(AMDRG.SA). So when i am done, the info in column C will look like this...
Column A and column B will always line up with eachother, but column C will not always line up right beside the cells associated with what the replacement text is.

Thanks for any help that you can give!

A:Multiple find and replace cell values in excel

Can you post a sample workbook with a few more records?


Read other 3 answers


I really could use come help on how to set up my Excel file so I can send automatic emails for when a project is past-due.

So basically If Column N is over 4 days I would like an email sent to the usernames in Column L and CC the names in column B.

In the subject line, it should have the info in column E

email message: Please complete your task for the CMT number mentioned above. This task is now past-due and at risk of cancelation. Please let me know if there is something I can do to assist to get this task completed by end of day.

Read other answers

I have some values in Excel that i would like to get into variables in Visual Basic. So I have values from cell A4 to A50, that will be one group. then Values from cell B4 to B50 tha will be another goup. I am using Visula Studio 2017 if it makes a difference. I want to use Visual Studio instead of Excel so i can turn it into and exe eventually. Can anyone tell me how to do this.


Read other answers

Good Afternoon All,

I am working on developing a template using Word 2013 for Forensic Engineering reports and I would appreciate a little advice. I am trying to use custom document properties throughout the report to ensure consistency across the document of certain key pieces of information such as site address, report author, issue date and so forth. What I am hoping to achieve is to have a document control table inside the front cover (see attached screenshot) which can be filled out by the author of a new report and then get Word to read the values from this table into custom properties which I can in turn insert as fields elsewhere in the document where this information is required. This may seem trivial but I have done hundreds of similar engineering reports and reviewed hundreds of others and the amount of times I have literally encountered hundreds of incidences where the author of a report has say changed the site address and job number on the cover sheet but forgotten to change it say on headers or footers. This is not only embarassing for the company but could potentially be breach of data protection laws in that I can send out a report to one client which has the name and address of a previous client still embedded within it somewhere.

I have already figured out how to define and edit custom property fields and I am aware that I can link their values to the document via bookmarks. However, this is not a fail safe approach as if a user selects, delete... Read more

Read other answers


I am having an issue with creating a Word 2010 formula that provides a count of the specific values found in the column of a table. The basic details are below.

The column may contain the following distinct values:
Off Duty
Limited Duty
Returned to Regular Work

I would like to create individual formulas (in three different cells) that count the number of times that each string occurs. For instance, the example output would be:

Off Duty: 5
Limited Duty: 7
Returned to Regular Work: 12

I understand how to handle this in Excel using the COUNTIF function. Unfortunately, I don't believe that Word has the same function.

I'm certain that it is easier than I'm making it...I appreciate any and all suggestions!

A:Count Specific Text Values in a Word Table Formula

I appreciate any and all suggestions!Click to expand...

Insert an Excel Worksheet Object?

Read other 1 answers

Hi everyone,

I'm using Access 2007 and I want to:

Populate a List Box with values based on the selection in a Combo Box
Then I want to be able to select a value from the List Box to find a record on a form.

The scenario here is that Jobs have multiple Items and I want to be able to see the details of an individual item by first selecting a Job then selecting the item from a list of all items in the selected job.

I'm fairly new to this and this is a big jump in complexity for me, I'm looking forward to hearing your responses.


A:Solved: Selectable List Box Values Based on Combo Box Selection

Read other 13 answers

So, this one has me stumped and the Internets is not revelatory.

I am trying to create a decision analysis tool in Excel. It's a typical design: one structure for criteria, another for proposals, a third to enumerate the alternatives, and a fourth for scoring.

It's fine for simple decisions where all proposals are mutually exclusive. I'm trying to incorporate the concept of dependency and partial exclusivity, though.

So, for example, on my proposals sheet, I've created a simple exclusion list that looks a bit like so:

[Proposals Sheet]
C1 C2 C3
x1 ...is incompatible with... y3
x2 ...is incompatible with... y2
x3 ...is incompatible with... y2

Where x1, x2, x3, y2, and y3 are all proposals being analyzed.

The alternatives are enumerated automatically using binary math, like so (just a sample extract):

[Alternatives Sheet]
x1 x2 x3 y1 y2 y3 | Alternative Code
0 0 0 0 0 0 | Do nothing
1 0 0 0 1 0 | x1, y2
0 1 1 0 1 0 | x2, x3, y2 ****
0 0 1 1 0 0 | x3, y1

The row with all those asterisks is mathematically considered an alternative but should not be considered due to the exclusion list--it includes both x2 and y2, which are incompatible.

I'm trying to find a worksheet formula that will check whether an incompatibility defined on the Proposa... Read more

Read other answers

OK... Here's something I have been looking at for a while and getting myself all in a tizz...!!!

I have 2 worksheets within 1 workbook - see attached example. The first contains unique references (ABx in Column A) with associated processes in Column B. Each cell may contain more than one process - comma seperated. The second sheet contains unique references (XYx in Column A) which map back to ABx references in Column B (again, each cell can contain multiple mapping references - comma seperated).

Ref - Process
AB1 - Create, Update
AB2 - Create, Read
AB3 - Delete, Update

Ref - Maps-to
XY1 - AB1, AB2
XY2 - AB1
XY3 - AB2

Now, what I am looking to do is within sheet 2 (containing the XY references mapped to AB references) is create a column which will display the corresponding processes without duplication.

Ref - Maps-to - Process
XY1 - AB1, AB2 - Create, Update, Read
*Note that AB1 and AB2 both contain Create which should only be returned once.

I have tried using =VLOOKUP(B2,'Reference Sheet'!A:B,2) but this only uses the first reference to return results and therefore using the above as an example the result would not include "Read".

Any takers? Hope someone can help...

Thanks in advance!

A:Solved: Excel: Cross Reference multiple values in cell

Read other 16 answers