The "Jul" which stand for July, it is possible to have a formula where I can have certain text in a formula that is based on the value in B1 (In this case, B1 being JUL in bold writing.)
Like this;
=('Input &B1 2014'!$BI$3)/1000
Or must I manually go in and replace all formulas for each month?
I have table as below of raw data. In which data will increase dynamically both for rows and columns from starting column for eg say Column "D".
I want to get ID data on sheet2 to compare it with some other data for the ID by using formula = INDEX(Dynamic range,MATCH(ID Ref- Sheet2,Sheet1! ref -dynamic,0),column ref) and get the referred ID data on "Sheet2").
On Sheet2 ID Data1 Data2 Data3 Data4 Data5 Data6 Data7
I'm trying to find the details required to refer to contents of a cell and then treat the value as the formula. IE for Sum(B5:B8), i'd like to have two cells, one with a 5 in it and one with the 8.
I know its possible if i put "b5" as the cell content using indirect - but I'll be using the same cell value to update formulas in different columns so I cant afford to include the B. I assume theres a very simple method along the lines of Sum((B&(Cell(a4)):B&(Cell(a5)))?
I would the formula to refer to C1 to get the "Smith B" part.
This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.
To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?
Cell B13 contains the row number of the first row of data on the sheet and B14 contains the row number of the end row of data. This is because the number of rows vary over time.
I'm trying to do a COUNTIF to test for duplicates in column R (contains integers) and I want to specify the range to count by referring to the value in B13 and B14 in the formula and so I have the following which returns FALSE for some reason even if the value in R19 appears more than once in the range ...
i'm having in excel- I'm looking to populate rows in 2nd tab based on the data entered in the 1st tab. The rows to be populated in the 2nd tab resides in the 3rd tab. So a match has to be made on the data entered in the 1st tab. When a match is found on the 3rd tab then the corresponding rows from the 3rd tab should be listed on the 2nd tab.
I am having trouble creating a macro that renames all excel sheets using cells on Sheet1.
See attached, I want to rename all excel sheets (renamed as "1", "2", "3"....."50") as "9999", "10000", "10001"..."10045", found in Sheet1, renamed as "Base". I will change the cell values everyday (range Base!A5 to Base!50) - and will rename all the sheets again.
i want to update values fon an excel sheet on a month by month basis. So i have jan to dec folders and excel sheets under each folder , i want to update my monthly forecast sheet based on the numbers in the folder I want to just change the cell in my monthly forecast sheet to Jan or Feb and the values should be pulled from the closed excel files in the monthly folders. I tried concatenation but it gave me #REF .. Is there a way to do it other than using "pull" ??
I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example
If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.
I'm creating a workbook to keep track of my utilities payments, one sheet for one utility and so on. I like to copy two cells from each sheet to another one to keep me updated of the amount to pay and the date. an example: column A with text, if text "NEXT" appear in column A, copy the value of two cells (at columns B & C) at the right of "NEXT" to another worksheet, if that possible? Below is a photo as an example:
Currently I have it setup to copy rows to a sheet "Report" based on a single cell value. But now I need the same thing but have it copy the rows based on 2 cells values to sheet "Report". So for example I wanted to copy and paste each row in my workbook that contain values in Columns N:N that contain the value "Test" and in columns AB:AB that contain "1".
Sub copyagain() Application.ScreenUpdating = False Dim sh As Worksheet, findThis As String, fAdr As String, fLoc As Range findThis = "1"
I have a workbook with a list of staff in it. The format is: the manager's name is in row 1, the 8 or so team members are listed below. And again in row 20, there is a manager's name with 8 or so team members listed below. This is duplicated over many columns and multiple sheets. The sheets are all named after the ops-managers who manage all the managers on their respective sheet. The book is called "stf.xls"
I'm using the following code to look up a staff member and return who their manager and ops-manager is to the relevant cells in a different workbook:
Code: Sub AdvisorFind() Dim Wsht As Worksheet Dim Slookfor As String
[Code]....
My problem is how to return the manager's name for a staff member in the middle of the book. Activesheet. is the last worksheet so no good. Wsht.name.Range(manager) is what I want to do but I don't know the correct syntax.
i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....
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.
I just named a range on a sheet and would like to paste that range on a different worksheet within the same workbook. I can't figure out how to refer to the range. I've tried several different variations so far. Example below:
ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11" 'Add new sheet and paste data ActiveSheet.Name = "Raw_data_Sector_Summary" ActiveWorkbook.Sheets.Add.Name = "RawData" Range("A1").Formula = "=RawData!(RawSectorData)"
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required" if B5 is +3% then b6 =110 if B5 is +2% then b6 =106 if B5 is +1% then b6 =103 if B5 is 0% then b6 =100 if B5 is -1% then b6 =96 if B5 is -2% then b6 =93 if B5 is -3% then b6 =90 if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
I'm trying to summarize some data from an external workbook. The problem is that the worksheet names there are months (Jan2009, Feb2009 etc.) and the sheets rotate (change position) with time. In the summarizing workbook I would like to use a function to refer to (for example) cell A1 on the third worksheet, regardless of the current name of that worksheet. So I might have a cell in the summary workbook that looks something like "=CoolFunction(ExternalWB.xls, 3, A1)"
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A 0 0 0 0 0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
I would like the formula to read the worksheet name rather than entering the word Tankersley directly, so that if I copy it to another worksheet named Tyson, the formula would read:
I would like to have vlookup formua. And I would like the sheet for the table array section be linked to a cell value. So in my workbook this is my vlookup formula:
=VLOOKUP(B2,sheet1!B2:C8,2,FALSE)
I would like to to be something like =VLOOKUP(B2,(=b3)!B2:C8,2,FALSE). This way I can change the value in B3 and the vlookup formula will look for values in a different sheet as opposed to the one I originally designated in the formula.
Is it at all possible to refer to a array that may change in a formula?
For example I need to use a Vlookup formula, however the table array will change depending on the value of another cell.
I need the user to be able to select the column heading that the lookup should work off from a drop down list. So if the user selects column heading C, the array should start from column C though it will always end at column Z. If the user selects column heading Y the array would be Y:Z.
I think I understand what you were talking about now with the generation of the watchbill using the rand() and sort. You were trying to get me to get rid of the whole system I was using before and use only the rand and sort. I thought you were asking me to incorperate the rand sort thing into the randomization process I already had using offset etc. The whole thing works good now, however, because the column Ive designated for the roster names may or may not always be filled the formula sometimes refers to empty cells in that column thus producing 0s on the watchbill. I was thinking maybe (if its possible) having a formula to identify a 0 and if so skip to the next cell down. The formula would repeat until it found a name without a zero in it.
I am trying to write a sum formula using Indirect so that the end-user can enter the names of the sheets in B1 & B2 (the values are always in cell F5 on each sheet). I thought this would work but it is throwing a REF# error.
I need a better way to refer to an empty cell. I'm using “” but if the cell contain a formula but show empty it affect the conditioning format I'm using this =AND(cell <> "", existing_rule)
This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.
I need to know what code i would have on the 'delete' button of the form.
If you want me to describe it any further i would be happy to, just ask.
I want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement
I have a workbook with two sheets. Sheet 1 has raw data that contains contacts with multiple statuses. I need to pull out only the contacts that have a positive status and put them into Sheet 2.
I would like there to be no spaces between the contacts in Sheet 2. I'd also need all of the data pertaining to each positive contact (so not just their name, but all of the data in the row that relates to the contact).
