Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Auto fill Column based on data in other columns

I have a report I'm downloading into Excel each day. Columns A, B, and C
have the same number of rows populated. Columns D and E don't. I want to
populate columns D and E with code the same number of rows Columns A, B and C
are. I want to auto fill Column D with the text "ABCD" and Column E would be
the current date in 06/05/06 format. I would like to put this code into an
existing Macro. Any help you can give me is appreciated.


Post your answer or comment

comments powered by Disqus
Hello,

I need to know how to auto-fill text based on text in another cell. For
example:

Every time I enter "CHS" in Column B, I want Charleston to auto-fill in
Column C.
And when I enter "SAN", in Column B, I want San Diego to auto-fill in Column
C.

How can I set up a list like this? Any ideas?

Thanks!

Jason

Hello,

I need to know how to auto-fill text based on text in another cell. For
example:

Every time I enter "CHS" in Column B, I want Charleston to auto-fill in
Column C.
And when I enter "SAN", in Column B, I want San Diego to auto-fill in Column
C.

How can I set up a list like this? Any ideas?

Thanks!

Jason

I have a report I'm downloading into Excel each day. Columns A, B, and C
have the same number of rows populated. Columns D and E don't. I want to
populate columns D and E with code the same number of rows Columns A, B and C
are. I want to auto fill Column D with the text "ABCD" and Column E would be
the current date in 06/05/06 format. I would like to put this code into an
existing Macro. Any help you can give me is appreciated.

I'm trying to make a formula within a cell that selects other cells based on cells in another column containing data, is this possible? An example below.

----A-----B
1 393 474
2 378 428
3 353 ----
4 336 ----

The final product would work like this.
B1:B2 have data, so I would need a cell formula to select A3:A4 automatically and sum them together. So, once I enter data into B3, A3 would automatically be taken out of the cell selection.

I have Excel 2010, thanks for any help!

I want to calculate the average of numbers in one column based on crieteria
in another colume.

If the value in column A = x, sum and average the corresponding values in
column B

How can I sum the total numbers contained in one column based on dates in
another column?
Example: I need the total of 5 units sold int the month of January 2006. I
have the sale ammounts in one column and the closing dates in another.
What forumla would I use for this?
Thanks in advance for any help.

Auto fill series based on Condition.(Excel 2003)

I know very little about macros (basically just concepts). I found this macro suggested in another thread (posted By VoG II…Thanks)

Sub test()
Dim Lastrow As Long
Lastrow = Range("A5").Value + 4
Range("B5").AutoFill Destination:=Range("B5:B" & Lastrow), Type:=xlFillSeries
End Sub

When this macro is run the first time it will fill in the numbers 2 through 15 (as I had hoped). Given 15 is the variable and 1 is the start of the range.

Sheet1

*ABC1VariableRange*2***3***4***5151*6*2*7*3*8*4*9*5*10*6*11*7*12*8*13*9*14*10*15*11*16*12*17*13*18*14*19*15*20***

Excel tables to the web >> Excel Jeanie HTML 4

