I have a workbook, everyday 2 new worksheets get added to reflect the previous day's work. How can I reflect in a formula to go to next sheet instead of the conventional naming 'Sheet1' G7?
View 6 RepliesI need to change cells to reflect new costs. Then have these costs roll into new totals. I attached a worksheet to demonstrate how it works. The numbers in gray should be adjustable. The numbers in tan are summing up incorrectly with my "SUMIF" formula.
View 2 Replies View RelatedI have a sheet (sheet2) that has cells in column L, M and N referencing cells in another sheet (sheet1)(same workbook).
If I delete a row out of Sheet1 it causes the formula in Sheet2 to go to #REF.
Normally the formula would be something like
='Sheet1'!L13
How can I get these formulas to not change just because cells where deleted from Sheet1?
My goal is to have changes done to one sheet reflect on to another to make things much more easy to do. Below is a picture of what I am dealing with. ( I covered some personal information)
pic of excel.jpg I want to be able to plug in something or delete it and have it reflect to all other sheets. Also I would like to be able to plug in some information and have it export itself based on a specific information. For example, I would want all Newark,NJ contacts to be place in the Newark list automatically.
I have two different Google Sheets Workbooks;
I need that for ie: changes made on C3 of Workbook 1, reflect on E23 of Workbook 2.
I'm trying to copy a huge list of formulas and formats from one work book to another almost identical, each time it adds the old workbook name title into the formulas, this is easy to do in the formula bar for individual formulas but can this be done for many?
View 3 Replies View RelatedI have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.
e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.
I am running a macro to create a combined master summary sheet from data in several other sheets contained in one workbook. That is working fine. I need to create some kind of refresh macro so that when data in these sheets change the master sheet will change and update automatically.
View 9 Replies View RelatedI am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it.
The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?
How do i change a title in legend on a chart. i.e. I want the label to be MGRS 2%
Job Category
Category %
EE Total
Mgrs
2%
15
Supvr
6%
51
[code]....
I have managed to change the icon on the title bar but is it possible to change the "MICROSOFT EXCEL" wording next to the icon on the title bar
View 3 Replies View RelatedI want to use a macro to save an .xls file as an .mht file. I have:
ActiveWorkbook.SaveAs Filename:="C:ReportsBook2.mht", FileFormat:= _
xlWebArchive, CreateBackup:=False
which works for saving, but how can I Change the resulting web Page Title using VBA? I tried recording the macro as I changed the title and saved but nothing recorded for that. I know I can always just go in after the macro runs and manually change it but it seems like there must be another way.
i have 2 workbooks open, i need to write the code so that i can copy the sheets from each of the workbooks to a whole new workbook. this does not seem like a difficult task at all. so here are my problems:
1) once im in the activewindow, how can i copy the sheet it has in it, since i do not know what the sheet is called, and also i should store the sheet name in a variable so i can reference it later
2) how can i switch to the other window without knowing what its filename is
I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
i'm trying to get data added in one sheet of a workbook to automatically be entered into another sheet. such as a monthly, Quarterly and Annual balance sheet.
View 3 Replies View RelatedFirst, to i change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not.
Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. but its hideing the formulas. once i protect the cells it wont let me to edit the worsheet (eg- cell height, cell width)
I am looking for the best way to change the font of cells in a Worksheet that contain a formula. I have used Go To-Special-Formulas but have one slight problem with this method. It highlights all cells that contain an "=" sign. Some cells may be linked (ie cell A2 may say =cell A1). How would I change the font only for cells with a Function such as Sum or Vlookup and not for cells that simply link to another cell?
View 7 Replies View RelatedI have what I thought was a very simple task; Change all date formulas in the workbook to values. I've accomplished this, but I was wondering if someone had a better way of doing without so many loops (I used two). NOTE - Dates are on different sheets within the workbook. I've tried setting each date on each sheet to one named range. I then tried doing this
range("dateranges").values = range("datranges").value
this obviously didn't work. Anyhow, this is what I used that works, but I'm convinced that there is a better way.
Sub FinishOU()
Dim sh As Worksheet, wb1 As Workbook, r As Range, TestRange As Range
With Application
. ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
Set wb1 = ActiveWorkbook
With wb1
.Save
For Each sh In Worksheets
Set TestRange = sh.Range("A1:S4")
For Each r In TestRange.SpecialCells(xlCellTypeFormulas).........................
How can I create a formula that will reflect a % value within that range?
(Example)
Input cell = 129 this value can be changed between 0 and 800
A1 = 0
A2 = 800
(Range) 0 to 800 = 100%
What would the percent value equal?
In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.
I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.
Book0.5 template.xlsx
I have a spreadsheet that holds the NHL schedule for the season. (See thumbnail)
In the picture I've highlighted the formula I use to determine how many games each team has over the next 7, 14, 21 and 28 days. What I've been doing is deleting the column for each day after that day has passed. (So tomorrow, I'll delete column K, and all the other days will move up one. Sunday will become column K)
My problem is that the formula =7-COUNTIF($K3:$Q3,"—") in cell G3 will read =7-COUNTIF($K3:$P3,"—") after I have deleted Saturday, and thus will only count 6 days worth. (The 14, 21 and 28 columns will only count 13, 20 and 27 as well.) Is there any way I can write this formula so that it stays as K3:Q3? Right now each day I modify the 4 formulas for the 1st team and then copy/paste then over the other 30, but this is rather tedious.
I have a master worksheet with due dates for various forms to be completed. These forms are on separate worksheets within the workbook.
I'd like to highlight the master date entry in red if the form hasn't been completed by the due date, but also highlight in green when it has been done.
I've seen it done, and had the formula on a couple documents, but had a loss of data. What I'm looking for is the formula to enter into a cell for copying the name of the worksheet into that cell.
View 6 Replies View RelatedI am trying to sum the details from sheet2 in sheet3. I am currently using a plain formula to sum-up the values. However, create a indirect and vlookup function to sum the values.
In sheet3, I have to sum the values from sheet2 based on the contacts. Sheet1, sheet2 and Sheet3 are connected and any update(insert,delete.. etc) will be made in sheet 1 only. Sheet2 automatically updates based on sheet1. Similarly, Sheet3 should also update based on sheet1 and sum the values based on contacts from sheet2.
I have added the formula which I am currently using in the sheet.
I have a spreadsheet that references another spreadsheet, however the other spreadsheet will get updated from time to time. When it's updated the file name changes to reflect the revision:
"C:Projects9-022_Tunisia1_Design9-022-R00-Inst_Database.xls"
When this file is revised the "...R001..." will increment with each revision but the rest of the file will stay the same.
Sometimes I have to reference a file in another directory:
"C:Projects9-030_Brazil1 Design9-030-R00-Inst_Database.xls"
I was planning on placing information in the following cells of this spreadsheet ("C:Projects9-022_Tunisia1_Design9-022-R001-Cause_&_Effects.xls):
Cell A1 - Project Number (i.e. 09-022, or 09-030)
Cell A2 - Project Name (i.e. Tunisia, or Brazil)
Cell A3 - File Rev Number (i.e. 1, 2, 3, etc.)
I was thinking my "INDIRECT.EXE" would look something like this:
=INDIRECT.EXE("'C:Projects"&A1&"1_Design["&A1&"_"&A2&"-R00"&A3&"-Cause_&_Effects.XLS]Index'!Q5")
The value on the spreadsheet tab "Index" that I'm referencing in cell Q5, is "FG-5901" but all I'm getting in the spreadsheet were this formula resides, is an error "#NAME?"
Part of my problem is that I don't fully understand the function of the "&" symbol in the formula. Sometimes I've seen it placed on both sides of a cell reference (i.e. &A1&), and other time only at the being (i.e. &A1).
I am needing to reflect some of my work in a waterfall chart, but do not have that as an option in my list of charts. Is there a down load I can get?
View 2 Replies View RelatedI have a 2 columns (A and B) next to each other that is using a Sum formula and other multiplying formulas. They reference 3 cells in a different column (C) to come up up with the answer for A and B. Therefore A1 and B1 reference C1 C2 and C3 (and possibly if want to use in future column D with D1 D2 D3).
Then in A2 and B2 I want to reference from C4, C5, C6
Then in A3 and A4 I want to reference from C7, C78, C9 etc.... and so on...
Instead of retyping formulas for each row in A and B, I want to copy down, but I getting the wrong answer when I do that.
I am therefore assuming I typing the formula wrong. How do I type it correctly so the cell reference changes automatically when I pull down columns A and B?
Formula for Column A: =SUM($G28:$G30)
Forumla for Column B: =($G28*$H28+$G29*$H29+$G30*$H30)/$B17
Therefore next row should be referenced from G31 to G33 and H31 to H33
But when I copy it only adjusts it for 1 reference down.
I have an excel file - central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.
I now need to add a few columns to the central database and these columns can't be after the columns already existing, i.e. they have to be on the left of some of the existing columns. Inserting those columns is going to mess up all the vlookup formulas in the dependent files.
Is there a way change the central database file without affecting the vlookup formulas in the dependent files?
I've compiled data into a spreadsheet which indicates the number of homes sold over the course of 3 years (from 1/1/05 to now) along with their price per square foot, sale price, year built, bed/bath count, and other pertinent factors regarding each property.
I've extrapolated graphs from these in the past which indicated the trends in each category over time (i.e.: Average quarterly price per square foot over time, and so forth).
The data range I'm working with now, however, is from an area of my region so small, that the number of units sold over the past few years is sporadic. Whereas it's normally 100 to 1000 units that comprise my data, now it's only 15 units.
This has created gaps in my line graphs where the price per square foot will dramatically drop down to zero over the periods in time where no sale was recorded. So the line graph ends up looking like a zig-zaggy range of peaks reflecting the price per square foot of a sale, and valleys representing "$0.00" for periods of time where nothing has sold.
To have a Price per square foot of $0.00 is incorrect since in reality nothing was sold.
I would like to be able to eliminate the portions of the graph that have the "$0.00" so that the line graph can "jump" from one sale to the next when the quarterly data indicates 0 units sold.
I'd like to do this without having to eliminate those areas in my spreadsheets. Is there any "catch-all" tool that might do this or is that just crazy talk?
Im trying to capture the first instance of a value(ie 1) and reflect the top row of the value (date). I cant seem to make Hlookup work. I got it to work on the first line using index and match, but when i tried to copy it on the succeeding cells, it gives me the wrong data.
I also tried the following
StartedDate started1-Dec2-Dec3-Dec4-Dec5-Dec6-Dec7-Dec8-Dec9-Dec10-Dec
aaaaaaayes12/1/200911
bbbbbbyes1/0/1900111
b2b2b2b2b2b2no
ccccccccyes111