# How Do I Fill In A Cell Based On The Relationship Of The Number Within A Range Of Numbers

Feb 6, 2009
This is a spreadsheet for a real estate office. The boss gave me a spreadsheet that he created to give a seller an idea how much money he will walk away with at closing. The spreadsheet includes all of the pertinate numbers but there is one item that he would like to have filled in automatically. When selling a house, you use a title service that charges a fee based on the price of the house. So, for example, If the house sells for $0 to $50,000, then let's say the fee is $112. If the house sells for $50,000 to $60,000, then the fee is $118. If the house sells for $60,000 to $70,000, then the fee is $125, and so on up to 1.5 million.

The boss' spreadsheet includes 5 different selling price scenarios, which I guess doesn't matter since you do the first one and the rest follow the same pattern.

So how do I automatically fill in a cell based on how one number falls within a range of other numbers? The fee schedule will be within the same spreadsheet, if that matters.

Ideally, I think they should be separate files, but this will be used in various locations as different realtors take it home or use it on their laptops.

View 9 Replies
ADVERTISEMENT
Jan 22, 2008

I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance.

The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.

View 5 Replies
View Related
Feb 15, 2010

In the attached workbook I'm trying to populate Column E with sequential numbers (as shown) based upon a changing range (defined as a named range called 'range'). Is it possible to write a formula in the cells in Column E that will do this?

View 2 Replies
View Related
Feb 11, 2010

I am trying to create a macro to fill Sheet1 cells A1 through P1 down, and I would like it to be filled down based on the number in Sheet2 cell R1.

View 4 Replies
View Related
Jul 3, 2014

I am looking to use a VBA routine so when a Date is added in a cell and a number is in the cell below on running the routine I would like the Date to be filled by serial number to the cells to the right. So if D3 has a date in the cell say 03 July 2014 and E3 has the number 10 then to the right of D3 it fills the Date's 10 Cells to the right 03 July 2014 through to 13 July 2014.

Code:

Dates

03/07/2014

Amount

10

View 7 Replies
View Related
Dec 8, 2011

I have a range of cells (D10:D20) with numbers in them. I need cell A10 to look at D10 and return a specific number based on a set of criteria.

The same thing would go for A11:A20 & D11:D20.

If the number in D10 is 1-2 then A10 needs to display 0

If the number in D10 is 3-8 then A10 needs to display 0.1

If the number in D10 is 9-14 then A10 needs to display 0.2

If the number in D10 is 15-20 then A10 needs to display 0.3

Once I have that formula I know that i will just highlight A10 and drag the formula down through A20.

View 9 Replies
View Related
Dec 15, 2009

I have a set of data in column R, with an unknown number of rows that looks like this

Days Late

-28

150

3

16

41

.

.

.

and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:

View 4 Replies
View Related
Feb 27, 2014

I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.

What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.

View 7 Replies
View Related
Mar 19, 2013

A have three columns with "Kilograms range" and corresponding value.

A========== B========== C

KG From====-To======= Predefined Value

what formula will fill up the cell with the predefined value automatically based on the range, after a kilograms are entered in a different cell as explained in the attachment.

View 2 Replies
View Related
Feb 24, 2014

I wanted to enter value in sheet1 on A1. Columns in sheet2 from B1 until the value entered in sheet1.A1 should be automatically filled.

E.g.

Sheet1.A1=5, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3, Sheet2.B4=4, Sheet2.B5=5.

if Sheet1.A1=3, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3.

if Sheet1.A1=7, then Sheet2.B1=1, Sheet2.B2=2, Sheet2.B3=3, Sheet2.B4=4, Sheet2.B5=5, Sheet2.B5=6, Sheet2.B5=7

etc.

View 2 Replies
View Related
Apr 23, 2009

refer to attached worksheet.

I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.

View 9 Replies
View Related
Aug 8, 2013

I am collecting data the temperature of an animal, i am also collecting ambient temperature at the same time when i plot the two data sets there is an 89% Correlation so i know the animal temperature is affected by the ambient temperature. i want to be able to take this influence of ambient temperature out of the recorded temperature. I dont think its a linear relationship

View 5 Replies
View Related
Jul 25, 2008

