Formula For Obtaining Value A Dependant On Cell X

Jan 16, 2010

so basically, im trying to find a way to get cell G3 to have Value X dependant on value from Cell G2. e.g. if cell g2 is 8 and 9, g3=10 if g2 is 11, g3=20 and so on. i dont mind having to add a preset table of values if we need, but it would be set on sheet 2.

EDIT
Value from cell G2 will always be between 4 and 20, set by the user. what i want is that each of those values gives G3 a pre-planned value.
if g2 has value 4. g3 value would be -5
if g2 has value 5. g3 value would be 0
if g2 has value 6. g3 value would be 5
if g2 has value 7. g3 value would be 5
if g2 has value 8. g3 value would be 10
if g2 has value 9. g3 value would be 10
if g2 has value 10. g3 value would be 15
if g2 has value 11. g3 value would be 20
if g2 has value 12. g3 value would be 20
if g2 has value 13. g3 value would be 25
and so on until it reaches g2=20

i was thinking of setting a table on sheet 2 for the g3 values, and get an IF function to do something like IF g2 = a then x, if g2 = b then y etc etc. im just not sure how it would work, unless an INDEX function would work better?

View 4 Replies


ADVERTISEMENT

IF Formula That References A Cell And Returns A Different Result Dependant On The Number In The Cell Being Referenced

Oct 2, 2009

I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.

For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.

What I wrote as my formula is as follows-

=IF(A1=3,"TEST")+IF(A1=4,"RESULT")

It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.

View 2 Replies View Related

Obtaining Varying Position Cell Value From Different Sheet?

Jan 21, 2014

I want to automate the process of updating my football stats and have delved into getting my data from the web. I have managed to import a football league onto my "standings" sheet but I want my "strengths" sheet to read it's values from this new "standings" sheet.

The "standings" sheet is broken down into 2 tables (home & away) one above the other.

The values I want are Home Games, Away Games, Home Goals For, Away Goals For, Home Goals Against And Away Goals Against from both the home table and the away table. The main problem I have is that the cell information I need is likely to change as I update the league tables from the web. So say one week Team A's data is in row 7 the following week it could be in row 8 so i need the data to correlate with the team name cell in my "strengths" sheet.

So at the moment Atromitos Home games (cell B8 on my strengths sheet) needs the value from cell C8 on my "standings" sheet but by next week it could need the value from C9.

Ps I am assuming that I can just just hit the refresh button on the ribbon and the data from the web site will update. Is this correct?

View 4 Replies View Related

Pivot Table Formula Dependant On List Selection

Jun 10, 2009

Im trying to create a 'drill-down' interface with the GETPIVOTDATA command.

I believe (but im not sure) that this will require several different formulas.

e.g., assuming this formula resides in A1, this returns all data in the pivot $A$6 for Monday of 6/1/2009:


=GETPIVOTDATA("Sum of Mon",$A$6,"Week", DATE(2009,6,1))

however for cell A1, if the user wants to drill down, then the required formula expands to the following..in this case we are drilling down to Name=Baby Becket/Ball, Stage=Infant..and so on..


=GETPIVOTDATA("Sum of Mon",$A$6,"Name","Baby Becket/Ball","Week",DATE(2009,6,1),"Stage","Infant","B/L","B","WL",)

Essentially, without writing a bunch of IF's in the formula for A1...is there a way to put these formulas in a lookup table, and then depending on what the user chooses on how they want to analyze the data (e.g., they may select Name, Stage, etc from a drop down list elsewhere on the sheet), the appropriate formula is populate in A1?

In a nutshell: Can the formula of a cell be changed depending on what the selection value is of another cell or list value?

This could probably been done easily via VBA, but if there is a formula or vlookup based solution that would be easier..

View 9 Replies View Related

Formula For Obtaining List Of Unique Values But Not Including Values Which Contain IC

Aug 14, 2014

1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).

I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)

2. To include all unique string values except those starting with the letters "IC"

3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.

Here is the formula I currently use which serves #1 (above):

[Code] .....

Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.

