LOOKUP And IF - Generate Formula And Replicate It Through All The Cells
Feb 28, 2013
Actually i can do it just by using LOOKUP formula but the mark scheme says LOOKUP and IF.
If i use Just LOOKUP i should do the same thing for other cells but i want to generate a formula and replicate it through all the cells.
Here is the question;
Enter formulae in cells C2:C10 that refer to Book Orders.CSV and display whether the Text Book is Required by the student whose Student id is entered in cell A2.
I also uploaded the source file. Q3.xlsx
View 2 Replies
ADVERTISEMENT
Feb 11, 2009
If I need to replicate a formula in some cells, I usually drag it down or sideways. Is there any easier way to do this? I have large numbers of cells into which to drag the formula. Can I specify which cell range the formula is applicable to (i.e. A1:A10,000 or something) instead of dragging? If so how?
View 5 Replies
View Related
Oct 18, 2007
How can I continue to replicate this formula automatically?
Interest RateAmountM1M3Formula Required
1%10000010001000=B2*C2
2%10000020001000=B2*C3
3%10000030001000=B2*C4
4%10000040002000=B3*C5
5%10000050002000=B3*C6
6%10000060002000=B3*C7
7%10000070003000=B4*C8
8%10000080003000=B4*C9
9%10000090003000=B4*C10
10%100000100004000=B5*C11
11%100000110004000=B5*C12
View 9 Replies
View Related
May 3, 2007
I have two formulas which I'm using: =180*(120.5-1)/119.5 and =90*(60.5-1)/59.5
In the first example, I want to replicate the formula across row 1, but with the value '1' incrementing by 1 each time, upto 240.
In the second example I want the '1' value to also increase by 1 for each cell down column B, up to 120. when I try dragging the formula down or across, I am only able to replicate the formula exactly as in the initial cell (ie. the '1' doesn't change). I've tried using the $ symbol, but this doesn't work. I don't fancy doing this manually for a total of 360 cells!
View 5 Replies
View Related
Jun 17, 2014
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
View 3 Replies
View Related
Mar 28, 2008
we have salespeople in all 50 states and each state uses different rules for business days. Some states include Saturday as a business day, some don't, and Alaska uses 5 business days including Saturdays. We are open 7 days a week.
What I am trying to create is a worksheet that has each day of the month going down and in the cells next to it, a column for states that include Saturdays as business days; so a date 3 business days out would appear in the next cell. In the cell after, a date 3 business days out not including Saturdays. And in the last cell for Alaska, a date 5 business days out including Saturday. Of course federal holidays are all excluded.
Something like this:
March w/ Sat w/o Sat Alaska
1 5 5 7
2 5 5 7
3 6 6 8
4 7 7 10
5 8 10 11
Now Vermont has extra holidays certain months so I would have to adjust that in the formula for the middle column during those months.
View 9 Replies
View Related
Feb 15, 2009
I have a workbook with ten sheets. The front sheet is a summary with nine divisional data sheets (DDS) following. In every sheet cell G12 has the date. At the moment the DDS reference the summary sheet and pick up the same date from it. Is it possible to set it up so that if I change the date in cell G12 on any of the sheets I can get them to all read the same?
View 11 Replies
View Related
May 30, 2007
I'm exploring a different approach to my current project.
Sheet1, CellA1 contains the year ie. 2007
Sheet1, CellA2 contains the storage directory ie. E:archiveyear
Sheet1, CellA3:A10 contains the names of the employees.
Here is what I would like to make happen.
Running this in a macro, the result would be a copy of Workbook2 renamed for each employee's name in cells A3 through A10 and all saved in a newly created directory of
E:archiveyear2007employee name1.xls
E:archiveyear2007employee name2.xls and so on..
View 9 Replies
View Related
Aug 15, 2012
I have a userform that has nested multipages (5 in the outer page, 4 in the inner page).
On each of these multipages, I want to have the same controls (sliders) laid out in the same order - but with unique names, named after their tab location, for each control so I can use their value property later in the code.
I've designed the layout and named all the controls on my first sheet (e.g. Slider1Outer1Inner1). I now need a way of automating the replication of these across the other 19 sheets (including the nested inner multipage!); so that equivalent slider for example would be called Slider1Outer1Inner2, Slider1Outer1Inner3 etc.
I'm not sure about coding VBA to act on items within VBA...
VB:
" For each multipage in outer
For Each multipage In inner
For Each Object In current multipage
Copy inner.object -> Next multipage
Inner.object.name = CurrentOuter & CurrentInner & CurrentSlider "
View 2 Replies
View Related
Jul 24, 2007
if i create a record in 'XYZ' worksheet, it should create the same record in TARGET worksheet.
Likewise, if i create a record in 'ABC' worksheet in the same workbook, it should create the same record in the SUBSEQUENT ROW of the TARGET worksheet.
For this to happen, it must be done programmitically either using a MACRO or VB Script.
View 9 Replies
View Related
Dec 16, 2009
I understand the end(x1Down), but i think I am missing something. I just want to go to the next cell left, right or Up, Down from whatever cell is currently selected. How do I replicate a 1 cell move as an arrow key would do?
View 9 Replies
View Related
Jan 9, 2014
Creating a formula that would generate a perfect and full circle - see attached picture example. The circle needs to remain centered in the X,Y axis. Also the circle diameter will either be 1, 2, 3, or 4 units large depending on user selection.
View 1 Replies
View Related
Sep 3, 2009
The code below is supposed to find blank cells in column E on sheet3, copy some data from adjacent cells, create a string from that data and paste on sheet4 in Column J. I get duplicate data and or most blank cells? What the heck did I write.
View 4 Replies
View Related
May 7, 2007
I am trying to make a more useful excel extracted report – what I have to start with is muck like the attached sample file. With at the bottom is what I would like to be auto done.
There is a column of names – with most names repeating so the first thing needed is to create a new summary list – can be in the same worksheet or in a new worksheet. The new list with be just a list of each name but only display each name once rather that the multiple of times as source list.
The second requirement is than once have a list of each name that appears then in columns next to each a tally /count of each non blank cell under each respective column heading - now it has to be a count of non blank cells as the content of each cell will chance – now need to understand it is not a count of numerals as such but a count of non blank cells so it is not 1 + 3 + 5 = 9 but should be 1 +3 + 5 = 3 (3 non blanks).
A idea of what I want is at bottom of sample worksheet attached
View 9 Replies
View Related
Dec 15, 2007
I want to create a list from a "seed" word that consists of a word followed by a number (ex: seedword100).
What I want to do is to be able to create a list in Excel using that seed word and then have excel produce a bunch of replications of that seed word by incrementing the number part of the seed word by 1 for every replication.
So, for example, if cell A1 = "seedword100", I want Excel to replicate it up to "seedword199" like this...
B1 = seedword101
C1 = seedword102
D1 = seedword103
E1 = seedword104
F1 = seedword105
G1 = seedword106
H1 = seedword107 ...
and so on until the last cell = seedword199 (or whatever number I want it so stop at).
View 7 Replies
View Related
Jun 14, 2013
So I have a need to enter multi-line notes. Using a merged cell not working for me so thinking best to use a Textbox.
This works fine for entering the notes but I need to replicate the content on another Tab in the same Excel workbook.
So lets say I have named my Texboxes 'Textbox1' on one tab, and 'Textbox2' on another tab...... how do I replicate the contents?
I've tried putting =Textbox1 into 'Textbox2' like you would for a normal Cell but that does not work.
View 5 Replies
View Related
Jun 16, 2009
I created a UserForm to populate client billing info and job location info (so it's a two-parter). A clicked button will then populate a spreadsheet with all of the information. That part works fine.
When the client billing info is the same as their job info, I'd like to offer a checkbox that will populate the job info with the billing info rather than having to type everything twice (like you see on online billing and shipping orders).
View 5 Replies
View Related
Apr 21, 2007
I am trying to rebuilt a chartsheet without using copy/paste. I have chartsheets with 2 or more charts on it. I add a new chartsheet, then add a new chartobject and then I want to copy the same series ( name, xyvalues, values). Unfortunately it doesn't work.
Sub ReplicateCharts()
Dim Cht As Chart
Dim NewCht As Chart
Dim ChtObj, NewChtobj As ChartObject
Dim SrSerie As Series
Dim ns As Series
Dim x As Variant
If ActiveWorkbook.Charts.Count > 0 Then
For Each Cht In .Charts
Set NewCht = .Charts.Add()
NewCht.PageSetup.Orientation = Cht.PageSetup.Orientation
For Each ChtObj In Cht.ChartObjects...........................
View 8 Replies
View Related
Oct 12, 2009
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies
View Related
Mar 21, 2014
I'd like to generate a total sum based on the quantity from each item. So columns H3 - N3 should generate a total in P3 (skipped O) by the quantity entered in each cell. In addition I'd like the value of D3 to change the pricing in the formula. D3 has a drop down with 3 products, so selecting ANY of those products should cancel out the previous formula and replace it with another.
This is the sum without a value in D3
=SUM(H3*10.00)+(I3*8.00)+(J3*6.00)+(K3*18.00)+(L3*10.00)+(M3*8.00)+(N3*8.00)
IF D3 has a package selected from the drop down, I'd like this sum to generate instead.
=SUM(H3*8.00)+(I3*6.00)+(J3*4.00)+(K3*15.00)+(L3*8.00)+(M3*6.00)+(N3*6.00)
[code]....
View 3 Replies
View Related
Jul 16, 2014
Is there anyway in VBA to show the key sequence Alt+Shift+F10 then c?
Trying to find an easier way to convert text to number rather than having to use the Error Box method all the time as several large worksheets.
View 5 Replies
View Related
Feb 29, 2008
I have a single sheet broken into multiple sections of 10 rows (see attached). The first 5 rows of the section are formatted and I need to Insert 5 rows underneath and format them the same. I can't work out how to do this for the different sections (ie: add 5 rows to section 1, then section 2 then 3). These functions will be on separate buttons to use multiple times. I would have used multiple sheets but the project calls for a single sheet representation.
View 4 Replies
View Related
Feb 27, 2009
I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file. The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet.
Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up.
The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date. There are explanations for each of these on the worksheets for quick referral.
View 2 Replies
View Related
Mar 28, 2014
Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
View 3 Replies
View Related
Mar 6, 2012
I have a workbook that has 30 tabs in it. Each tab is a report card for students. What I'd like to do is create another tab with a button on it that when I hit the button it will search through each tabs range of D12:D40, D48:D76, D84:D112, D120:D136, J12:J40, J48:J76, J84:J112, and J120:J136. And if any of these cells has an MS in them then this new sheet I have created will list each students name which is in cell E5 and list what they recieved the MS for. This will be in the same row number but in column B. So if cell D12 has an MS in it then this report will list the students name and what's in cell B12.
Windows XP
Excel 2003
View 3 Replies
View Related
Mar 29, 2007
I have built conditional formatting into some cells on ' Sheet 1' and would like a message box (saying "Check errors before closing") to appear when the user tries to close the workbook if any of these cells are coloured red (ColourIndex 3).
View 3 Replies
View Related
Oct 13, 2013
I've got this relatively simple file I use to calculate some values for a game mod. Its currently using ad-hoc excel functions, but its maths related to aerodynamics and they get unreadable quickly. I'd like to move to VBA functions for ease of maintenance, and programming the maths part of the code has been no issue so far.
However, I don't understand how to replicate the functionality of the default maths functions regarding range inputs.
Simple example, one of the intermediate values is the Aspect Ratio of the wing:
Code:
=ROUND(2 * Semi_Span / Mean_Aerodynamic_Chord, 3)
This works fine with both numeric inputs, cell references, or, as shown here, defined range names.
However, my VBA version:
Code:
Function AspectRatio(b_2 As Variant, mac As Variant, Optional round As Integer = 3) As Variant
AspectRatio = Application.round(2 * b_2 / mac, round)
End Function
Only works with single-cell references or numeric inputs.
When called as:
Code:
=AspectRatio(Semi_Span, Mean_Aerodynamic_Chord)
It fails.
From my limited understanding - I've only started looking into VBA about 2 hours ago - I need to do a IsNumeric test on the inputs in question, and then use the row from Application.Caller.Address to select the right cell if the input is a range? Am I on the right track?
How to craft a Sub I can call in the 15+ math functions I made.
View 3 Replies
View Related
Jan 30, 2010
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".
Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
View 5 Replies
View Related
May 6, 2014
Using Excel 2010.
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB:
Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
View 3 Replies
View Related
Feb 18, 2014
I have list of data with corresponding number of chances ( see attached column E) I need to replicate the data under column A,B,C & D to a new rows as per the number under column E and so goes till the end of the list using a formula.
Basically in the attached example i should have a 43 new data rows
View 4 Replies
View Related
