Formula To Show The Value Of One Cell Based On The Value Of Another To Automate Some Internal Processes
I'm trying to get a formula to show me the value of one cell based on the value of another to automate some internal processes. Cell J3 returns today's date, then J4 says what week number that is. J5 concatenates the two to make the look up. Column A is named range "PN", and columns C onwards are named ranges to match the column heading.
What I'm wanting is for the result in test cell 2 (J8), is to return the stock requirements for the current week, based on the result of cell J5. I can do this by using nested IF statements, but that would be 8 cells to calculate, then another one to find the cell that has a value. If there a way to get Excel to recognize the value in J5 as a range name, so it just needs the one formula?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Internal Repayment Formula
I have the following rows in my excel sheet: -Cash needed to start the quarter. -Net cash position. -Begining of the quarter debt owed. -Internal repayment. I am trying to develop a formula for internal repayment which must fulfill following requirements(Net cash position-cash needed to start the quarter),the remaining amount goes to internal repayment but that must be <=Begining of the quarter debt owed.
View Replies!
View Related
Internal Range Conditions Formula
Please take a look at this sample spreadsheet. [url] I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.
View Replies!
View Related
Formula To Show Project Size Based On Number Of Hours
I need to show the size of a project in a cell based on it's number of hours, e.g. Between 5-80 hours = SMALL Between 80-200 hours = MEDIUM Over 200 hours = LARGE How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?
View Replies!
View Related
Show Sheets Based On Cell Value
I have 15 sheets - Resident 1, Resident 2 etc If cell A1 on Summary tab contains 10, I want sheets Resident 1 to Resident 10 visible, everything over Resident 10 to be invisible If cell A1 on Summary tab contains 5, I want sheets Resident 1 to Resident 5 visible, everything over Resident 5 to be invisible
View Replies!
View Related
Show Different Data Based On Value Of Cell
Call centre data for financial years located in separate workbooks (1 for each year) Here's what I want to create. One main file to consolidate/view all data I've tried to make this as user friendly as possible by having a validated list, whereby the user can select the year they want to view, and hey presto, the data changes to reflect the year displayed in the validated cell.
View Replies!
View Related
Show Image Based On Cell Value
Question: I would like to have two pictures (one in cell B2, the other in cells D1-C10 to I10) display based on a data validation list that I have in A1. So, for example, if you click U.S.A., two pictures for U.S.A. show up; if you scroll down to Canada, two pictures show up. If you're interested, I have included the history of what I have tried in the past. History of what I have tried: I used this code before [url] , but that makes all pictures but 1 invisible and I need two. So then I used this one, Sub HelloWorld() 'Message box to say hello MsgBox " Sub GetPicture() Dim oPic As Picture, imgFlag As Range, imgMap As Range ' Loop Flag and Map images 'imgFlag = Worksheets("Image").Range("B1") 'imgMap = Worksheets("Image").Range("D1") For Each oPic In Worksheets("Summary").Pictures If (oPic.Name = Worksheets("Image").Range("B1").Text) Then oPic.Visible = True oPic.Top = Worksheets("Image").Range("B1").Top oPic.Left = Worksheets("Image").Range("B1").Left ElseIf (oPic.Name = Worksheets("Image").Range("D1").Text) Then oPic.Visible = True oPic.Top = Worksheets("Image").Range("D1").Top oPic.Left = Worksheets("Image").Range("D1").Left Else oPic.Visible = False End If Next oPic End Sub"
View Replies!
View Related
Show Picture Based On Cell
I have folder with 5 photos on c (c:Pictures). Photos are named a, b, c, d, and e. In Excel workbook sheet1 (c:Photos.xls), how to display the photos in cell range (B1:F10) in response to change in cell A1 (i.e., when A1 cell value is “a”, the photo “a” is displayed in the range B1:F10, and so on when the A1 value changes)
View Replies!
View Related
Hide Or Show An Image Based On A Cell Value
I have a spreadsheet with two bitmap images inserted into it. In cell E2, I will enter a number, either a 1 or a 2. if I enter a 1, I want only the first image to be visible. If I enter a 2, I only want the second image to be visible. Is there a way to accomplish this (hopefully without the need for macros)? I've attached a spreadsheet as an example of what I'm trying to do. Also, note that I'd like the images to be stacked on top of each other so that they show up in the same place regardless of wether there's a 1 or a 2 in cell E5
View Replies!
View Related
Show Numbers Based On Percentage In Cell
I have a rather large report full of data, 10+ columns contain a RANKING column, and these ranks, 1-10, are assigned by a % bracket: 1-20% = 1 21-40% = 2 41-60% = 3 61-80% = 4 81-100% = 5 101-120% = 6 121-140% = 7 141-160% = 8 161-180% = 9 181-200% = 10 There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.
View Replies!
View Related
Automatically Hide / Show Rows Based On Cell Value
I wrote a small code to hide some columns if a certain cell is equal to a certain string. The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line. Sub Auto_Open() Range("A1").OnEntry = "Action" End Sub Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
View Replies!
View Related
Hide & Show Rows Based On Cell Value
I have two sheets say: Sheet1 Sheet2 Sheet1 has a few comboboxes saying (YES / NO) conditions Which are assigned to particular cells (for Ex: say Combobox1 value assignes to Sheet1!B5 ) If Sheet!B5 = YES some rows in Sheet2 Say ( Row12 ,Row 15,Row 16) has to be hide. I will add a command button to sheet1 and call macro if i click command button checking the conditions in sheet1 combo boxes..rows in sheets2 has to hide..
View Replies!
View Related
Automate Calculation Of Output Based On User Input
What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required) I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.
View Replies!
View Related
To Automate A Formula Sheet
I am trying to automate a formula sheet used by truck drivers to determine how many hours are available to work as they cannot work over 14 hours in a day or 70 hours in 8 days. This must be charted daily, including days off. Taking two consecutive days off resets the 70 hour rule. So... Col B2:B8 Enter the number of hours worked each day. Cell C8 Sum B2:B8 except when there are two consecutive zeros entered during the seven days being counted, then count only from after the second zero. (ie. b2=6, b3=0, b4=0, b5=8, b6=10, b7=7, b8=11 only sum(b5:b8)). So I am looking for the formula to to sum under this condition.
View Replies!
View Related
Formula To Show File Name In Cell
I am trying to get my cell to match the name of the file. I am aware of the following formula: =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5) This does return what I want, but members of my team often have several sheets open within one Excel window. The formula returns the most recently opened file within the window rather than the name of that particular file. This then distorts my results sheet as it ends up with the name of the most recent file opened rather than the person's name (which is what the file is called)
View Replies!
View Related
Formula To Show A Blank Cell When A SUM = Zero
On my spreadsheet i have 4 columns which are in currency format. The 5th column (total) adds the 4 up which currently looks like this - =SUM(BG44:BG45) this shows £0.00 in the 5th column. What i need is a formula in the 5th column (total) so that when it adds up the 4 previous columns if the sum = £0.00 then the total column should show a blank cell.
View Replies!
View Related
Copied Formula Show In Cell Not Results
I have copy 65'000 formula text cells from a software and paste it to excel. But, the formula in cell is not activated. In order to activate the cell formula, I have to click on each individu cell and press enter to active it. But i got 65'000 rows of the similar cells. So, what is the quick way to all the text formula at the same time.
View Replies!
View Related
Automate Formula Copy/Fill-Down Until Last Row
I have some formulas that combine template text and values from cells in that row to result in text that I then Copy and use in another place. I am trying to convert that to a Macro. My Skills are Novice -- I have searched Google and Tutorials and it seems that my usage is not common so, I have not found answers to help me move ahead. I'm working with a simplified version of what I want to do to eastablish a Working model that I can modify and expand. I had a macro that ran to completion but I abandoned it. I realized that I was on a Wrong Track. It was created from recorded macro sections and googled routines and very clumsy. Heres my Scenario If my Excel Cells were Cell A1 = 135252 Cell B1 = $13.95 Cell C1 = 3 Cell D1 = Oversize I want to run a Macro from E1 that would Result in text of Our Stock Number is 135252 - The Price is $13.95. We have 3 in stock. Type of Packaging is Oversize being left in Cell E1. I would like the Macro be able to run on other rows as well when launched from Cell E of that row. My Goal would be to make the macro work on a range of rows by Highlighing Cell E in those rows. My Excel Formula to accomplish the same thing is - ="Our Stock Number is "&A1&" - The Price is "&B1&". We have "&C1&" in stock. Type of Packaging is "&D1&"." If I could get this much working, I think I can expand and edit to encompass my actual application.
View Replies!
View Related
Hide/Show Sheets Based On Cell Values & Validate Entry
excal VBA programming.I have attached the file name "help" for your easy explanation purpose. 1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell. 2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
View Replies!
View Related
Display/Show Blank Cell Via IF Formula/Function
The following are on a sheet: A1 = 5700 B1 = 235 C1 = 17:14 D1 = 5922 $E$1 = 09:01 $F$1= 3 $E$1 and $F$1 are (the only) absolutes/constants. A2 contains the following: =IF(A1>D1,B1,IF(A1+B1<D1,"d",IF(AND(C1<$E$1,B1<$F$1),"",B1)))) This translates as: =IF(5700>5922,235,IF(5700+235<5922,"d",IF(AND(7:14<09:01,235<3),"",235)))) The result is a variable/number (235), "d" or a blank cell (""). This formula is in a column and works fine where there are numbers in corresponding cells. The problem arises with corresponding cells which appear blank (show no values) but contain references to other cells: they result in a "d" when nothing should be displayed. So while a corresponding blank cell is correct if it shows no values, it gives me this problem - I don't want "d" or anything.
View Replies!
View Related
Conditional Formulas-to Get A Spreadsheet To Automate Calculations Of Unit Costs Based On Variable Packaging Names
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names. I have a series of packages that are denoted by text phrases. Examples: 4/6/12 2/12/12 6/4/12 18/12 24/12 For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1). I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
View Replies!
View Related
Changing A Worksheet Internal Name
I need to create a new worksheet and control both the internal and tab names of the worksheet. The following code works to create the new sheet, and set the external (tab) name. Sub AddAndRenameWorksheet() Dim ws As Worksheet With ActiveWorkbook .Worksheets.Add Before:=Sheets(1) .Worksheets(1).Name = "Schedules" 'changes the external name End With End Sub I do not know how to set the internal name For example, I would normally go into worksheet Properties and change the (Name) property. Is there a way to do this in VBA?
View Replies!
View Related
External References That Should Be Internal
I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.). In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file. BUT on 2 occasions this reference changed to an "external" reference to the previous month's file. I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it. Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time. One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?
View Replies!
View Related
Macro To Check Internal Ips
we have a network of small routers and wifi aps. The Ip range has got a bit out of control so I would like to be able to audit it every once and a while. how I could go through a set of IPs on a spreadsheet, open a telnet session or whatever, try pinging each ip individually, paste a response or no response in the adjacent column then move to the next ip in the first column (some sort of loop). Time is not too much of an issue as I could run this overnight if need be. As a response, the macro could just copy the last line of the ping eg Minimum = 7ms, Maximum = 9ms, Average = 7ms if it responds or Packets: Sent = 4, Received = 0, Lost = 4 (100% loss) if it does not respond Example Col1 col 2 Ip 1.2.3.4 Packets: Sent = 4, Received = 0, Lost = 4 (100% loss) 4.5.6.7 Minimum = 7ms, Maximum = 9ms, Average = 7ms 5.6.7.8 Packets: Sent = 4, Received = 0, Lost = 4 (100% loss) 6.7.8.9 Minimum = 3ms, Maximum = 21ms, Average = 9ms etc
View Replies!
View Related
Solver Fails In XP- An Unexpected Internal Error Occurred
I have a little spreadsheet aplication which ran perfectly in Office 97, but since the move to Windows/Office XP, I get the message: Solver: An unexpected internal error occurred, or available memory was exhausted. I'm running a P4 2.26GHz with 512MB RAM. My code is as follows: Public Processing_Message As String, Macro_to_Process As String Public StartTerm As Integer, StopTerm As Integer, StepTerm As Integer Public ResultsLabelCount As Integer Public myErrorFound As Boolean Public rs As Object Public ctl As Control
View Replies!
View Related
Solver: An Expected Internal Error Occurred, Or Available Memory Was Exhausted
I have a macro that does a solver routine. I have another macro that runs this solver, then copies and pastes new inputs, then runs the solver, then copies the results and pastes them somewhere, then copies new inputs, etc. It is a long process and it worked fine a few days ago. Now when I run it, about 90% of the way through and then excel crashes. The error says: Solver: An expected internal error occurred, or available memory was exhausted. What can I do?
View Replies!
View Related
Set Up A Direct Link To The Internal Calculator, Using A Button In VBA
I'm trying to set up a direct link to the internal excel calculator, using a button in VBA. I have the shortcut on my own toolbar, but I have many users that use the workbook, and to get them all to set up is almost impossible, so I am trying to make it simple for them. I've tried doing it through a hyperlink direct to the calc.exe file, but get a lot of messages popping up (that'll scare a few of them). So thought I would try and find the excel name for it and use VBA, but I cannot find it anywhere.
View Replies!
View Related
Automate Time & Date Insertion Upon Cell Selection
I have a Workbook with about 20 Worksheets, for each sheet I have a Selection Change Event that simply performs the Following: MainSheet1. Range(“Z1”) = Now 1 Cell on every Sheet then is pointed to this cell via =Main!Z1. Problem is that when this is preformed Each Row from the Other In-Active Sheets and sometimes a near-by Button will Appear on my Active Sheet. I cannot select the parts of the sheets that appear and when I scroll down till they are out of view and then scroll back up they are gone until I select another cell and it happens again. I have tried in the Z1 cell to just put =NOW() and Make Calculations Manual and then with the selection change to call MainSheet1.Calculate or Workbook.Calculate but this has the very same effect and the other sheets bleed onto or ghost onto my Active Sheet. So far the only way I’ve been able to get around this is: If Application. ScreenUpdating Then Application.ScreenUpdating = False MainSheet1.Range(“Z1”) = Now Application.ScreenUpdating = True Else MainSheet1.Range(“Z1”) = Now End If...............
View Replies!
View Related
Formula For Cell Result Based On Other Cell Input
I'm trying to work out the way of having a cell give a result based on what has been input into two other cells. Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
View Replies!
View Related
Macro To Automate Multiple "IF Formula"
I have a worksheet of 5 columns. What I would like to do is subtotal an amount in column d, where the item code in column b are the same. Lets say there are 5 rows with code 8150 in column b, I want to add all these rows up BUT ALSO add up any rows which are 8150+1 (x+1) i.e. 8151. All transactions where the code in column B, x + (x+1) = 0. All true results should then be cut and pasted into a new worksheet (one worksheet for all transactions that are true). e.g. 8150 1233 8150 12 8150 644 8151 -2084 8151 -10 8151 -205 Total of above amounts is zero, therefore cut and paste to new spreadsheet. Now that I think about it, its actually quite complicated.
View Replies!
View Related
If A Cell Has Data Show It If Not Show 0
I have a column of data held in column B. I am required to show the following. If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0. I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it. do I need a macro?, or conditional formatting?
View Replies!
View Related
Lock Cell Based Upon Formula
Is it possible to lock all cells with the exception of a select few(say a column) based upon a date? I have a spreadsheet where I want to lock all cells with the exception of the current months information. I want the spreadsheet to auto unlock the date that is listed on the top of the spreadsheet. I have columns for all months of the yea and I have the current month listed in cell A1. I want match the A1 cell to the column header to unlock the correct column.
View Replies!
View Related
Change Cell Formula Based On First 2 Numbers Of Another
Would anyone have a script for something like this? I'm looking at range c5:c1200 Starting at C5 If the first 2 digits in that number start with 01 then I want the formula to be in G5 = F5*12 if the first 2 digits are 03 then I want G5 = F5*24 I have several of these if statement to put in However I need it to check the entire range as well because the formula may be different in each cell (G)?
View Replies!
View Related
FORMULA TO EXTRACT DATA BASED ON CELL VALUE
I'd like to have a formula in Column C which would extract the quantity from either Column A or Column E depending on the value in Column D. A ---- B ---- C ---- D----------------E-----------F QTY -UOM----------Tariff #----------QTY 1-----UOM1 46----PCS---------3402.12.5000----94.3--------KG 85----PCS---------3402.12.5000----174.25-----KG 4-----PCS----------3701.10.0030----4.8---------M2 5-----PCS----------3701.10.0030----1-----------M2 15----PCS----------3701.10.0030----1-----------M2
View Replies!
View Related
Formula To Reset A Count Based On Cell Value
I've found a # of threads on this subject but have been unable to find the function I need. I've got a spreadsheet (attached) to calculate vacation time, but I can't figure out how to make the used time reset on an anniversay date. For example, if Employee 1 has earned 80 hours of vacation but only used 40 by his 4 year anniversary date, how do I make unused time roll back to 80 the day of his anniversary? Here's the structure: 2 Personal days given Jan. 1, then 2 more on July 1 to employees here at least 6 months. Those whose 6 months fall after or between are awarded 2 days on their anniversary then accrue as normal. Unused personal days are lost at the end of the year. Vacation time is 40 hours after 1 year, 80 hours at 3 yrs, then 120 hours at 5+.
View Replies!
View Related
Formula To Adjust Based On Target Cell
I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%. I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers. I have attached the spreadsheet which will be clearer.
View Replies!
View Related
Constant In Formula To Change Based On Cell Value
I am creating on the fly an array (2 columns) in which the one row cell is mathematically derived from its neighbor (A left-hand cell). However, when the mathematically derived product exceeds a value, the row neighbor changes to another constant until its neighbor's product exceeds another value, etc. Can I create such an array without resorting to VBA?
View Replies!
View Related
|
TRACKER