Example of desired results:

Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |

View 14 Replies View Related

Inputting Dependant On Cell

Oct 12, 2008

on sheet1 which is called working sheet and cell d25 is asking what type of goods been sold

i have several different sorts"

ie
s25
s28
s50
s69
s70
s82
abd
t70
t140

i want the spreadsheet to look up somewhere on a sheet called delivery all the components that could be used for that type

so i guess i need to name define?

ie if s25 is selected

on delivery sheet in a20 it would then list all the components underneath each other.

View 14 Replies View Related

Automatically Shade Cell Dependant Upon Text In Aother Cell

Jul 13, 2009

Im writing a spreadsheet that will act as a hotel room booking diary. There will be 52 sheets (representing each week of the year) and each sheet will therefore represent one week

Each sheet will look at operate the same. Running horizontally will be the days of the week and running vertically will be each of the bedrooms. There are 22 bedrooms in total and each room will have 12 cells allotted to it for each day - lets call this a 'block' and presume that the first block runs from C10 to c21. The final of these cells C21 will have a validation and the user can select 'PAID' 'TO PAY' or 'INVOICED'

If the user selects 'PAID' in C21 I want cells C10 through to C21 to shade GREEN
If the user selects 'TO PAY' in C21 I want cells C10 through to C21 to shade RED
If the user selects 'INVOICE' in C21 I want cells C10 through to C21 to shade YELLOW

View 3 Replies View Related

Copy Picture Dependant On Cell

Jan 9, 2007

I am trying to make an Excel sheet with a frontpage sheet and a data sheet. In the data sheet I have several columns with data and then in the end a cloumn with pictures. On the front page sheet I can easily with VLOOKUP formulas transfer the desired data from the data sheet to the front page sheet but I cannot seem to figure out how to copy the picture. When I try to copy the cell with the picture in it, the formula only copies the cell value, which is "0" because the only thing which is in the cell is the picture. how to copy a given picture from one sheet to another based on the information in another cell like a "Vlookup" formula?

View 3 Replies View Related

Cell Format Dependant On Rows

Mar 13, 2007

I have

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
Select Case .Value

Case "(None)": .Interior.ColorIndex = Null
Case "One": .Interior.ColorIndex = 38
Case "Two": .Interior.ColorIndex = 18
Case "Three": .Interior.ColorIndex = 35
Case Else: .Interior.ColorIndex = xlNone
End Select
End With
End If

End Sub

My problem is that columns A to H are yellow(36 I think) and are merged cells.
And Cells AW2 to BD2 are also yellow. The cells inbetween are white.

At the moment when the Cell value is "(None)" the cells turn white, I dont want this. I would like them to go to default.

View 9 Replies View Related

Image Dependant On Cell Content

Aug 31, 2007

i have a column with numbers in, in numerical order, however some cells are null. ie.
1
2

3


4
..
etc. I need to put paste images from a folder next to these numbers. (1.bmp next to '1' etc) and i need to leave the empty cells in. This code is sort of right..

Sub aids()
Dim strPath As String
Dim strFile As String
Dim lngRow As Long
Dim objPic As Object
Dim sngMaxWidth As Single
On Error Resume Next
lngRow = 2
strPath = "C:images"
strFile = Dir(strPath & "*.bmp")
With ActiveSheet
Do While strFile <> ""..........

View 7 Replies View Related

Color Row Dependant On Cell Entries

Jun 3, 2008

I have a spreadsheet where I would like the rows to change colour dependant on value input to certain cells, i.e. if a any value other than a zero put into a cell it changes to green, if a zero put into a cell it changes to grey, if a zero put in but a value put in another cell it changes to another colour, if values already in are taken out of other cells, it changes to yellow.

I've looked on FAQ and there are a lot of similar things but not quire wha I'm after and even thn I dont know how to use VBA etc to achieve.

View 9 Replies View Related

Make Text Dependant On Cell - Bold

Feb 18, 2010

i have some code where i need to make the text bold but not sure how to go about it
the formula is

View 3 Replies View Related

