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.
How to input information into one cell and it appears in another cell based on specific information inputted.
View 3 Replies View RelatedI am trying to make excel list in a column like 1,2,3,4,5. this is dependant on a value i place in one cell eg 5
i want excel to then place 1,2,3,4,5 in seperate cells down a column. does this make sense.
then the calculations will only appear the the numbered cells.
I'm inputting data in E3 and after I hit enter I want B2 to be
selected. But only for E3 and only in one worksheet. And possibly a
further step... Sometimes the formula in B2 has picked up data from
another place depending on what value was entered in E3. If it's done
this then I won't need to go to B2, I'd want to go to A8 after E3
instead of B2.
cell a1 has yes cell b1 has yes and i want to type yes in c1 that will change a1 and b1 automatically to a blank cell
View 3 Replies View RelatedI want to create a macro or formula for changing the colour of the cell after half an hour of inputting.
View 3 Replies View RelatedI have a worksheet in which i am asking a user to enter manually a number in cell E3. Suppose the user starts with 100, then the next time he is entering in E3 he shoudnt be able to enter 100 nor any number less than 100. I dont have a range for the numbers that the user is going to enter.Therefore countif function does not work. Is there any way that i an store the number taht the user enters first in cell E3 and then use that database to avoid the user from entering the same or a number lesser than that number.????
View 2 Replies View RelatedI'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.
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
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?
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 RelatedI 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.
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 <> ""..........
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.
i have some code where i need to make the text bold but not sure how to go about it
the formula is
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
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?
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
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 RelatedI'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.
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.
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 Relatedi 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 RelatedI have a question to ask, and not sure what you would call it. I am looking to input a 'Check Box' into a cell so you could have for example... two (2) choices - Yes - No - you would have an empty circle in front of each, and you select the one (1) you wish which would make it filled.
Is there a way to do this in excel? If there is, could someone possibly turn me in the right direction (or assistance) with inputting this?
I've just been asked to do a new Quality Control project for my boss. What I'm looking to do is be able to input the thickness values of our product in one worksheet, and have it recorded in another, as well as have the data separated by the model number
The main problem I'm having is figuring out how to enter data in the 1 worksheet as a template, and have the data consecutively stored, one after the other
Are there any functions that will allow me to do this?
way to 'insert' a formula into a specific range of cells using VBA only when the Value of $A$7 is changed to a value that does NOT equal "Custom".
Heres what I have so far using Worksheet change event....
I discovered this sample database sheet (attached) on the forum I wanted to ask if a picture can be linked included? i.e. when entering a new record a picture (stored in a directory) can be added and linked to that record. Then when reviewing the records the picture is then displayed.
View 6 Replies View Relatedfor example.
I type "20 Feb 07", and excel formatting auto changes that to "Tuesday 20 Feb 07"
I actualy need this for a more complex formula...
if A1= any date from monday to sunday of one week, then A2= mondays date+21
the only way i can comprehend this is to make a formula that says if A1= a monday A2=date+21, A1= a tuesday A2=date+20, etc.
I have some code that works fine below. It deletes an entire row based on the value in column J.
Ideally I would like to give the user the option to choose which column to base the deletion on. i.e. some kind of drop down box or input parameter where they could choose a different column "A", "B", "C" etc...
Private Sub CommandButton1_Click()
'Removes values less than 0
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("J1:J1000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value)
I have a sheet with a number of different columns, is there a way that when data is input into a cell in the first column that it time stamps the last column on the same row, and this time stamp is fixed. Then when data is input into the cell below the same thing happens, so i can have a time stamp for each entry.
View 9 Replies View Related