The idea is to get for some range (size/location doesn't matter) to fill it's cell with numbers from 1 to number of cells, but it doesn't seem to work.

View 9 Replies
View Related
May 27, 2012

Any macro or a VB script to fill the numbers in between the "start" and "end" range . The only input that will be provided is the start and end of the range.

For example if the start = 100 000 and end = 100 010.

The output should be

100 000

100 001

100 002

100 003

100 004

100 005

100 006

100 007

100 008

100 009

100 010

View 9 Replies
View Related
Jan 19, 2008

I am newer here and I want a macro in mentioned title. I have a number in column a2 and in column b2 quantity if in column a2 number is 2101400 and in column b2 i write quantity 50 so said number automatically fill series in column a like mentioned below...

View 9 Replies
View Related
May 27, 2009

Is it possible for a macro to enter the number that is in Range("SYear") into A4, fill that number down 30 rows (31 total A4-A34), then add 1 to the number and put in the next 31 cells..repeat untill the number = Range("EYear"). For example if SYear = 2010 and EYear = 2012, I need 2010 in A4:A34, 2011 in A35:A65, & 2012 in A66:A96.

View 4 Replies
View Related
Oct 2, 2006

I can't seem to make SumIF or Vlookup do what I want here.

I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column.

If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3)

Period Target Actual %Target

1 74 68 91.9%

2 81 71 87.7%

3 76 87 114.5%

4 76 68 89.5%

5 71 89 125.4%

6 69 81 117.4%

View 3 Replies
View Related
Feb 18, 2008

I am trying to create a table, based on what people predict in sports games. I have most of it in place, but I have hit a brick wall where one part of it is concerned. For example, cells M43:M56 have the scores for each week of what one person predicted., so it could be 2,5,7,2 and so on. What I then need the table to do is determine which round or week it is and display the relevant score. So based on the above let's say it's round 3, and his score would be 7 for the week, and the cell to the right of this shows the week's score plus his total score so far.

View 2 Replies
View Related
Mar 13, 2014

I'm using the VBA code below for a piece of code.

[Code] .....

However, I want to use this same function in another place, without the limit set on the cells that will be cleared.

What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.

Something like:

[Code] .....

View 3 Replies
View Related
Jan 18, 2008

I have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:

Start Range End Range Qty

2101200 2101499 300

I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc.

but some time these numbers are in qty 30000 or more. I have questions

1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.

My Second question is opposit of my above question.

2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example.

Series [b]Start Range End Range Qty

1 1 1 1

3 3 3 1

5 5 7 3

6

7

View 8 Replies
View Related
Mar 31, 2014

So I have 2 sheets from a much larger worksheet where I wish for the first sheet to extract a row of values, one at a time from the second sheet using a range of numbers I enter as reference for where to look for the data. Sheet 1 can be thought of as a summary page and Sheet 2 is where data is stored.

I need to first check if any data under Sheet 2 column B fall within a specified range of numbers and if any of those numbers are found I want it to grab the largest and latest number from that range and pull all the data from certain columns in that same row and place it into Sheet 1, one column value at a time.

Sheet 1 A1 = A range of numbers as text. 10-15, 16-20 etc Only one range is entered. This also tells me what results I'm looking at.

Sheet 1 B4:B14 = Destination cells for the individual data I'm wanting to pull from Sheet 2 columns H:P. B4 wants H, B5 wants I, B6 wants J etc

Sheet 2 column A = A3:A102 are numbered 1:100.

Sheet 2 column B = A series of numbers anywhere from 10-70. Always in sequence and they can repeat. 10,10,11,12,13,14,15,15,15,16 etc. Ranges from B3:B102. Only one number per cell.

Sheet 2 columns H:P = Data in H3:P102 I want to extract to Sheet 1 B4:B14.

Now lets say I want a formula for Sheet 1 B4 which wants a value in Sheet 2 H. If Sheet 1 A1 = 10-15, I want to check whether Sheet 2 column B has any values equaling those and then tell me which row that last number appeared in.

Example: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5. (B6 is outside range and B4 isn't the last time the 14 appears.)

Then, knowing B5 is the value I want, find which row it is in (row 5 in this example) or use the number in A5 (3) and then find my way to column H (H5) where the value I want to pull is.

Example 2: Sheet 2 B4=14, Sheet 2 B5=14, Sheet 2 B6=16. Use B5.

Sheet 2 H4=10, Sheet 2 H5=32, Sheet 2 H6=42. Place "32" from H5 into Sheet 1 B4.

I'd like to also have some error control so I'm not trying to pull data from blank cells if it's relevant. Maybe check if Sheet 2 X3=0 and if it is, do nothing as no data appears if the cell is 0.

I have put a lot of time into trying to solve this myself but I feel way out of my depth. I've tried going step by step but I can't seem to figure out which functions are relevant and also things like how to return the range that the A1 values appear in or if using MAX, not having it return values outside of A1's range also.

View 7 Replies
View Related
Jul 3, 2007

I'm trying to find out how can I fill a range of cells when clicking in a Command button in Excel. The process should be:

1) The user opens my XLS worksheet.