Save A File Dependant On Cell Reference

May 21, 2007

i have a order form that is customer dependant. the cust name on the form is manually entered by the user within a msg box. i have specific cusotmer folders on a shared machines C drive where i would like to save these excel docs to.

i understand that the cust name must match the name of the file and there needs to be a way to save those that do not have specific folder matching what was entered as the customer name.

the overall goal is to have the file name saved as CustomerNameDeliveryDateInvoiceNumber.xls where CustName is from cell "M3", delivery date is "Q7" and InvoiceNumber is "Q1".

Sub OrderFormSave()

Dim strCustFileName As String

savefile = "\SrSharedDocsCSPSharedFILESCustomerOrderForms & strCustFileName & "

ActiveWorkbook.SaveCopyAs savefile

End Sub

View 9 Replies View Related

VBA Restriction To Cell Input Dependant On Other Cells

Oct 2, 2007

I have a sheet where I want to not enable someone to enter data in a cell unless there is data entered in a range of other cells.

Range A1:A5 compulsory data to be entered
Range A6:A20 No data can be entered can be entered unless A1:A5 is filled in

Does someone have some code that can enable me to do this?

View 9 Replies View Related

Cell To Respond Either Or Dependant On Values Of 2 Cells

Mar 3, 2008

I am trying to get a cell to respond either or dependant on values of 2 cells.

If A1 = 13
B1 = 0
C1 = 0

I would like D1 to return A1, but

If A1 = 13
B1 = 1
C1 = 0

or

A1 = 13
B1 = 0
C1 = 1

I would like D1 to return the sum A1 + B1 + C1

View 9 Replies View Related

Change Cell Colour Dependant On Text

Jul 20, 2006

how hard would it be to chane the background colour of a cell when a certain word is entered into it? so someone would type 'james' and press enter then the cell would turn red....would only be in a set cell range..

View 9 Replies View Related

Variable Locked Cells Dependant On Cell Change

Jan 12, 2010

I'm trying to make a spreadsheet more secure to prevent unintentional changes.

What i'm looking to do is to lock all cells so no changescan be made unless the user clicks column A.

Column A should remain unlocked and when the user clicks it i would like the entire row unlocked for editing (but only after column A is clicked)

I will run a macro to lock the entire sheet again once the sheet closes.

View 2 Replies View Related

Propagating Text Boxes Dependant On Cell Relationship

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

Unlock Cell/s Dependant Upon Entry In Another Cell

Jan 3, 2010

I've never dealt with vba before and I'm not even sure if that's correct. I wish to lock a worksheet with two cells left unlocked, when an entry is made in one or other of these two cells I need other cells to become unlocked.

View 2 Replies View Related

Macro That Saves Into Specific Folder Dependant On Cell Values

Oct 20, 2008

i have this macro that saves into specific folder dependant on cell values and it works spot on with the exception that my po numbers are like st010,st011,st012 etc etc but when it saves it doesnt have the st in front. ive tried numerous ways but to no avail

View 5 Replies View Related

Formula For Obtaining Values On Separate Sheet From Values Declared On Current Sheet?

Jun 3, 2009

i have a workbook with two sheets. lets just call them sheet 1 and sheet 2. on sheet 1 i have data for employees and their current wages and other info. on sheet 2 the data is for compensation scale on three separate columns.......

in order for me to automatically get the data from (sheet 2 B3) the formula for sheet 1 E1 would be: ='Sheet 1'!B3. how do i formulate the equation so that i can do ='Sheet 2'!(C1)(D1)?. in other words i want to specify the column and row from the values declared in sheet 1 column c and column d respectively.

View 4 Replies View Related

Obtaining A Range

May 2, 2006

I need to obtain a range of cells starting on cell B10 to the end of records (vertical)

I need to do two things with this:

1) Count the number of records from cell B10 to the last row populated with data

2) Format the cells in this range

FYI - "R" is always the last column.

View 3 Replies View Related

Matching Columns And Obtaining The Value?

Dec 18, 2012