The problem I have is that if the number 15 (in cell a5) is changed to a smaller value. If it was changed to say 11, and the macro is run again, the formula fills in the numbers 1 through 11, but the other numbers 12, 13, 14, 15 (that were generated from the first time running the macro, visable. Is there anyway to modify this macro to delete or blank out the cells that are generated from the first time the macro was run. Thank you!!

Sheet1

*ABC1VariableRange*2***3***4***5111*6*2*7*3*8*4*9*5*10*6*11*7*12*8*13*9*14*10*15*11*16*12Left over17*13Left over18*14Left over19*15Left over

Excel tables to the web >> Excel Jeanie HTML 4

Hello everybody again!! I am using excel 2007. Here is my latest problem.

I have a very large worksheet. Each row is a date from 1/1/1900 through the
present. I have about 600 columns.

What I need to do now is take a certain exisiting column, and then create a
new column based on the data in the old column. The old column has either a
1 or a -1 or a 2 in certain cells, based on various criteria. The new column
must look at the old one, and find the 1, or -1, or 2 and then count down
five rows, and put in a 1. (All dates are not necessarily in. most weekends
and holidays are missing, so I wouldn't really need something based on dates,
simply to count down five rows, and put in the 1) To make this easier, I
would copy the column to a different worksheet, and work with it there and
then insert the new column where I need it. Also, I can also take the
original copied column, and change the 1, or -1 or 2 all to the same thing,
in order to make the new column. I have to find cells that are not blank, and
then based on that, put in the 1 in the new column. Also, not always do I
need five rows down, sometimes I would need 3, or two or 10 or 30, etc.

Sample:

Date: old column new column should end up with:
1/2/1900 1
1/3/1900 1
1/6/1900
1/7/1900 1
1/8/1900 1
1/9/1900 1
1/10/1900 1 1
1/11/1900
1/12/1900 1
1/13/1900 1
1/14/1900
1/17/1900 1

--
newyorkjoy
thanks for the help!

I have an Excel file with two workbooks. I'm working with Excel 2003. I'd like to fill a field in one worksheet based on data in the other worksheet.

I've attached an example copy. In sheet 1 I have two critical fields - RO and Make. Based on the RO field, I want the Make field to be filled in by looking at the RO field in sheet 2, take the corresponding Make field and carry it over to Sheet 1.

Thanks for any help.

I've seen an archived response to a very similar question, and did a search, but couldn't find quite what I was looking for, so maybe a new question will help...

I'm trying to hide columns based on information in another column. If that column contains "A" or "B", hide columns "U" through "W". If it contains "A", "B", "C", or "D", hide columns "V" through "W". Etc, etc., so on and so forth...

I've snipped the code I found earlier and modified it to (what I thought) was correct for my application, but it doesn't seem to do anything. (btw, should I get an error if it doesn't run correctly?)

Here is the code as modified:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)





   If Target.Address = "P:P" Then


  


      If LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Then


         Columns("U:W").EntireColumn.Hidden = True


      ElseIf LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Or LCase(Target.Value) = "C" Or LCase(Target.Value) = "D"
Then


         Columns("V:W").EntireColumn.Hidden = True
         

      ElseIf LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Or LCase(Target.Value) = "C" Or LCase(Target.Value) = "D"
Or LCase(Target.Value) = "E" Or LCase(Target.Value) = "F" Then


         Columns("W:W").EntireColumn.Hidden = True


      Else


         Columns("U:W").EntireColumn.Hidden = False


      End If


      


   End If


   


End Sub
I'd appreciate any help the gurus here can offer.

Thanks!

Hi folks,

Here's my conundrum. I have three sets of data points, all referenced to time/date - but not precisely the SAME time/date. One set of points is recorded once each minute, one set is recorded when a temperature changes and a third set is recorded based on a change of state "0/1".

I need to make a line graph with all three sets of data lined up based on their timestamps. To do this, I need to be able to have the rows of my sheet lined up by timestamp.

So, what I have is something like this:
Code:
         A           B            C            D              E             F
1  8/7/07 0:31     135.5     8/7/07 0:31      78.8       8/7/07  0:31       0
2  8/7/07 0:32     137.6     8/7/07 0:35      79.0       8/7/07  0:37       1
3  8/7/07 0:33     138.2     8/7/07 0:39      79.1       8/7/07  0:41       0
4  8/7/07 0:33     139.5     8/7/07 0:42      79.4       8/7/07  0:44       1
5  8/7/07 0:34     139.5     8/7/07 0:51      79.5       8/7/07  0:46       0
...etc
and this is what I need to generate:
Code:
         A            B            C            D              E             F
1   8/7/07 0:31     135.5     8/7/07 0:31      78.8       8/7/07  0:31       0
2   8/7/07 0:32     137.6     
3   8/7/07 0:33     138.2     
4   8/7/07 0:33     139.5                                         
5   8/7/07 0:34     139.5     
6   8/7/07 0:35     139.9     8/7/07 0:35      79.0      
7   8/7/07 0:36     140.0
8   8/7/07 0:37     141.1                                 8/7/07  0:37       1
9   8/7/07 0:38     141.0
10  8/7/07 0:39     140.0     8/7/07 0:39      79.1
...etc
If it were only a few data points, doing this manually by dragging groups of cells and lining them up would be an option. I've got approximately 3500 data points in the first series, however, and almost that many in the second and third. I'm looking for an automated way to line up the rows of the second and third series so they match up with the appropriate rows of the first series based on the timestamp.

Thanks!
Ed

I am trying to populate "Column E" in "Tab_1" with data from "Tab_2".
These are two tabs in the spreadsheet.

Tab_1:
- Column C, has a value say 12345a.
- Column E is blank.

Tab_2:
- Column A has a list which contains 12345a
- Column B has another value, one for each item in Column A

I want Column E in Tab_1 to be populated with the value in Tab_2
Column B where Tab_1:Column C matches Tab_2:Column A. There will be
only one match as each value in Tab_2 Column A is unique. Was
thinking a Macro could be pasted down Column E in Tab_1 to facilitate
this, but wasn't sure how to code it. Any suggestions?

Thanks.

JR

I asked this before but my explanation was labored and the answer didn't
address my needs. Here is a fuller, and I hope, clearer explanation. How do
add the figures in one or more columns based on criteria in more than one
column? For example assume the following worksheet:

A B C D E

1 aaa n n n
2 bbb zzz n n n
3 aaa yyy n n n
4 ccc xxx n n n
5 aaa www n n n
6 bbb n n n
7 bbb zzz n n n
8 ccc n n n

The real worksheet has hundreds of rows. I would like to add up the n's in
the rows that have a specific criteria, e.g. aaa in Column A and bull
(blanks) in Column B. I would also like to add up the n's in the rows that
have a specific criteria in Column A and anything in Column B (not null or
blank), e.g. aaa and yyy with the aaa and www. There are too many different
strings in Column B to use specific criteria. Any help would br greatly
appreciated. Setts

Hello everyone...

Please look at the attached sheet. I need to populate column C with data from column G, based on a comparison of column F to column B.

For example, wherever there's a "100" in column B, the next cell to the right in column C should be populated with "Fail", and so on.

Columns F and G are my "key" or "legend".

My real world example is 2000 rows long so I'm looking for an automated way to do this. Can this be achieved without writing code? I think I might have done this once with a pivot table but if I did, I can't remember how to do it. Any help would be great!

Thanks a million in advance!

Peter

Hi

I want to do a count of unique occurrences of data in one column based on criteria in another column.

I have attached a sample worksheet with the data. I want to get the result in cell I26.

Thanks.

ltsolis

I'm trying to sum the number in one column based on criteria in another
column, but there are also multiple columns on the spreadsheet.

1 5 2 5 1.5 7 2 7

If the second number is a 5, I want the sum of the first numbers. Likewise
for the 7.

Thanks,
Scott

I need to count the occurrences of a value in one column based on the value in the corresponding row in another column?? Seems I've done this and can't remember. =countif($a$1:$a$20,"V", $b$1:$B$20,"df")?????

Thanks, Mike

Good day,
I am trying to build a spreadsheet that tracks my aircrew's flight currencies.
On the General tab the last column (N) I am trying to somehow indicate to my personnel that if anything is red in columns B-M then column N will also be red (or some other indicator).
If all the other columns are uncolored then the box to the right in column N is green (or some other indicator).

I have attached my spreadsheet and any help with this problem would be appreciated.

So my problem:

in one column i have values and they should be formatted based on the value in that cell but the top and down values of the rule should be based on value in other cell (categ1, categ2, categ3) and the category can be changed manually (cell value which decides the top and bottom values)

For example:

value in cell A1 is 900€ and in cell B1 is category1 (<500 red, >1000 green so so this should be yellow). BUT if i change manually the category in B1 to kat2 (where <1000 red...) the conditional formatting should now change the cell red because the category value in B1 changed.

Can this be done in excel2010 and how?

I have been struggling with a macro for my excel worksheet. I need a macro that will delete rows based on criteria in two columns. Specifically....

EXAMPLE (note row 1 is column headings)

ITEM CODE = COLUMN A
DESC = B
LOCATION = C
STATUS = D

I need to delete all rows in which the value of column C = "ASSEM"
I ALSO need to delete all rows in which the value of column D = "D" or "M"

I would really appreciate any help, thx.

I desperately need help here. I have a spreadsheet that has sales price in
one column and the date in another column. It will only add if the date is
the month only i.e., January vs January 1, 2005. I need it to add by month
based on all days within the month because that column has to have the actual
date not just the month.

Can someone help me to with this formula?

I have 3 columns. Two of them have dollar amounts and the third one a date.
How to I add the 2 columns based on the date in the third column without
counting blank fields.

A 4,228.50
B 295.00
C January 12, 2005

I need to do something that is much like a pivot table would normally do. I have a spreadsheet in which one of the columns is 'sales dollars'. I need to get a total of the numbers in that column based on criteria in other columns.

Lets see if I can make that any clearer. Columns in my SS are Month, location, sales rep, as well as others. How can I total all sales dollar for 'x' sales rep, from 'y' location, in the month of 'z'? Is there a formula that can be set up with some sort of filter it? An 'If' statement or something else?

I am trying to create a summary worksheet for a 28000 line spreadsheet. The spreadsheet is added to every week by an automated download, so it gets bigger each week until the end of the year, when another one is started. I have created another worksheet as a summary page, and I want to enter formulas in that summary page to pull the data and total it. A pivot table will not work for me, because I need the data to be in specific cells on my summary worksheet, not in a table format. Each week, this summary page is used to compare to another spreadsheet. I currently create a pivot table from my downloaded spreadsheet and have to manually enter all the data from the pivot table into the summary. The data is always changing, even though this is Sept, the data for Feb may change due to the new download this week. With 35 locations, 230 sales reps, and 12 months, I have a lot of manual data to enter off the pivot table and it takes several hours each week. I would like to see if this can be automated by the use of some formulas/programming.

I just thought of another possible solution. Is there a way to lock a pivot table, so the selected information will be in a specific cell each time?

Any help is appreciated.

Hi all,

Could you please help me with the macro for this task, see attached fil.

I want to copy the values in column B based on the value of column C (1, 2, and 3) into three separated columns in another sheet (i.e., Red = 1, Yellow = 2, and Green = 3).

Thank you very much.

Best,
t


No luck finding an answer? You could always try Google.