I have this formula in cell C5 of a worksheet. =HLOOKUP(C2,OB_RB_MBRSHP_2002!$B$25:$M$35,11,FALSE). It is one of several formulas in a block of data referring to worksheets ending in 2002 (the year). Is it possible to take the OB_RB_MBRSHP_ part of the formula and use the 2002 part of the formula which is shown as the year in a C1l and create a formula without using the the OB_RB_MBRSHP_2002 in the actual cell? i.e. =concatenate("OB_RB_MBRSHP_2002",C1) in say c4
Then have a formula like this: =HLOOKUP(C2,C4!$B$25:$M$35,11,FALSE)
I know this method doesn't work because I have tried it, but I wondered if there was another way. My reason for asking is that this data is copied down on a regular basis and it would be far easier to change the year in one cell than to change all of the 2002 references to 2003 for example.
Sheet 1 is my main page containing all my data, full of formulas and is protected. Sheet 2 is a simple sorting page with no color and is simply for sorting and printing.
Sheet 2 has IF formulas refrencing rows and cells on Sheet 1.
Example Sheet 1 A2= Bob
Sheet 2 A2="" until I put in this formula:
=IF(Sheet 1!A2="","",Sheet 1!A2)
Now Sheet 2 A2= Bob
Simple...
But when I delete a row in Sheet 1 (using my cool new macro...long story)
I get #REF! in the cells on my sorting sheet, as I should I guess, because there is no more row there to reference. It's annoying, but what do I do to fix it without just unchecking the #REF! in autofilter on Sheet 2.
I just want to eliminate #REF!
Do I need to put something else in the IF formula?
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'. e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I need to set a cell colour by the value of a cell in another sheet so i can't use conditional format. Is there a formula that allows the user to set a cell style, colour, font etc.
I want to develop on the formula I have below to return the value in the same sheet as the formula from Cell AA2 if the result of the formula below returns #N/A
I found a post to name a sheet using the cell value with VBA but is there a "formula" in Excel that will do the same? I'm wanting only to use the first 10 letters of a cell.
After having searched for a while I can't seem to find any formula that will look at a cell for reference as to which sheet to go to and return a certain cell's contents.
For example, in column A, I have the text "Sheet1" and in column B, I need the formula to return the contents of cell B15 from Sheet1, based upon column A.
I have Excel 2007, here's what i want to do. I've got a workbook created with several worksheets in it. I want to input the name of a project in say, cell A1 on sheet1. Then on sheet2 cell C4, I want the text I input into the afore mentioned cell to show. I know how do do this with numbers (using the autosum button), but I can't figure out how to do it with text, though it's probably very simple & I'll be embarassed when I get an answer. Following is EXACTLY what I'm trying to do if the above example is confusing.
I have a Project Cost sheet and on it I input (text) a description of a building to be constructed. The cells next to it will be the associated costs of that particular building. There will be several buildings listed on the sheet. On a Sales Figures Sheet in the same workbook, I want to list those same buildings and then their corresponding sales prices. I want these descriptions to be automatically pulled from the costs sheet, so that I only have to input the sales figures.
How do I incorporate sheetnames into the vlookup function? I need the following sheet name: SALES (located in A1) to be incorporated into the function: vlookup("Sales"?,a1,n84,2,false). .
If I have 12 sheets in a workbook (Jan, Feb,etc..) can I have those names in specific cells (A1,B1,etc..) and call them out in a formula as the cell name as opposed to the actual name?
Any way to always include the sheet name before the cell address in a formula? For example, in my "Summary" sheet, I have a formula:
Code: =A2 + B4/B5
However, without typing anything else, I'd like the formula to populate the sheet name since I will be extracting the formula as text and need the full reference:
Code: =Summary!A2 + Summary!B4/Summary!B5
A way to get around this is to enter the equal sign, then temporarily select another sheet, then go back to the "Summary" sheet where all cell references will include the sheet name, but if there is a quicker way to do it using some kind of property or event in VBA, that would be ideal.
I am working on a sheet called 2005, with "Jan" entered into cell C4. I want to reference cells on the sheet called Jan2005 using a function that points to C4 and adds "2005".
I can't use code for this problem as I need to give this to someone who doesn't know VBA. They will need to use it in several different reports, so I can't produce something in VBA very easily. I have three worksheets, Summary, Year1 and Year2.
I want to display either Year1 or Year2 data in the Summary sheet depending on what the user enters in a cell in the Summary sheet. For example, if the user enters "Year1" in cell E2 then I want to point to a cell in the Year1 spreadsheet. I tried using formula: =E2 & "!B3" but this doesn't work. Is there another solution? I have attached an example.
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?
Excel 2007 I have 80 very similar sheets. The only difference is the sheet name (Phase A (1), Phase A (2) etc.). I want the formula located in cell B5 to be different on every sheet.
I have listed the desired formulas on a single seperate worksheet, named "Overview". On this sheet, the following columns have these data in them: A - Sheetname (example: Phase A (1), Phase A (2) etc.) B - Cell location (example B5, constant)
C - A formula (example: =Sheet1!G47, =Sheet1!G48, etc.)
I would like to create a macro that will loop through all the sheets, doing the following: 1. Check if the sheetname exists in column A of the Overview sheet 2. If it does, take the formula from column C of the Overview sheet 3. And put it in the cell specified in column B of the Overview sheet
Here is some code, to illustrate the above. I know the code is mostly nonsensical.
Code: Sub Enter_formulas() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets
Now this works well when the cell is in that same sheet but lets say I am making reference to a cell that is in a diferent spreadsheet how do I write that?
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.
I have an excel workbook that has many spreadsheets (each one sheet has a client name)I have another excel workbook that has these client names on one sheet (on a list) and next to every name I have a number (i.e total turnover of the year).
In the first workbook (where every client has his sheet (tab named after the client) I want to have a cell that equals to the sum of some cells on the other workbook, that refer to the specific client
(it looks like this ='[comissions NF 10-14.xls]comissions 14(auto)'!$J$81+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$197+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$313+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$429)
I want this sum to be added to every sheet of this workbook. each sheet refers to a client, so $J$81, $J$197 etc must be changed for the correct cell that refers to the name of the client. The tab names are alphabetical and so s the list.
Is there any way to do it, without re-entering the formula to each one?
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
I need a formula to add two (2) cells (one cell in sheet 1 and the second cell in sheet 2) and return a value of the two (2) cells in sheet 3. I would like the formula to return a zero (0) value instead of #VALUE! ERROR displayed in sheet 3 if a cell in sheet 1 has zero (0) value and the second cell in sheet 2 is blank. For example: sheet 1 D2 (0) + sheet 2 D2 (blank cell) = zero (0) instead of #VALUE! ERROR
In addition, if cells B11:B12 are blank in both sheet 1 and 2, I would like the formula to display blank in sheet 3 for cell C11:C15 instead of displaying #VALUE! ERROR.
Here is the formula I have: =IF(OR(ISBLANK(Sheet1!D2),ISBLANK(Sheet2!D2)),"",Sheet1!D2+Sheet2!D2)
Note: I have attached a sample data set for better conceptualization of the problem. Sample_Formula.xls