2) Goes to the next empty row and fill some specific fields

3) Then he should fill up 15 parameters on the right as Y or N

4) In case all of them are correct instead of going one by one typing "Y" I want he clicks in a button (with VBA code associated) and then 15 cells on the right should be filled with "Y" value

View 8 Replies
View Related
Jan 5, 2008

(The attached workbook will provide a more obvious explanation.)

I am building a 'reporting' workbook. It has 3 tabs.

i. the first I will use for buttons to call macros (no help needed)

ii. the second tab is 'REPORT' - where data from the third tab ('DATA') will be collected/organized -based on a textual value in column D - before being exported into a seperate workbook (finalized report).

iii. third tab is 'DATA' - this sheet will contain all the data that will provide info for reports

Presently, the 'REPORT' tab has been manually filled in with data from 'DATA' tab - I did this thinking it would provide better explanation here; take note that values in column D will be gathered via vlookup to other worksheets not included her.

Here is what I want to do:

1. goto 'DATA' tab, find the first PLATFORM (column D) text value

2. goto 'REPORT' tab, put that text value in B2

3. go back to 'DATA' and in each row that the same PLATFORM is present, copy over the corresponding info to the appropriate cell in 'REPORT' tab

4. once I've collected all the rows for one PLATFORM - export and save the 'REPORT' worksheet to a seperate workbook in 'C:REPORTS' and close

5. clear the range where data is being written in 'REPORT' tab ("A5:H60000"), then go back to 'DATA' and get the next PLATFORM (column D) text value - enter it into B2.

6. fill the range ("A5:H60000") with the corresponding values from 'DATA' based on the new PLATFORM name...

View 3 Replies
View Related
Oct 8, 2006

I have been working for three straight days (and nights!) to accomplish something that is very simple in other languages, but I can't make it work in VB

Column X has, with spaces between 4 possible contents:

3Q

FM

1Q

NM

St1 St3

3Q today

tomorrow

-2

-3

-4

-5

yesterday

FM today

tomorrow

-2

-3

-4

-5

-6

yesterday

1Q today

tomorrrow

-2

-3

-4

-5

yesterday

NM today

tomorrrow

-2

-3

-4

-5

yesterday

3Q today

tomorrow

-2

and so on for 1500 rows.

I can write this in Lotus in about 2 minutes, but VB has me stumped. I hope someone can put me on the corrrect path.

View 8 Replies
View Related
Mar 17, 2014

Please see attached sample worksheet. Column A will be generated by the user manually.

I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.

I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.

Book2.xlsx

View 3 Replies
View Related
Mar 14, 2008

I'm trying to populate a dropdown Combo Box by using conditions.

In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.

I've been trying to use worksheetfuntion.Index.

View 4 Replies
View Related
Aug 16, 2012

I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.

Data

A001

Data

A002

Add next number (A003) when data entered in cell to left

View 2 Replies
View Related
Sep 5, 2012

I'm trying to count the number of numbers within a given range.

Example Row Y has cells that contain a series of numbers 1,5,9,12,23. No cell in the range should have the same individual number more than once. At the end of row Y I want to know how many numbers, not the total of the numbers are within that range.

Y

1 1,3,5

2 21,32,44

3 12,19,31

..

40 2,4,18

41 total number of numbers in range Y1:Y40 - 12

View 9 Replies
View Related
May 23, 2014

I need to judge whether a number falls between a range of 2 numbers.

Ex:

A1=332.024 B1=349.956

C1=335.23 So D1 must give 1 (since between the range) else 0.

If C1=332.02 D1=0

If C1=349.9566 D1=0

If C1=332.024 D1=1

If C1=349.956 D1=1

What formula should be punched in D1?

View 8 Replies
View Related
Mar 5, 2012

I have a mass of data which look something like this:

table removed

and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.

So basically for example I would want the top row to now read:

table removed

and then apply the same procedure to the other ranges below this.

View 4 Replies
View Related