I have two worksheets, sheet 1 and sheet 2. On sheet 1, I have a information on two columns (Column A and B) Column a has information up to row 10 (aaa, bbb, ccc, ddd, ...). On column B, I have 111, 222, 333 . I have same information on Sheet 2. However, sheet two has an additional column (Column C) with 10 rows on information z, y, x, w, v ...

What I need to do is to match column A and B in sheet 1 to column A and B in sheet 2 and if both columns matches on the same row, copy the value in sheet 2, column C and paste it on sheet 1 column C.

Important: Information on sheet 2, column B may be twisted (As they may not appear exactly the same as in sheet 1. Which means 111,222,333 can be 222,333,111 along the column). Therefore, need to match the entire range.

View 7 Replies View Related

Obtaining Information From Listboxes

Feb 4, 2014

I have a listbox (form control) on an excel sheet, where the user can select (or deselect) multiple entries.

Is there a way to have vba spit out which options are selected? if I choose "single" for selection type, it will tell me which option i have selected. Is there something similar for multiple?

View 3 Replies View Related

Obtaining Values Within A Range

Mar 15, 2014

I've got a problem where I need to assign values to ranges between 0 and 3.

Eg.

RANGE VALUE
2-3 2.2
1-3 1.6
0-3 0.8
0-2 0.5
0-2 0.5
0-1 0.1
0-1 0.1
0-1 0.1
0-1 0.1

The values above are simply approximates done manually but I need it done automatically using a formula. The resulting values should be proportioned similarly to the ones above but they need to all sum up to 6 and be no more than 3 each.

View 9 Replies View Related

Obtaining A Number Value From A Field

Jul 31, 2009

I am going to create a large drop down list with data validation the list is to do with fitness the list will containt a sport with a numeral after it
for example : GOLF 4.5 then under that Basketball 12

what i am wanting to do is make a calculation based on what sport is selected in that cell for instance GOLF 4.5 is selected in the cell so i need to say valueinA43*D43/B9 can this be done?? Or if not. can i extract the value from the list to another cell without the text and then perform the calculation.........

View 5 Replies View Related

Obtaining Data About PDFs

Nov 20, 2013

I have a large number (couple of hundred) pdf's in a folder for electrical test certificates. The pdf's are in the format 54 Pike Drive.pdf etc. and have the date modified.

Ideally, as well as the address I would like the date modified as this is pretty close to the date of the certificate so will suffice.

Failing the date, just the addresses will do which need to go on separate rows in Excel.

I've tried copying and pasting the files but to no avail.

View 13 Replies View Related

Obtaining Birthday Day From Next Month.

Mar 5, 2008

I use the following fomula to test and advise of an upcoming birthday:-

=IF(AND(MONTH(TODAY())-MONTH(B6)=0,DAY(B6)-DAY(TODAY()) 25.

View 9 Replies View Related

Obtaining Digital Certificate

Sep 24, 2006

I have a spreadsheet with macro's in and everytime the user opens it that annoying security box opens and asks to allow/disable macro's. I believe it is due to a digital certificate. How to i obtain one, and if i do get one will it stop thie popup occuring for other users on different computers or just me????

View 2 Replies View Related

Obtaining Interpolated Values Of Z Based On X And Y

Jan 12, 2013

I'm working on a program, which gives me interpolated values of z from a digitized graph based on the cursor position or input values of x and y. Thing that bothers me for quite some time is how to get properly interpolated z value. I was using SADA (spatial analysis software), where I was creating a 100x100 matrix, but the interpolation methods there are not as accurate as I need.

Basically, from digitizing a graph I get a matrix:

VB:
x1, y1, z1;
x2, y2, z1;
x3, y3, z2;
x4, y4, z2;
.
.
.
x3742, y3742, z24

Now, inputting x and y that are not in the matrix should give me interpolated value of z, which gets even more tricky when the axes are in log scale. The only idea that I have in my mind and that I think would work is this:

for each missing z, from zmin=0,625 to zmax=3 , every 0,001, I would make 1000 (enough accuracy in this case) xs and ys. How to automate the process.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved