copying formula with worksheet name-need new formula to have another worksheet name

I want to be able copy a formula from b5 to b6 but have the worksheet
name change, i.e.:

I have 13 sheets in my workbook; the first sheet is called Summary
which is where my formula presides.  2nd sheet,3rd sheet, etc. are
called Sal Hrs_Jan10, Sal Hrs_Feb10, etc.

My formula in b5 is =+'Sal Hrs_Jan10'!$C$15.
I want b6 to say  =+'Sal Hrs_Feb10'!$C$15
I want b7 to say  =+'Sal Hrs_Mar10'!$C$15

Column a, cell a5 has Jan-10 (this is a date 1/1/2010 formatted as
custom, mmm-yy).

Can anyone help me???
0
Kim
2/4/2010 4:54:33 PM
excel 39879 articles. 2 followers. Follow

1 Replies
1003 Views

Similar Articles

[PageSpeed] 42

Put this in B5:

=3DINDIRECT("'Sal Hrs_"&TEXT(A5,"mmmyy")&"'!C15")

then copy down as required. This assumes that you also have dates in
A6 onwards - if not, then use this instead:

=3DINDIRECT("'Sal Hrs_"&TEXT(DATE(YEAR(A$5),MONTH(A
$5)+ROW(A1)-1,1),"mmmyy")&"'!C15")

and copy this down.

Hope this helps.

Pete

On Feb 4, 4:54=A0pm, Kim <erbe...@yahoo.com> wrote:
> I want to be able copy a formula from b5 to b6 but have the worksheet
> name change, i.e.:
>
> I have 13 sheets in my workbook; the first sheet is called Summary
> which is where my formula presides. =A02nd sheet,3rd sheet, etc. are
> called Sal Hrs_Jan10, Sal Hrs_Feb10, etc.
>
> My formula in b5 is =3D+'Sal Hrs_Jan10'!$C$15.
> I want b6 to say =A0=3D+'Sal Hrs_Feb10'!$C$15
> I want b7 to say =A0=3D+'Sal Hrs_Mar10'!$C$15
>
> Column a, cell a5 has Jan-10 (this is a date 1/1/2010 formatted as
> custom, mmm-yy).
>
> Can anyone help me???

0
Pete_UK
2/4/2010 6:44:56 PM
Reply:

Similar Artilces:

copy header/footer btwn sheets
How do I copy the header/footer from one spreadsheet to another? Copying the entire worksheet and using Edit->Paste Special->Formats leaves out the Header/Footer. and I know how to craete a copy of an existing sheet, but it's a pain to then delete the contents from Sheet1Copy, copy contents from Sheet2 to Sheet1Copy, delete Sheet2, then rename Sheet1Copy to Sheet2. I swear I saw somenoe do this years ago with a combination of holding down a key or keys and clicking on sheet and dragging to another, but I can NOT remember how to do it. Trevor, group the worksheets, then do the he...

why right outer join is needed?
hello, i am having one query regarding joins if in left join i reverse order of table it behaves like right join then why we need to seperate joins? thanks in advance. "Archana" <trialproduct2004@yahoo.com> wrote in message news:7ce01b82-18b9-4b21-98ad-b83fdda6548a@i19g2000pro.googlegroups.com... > hello, > > i am having one query regarding joins > > if in left join i reverse order of table it behaves like right join > then why we need to seperate joins? > > thanks in advance. For ease of writing queries - sometimes it's ea...

Visual Basic error when editing a formula
When editing a formula, I get a strange popup alert: Microsoft Visual Basic (in the blue bar) ! Tipo definito dall'utente non definito (user defined type not defined) Any suggestion? regards On Thu, 4 Sep 2003 09:52:57 -0700, "Enzo Leoni" <enzo.leoni@pilkington.it> wrote: >When editing a formula, I get a strange popup alert: > >Microsoft Visual Basic (in the blue bar) > >! Tipo definito dall'utente non definito (user defined >type not defined) > >Any suggestion? What's the formula that you're entering? Is it a built in one, a ...

I need Create a National Account and Child Account
Hi. I need Create a National Account and Child Account, too. I am Using a Econnet Schemas, i can create a ParentId, but can't create a childparenteId, i don't konw that schema will use to create this childparent, i read the SDK but i don´t understand how to apply this schema in VB.NET. How i can to create this ChildParentid in VB.NET?? I need only one example to Understand how to create this!!!!! I need create this account from CRM and i'm using a webservices whit VB to create this account!! thanks!!!!!! ...

Workbook name as variable to another workbook
Hi, Can you save a specific workbook name as a variable, to be used in a different macro? i need the variable to be saved and available to return to the specific workbook open if i have a lot of books open to complete the task. Regards, nigel Option Explicit Dim myWkbkName As String Sub runmefirst() myWkbkName = ThisWorkbook.Name End Sub Sub runmesecond() MsgBox myWkbkName End Sub If you execute runmefirst first, then runmesecond, you'll see that myWkbkname is available in the second. If you need to put the routines in different modules, declare myWkbkName as Public: Publ...

Need a method to convert varying french words to english within a cell
I have a spreadsheet with descriptive terms in French. I would like a search and replace method that can replace multiple the target words within a cell, when that cell contains additional words and names that are not to be altered. How do I do this with substitute and a vlookup from a table of target words and english equivalents or is there a VBA macro that will do this? Thank you for your time and any help you can give. Regards G Have you tried Edit > Replace........ Vaya con Dios, Chuck, CABGx3 "hollerg@basf.com" wrote: > I have a spreadsheet with descriptive te...

Excel 2003 - vba
HI All: I have this working in one workbook, but when I transplant it into another, it fails to function as expected. Issue: I would like to check my current workbook to ensure that a sheet does not exist, before trying to create it. In a UserForm, I allow the user to input a sheet name that they would like to import. Upon their depressing the "AddListBut" on the userform (ListForm) it checks to ensure the user put something in the Listbox "NewListName" then check to see if the worksheet already exists. Public ShtName as String Private Sub AddListBut_Click() If ListForm...

"display a notification message when new mail arrives" checkbox resets
Hi there, I have an annoying little problem here. I have a new windows XP PC running Office XP (2002). In MS Outlook I have set my "display a notification message when new mail arrives" setting, but every time I exit and reenter the application this checkbox is no longer marked. It gets reset for some reason. I have downloaded all patches from Microsoft's site, but to no avail. Any suggestions are appreciates. Thanks in advance. Mike ...

Laptop Needing POP3 & Exchange For One Email Account
Ok, here goes. I have a user that needs a laptop set up for 2 locations. Her primary location is on the network and will use the Exchange server to get mail. Her secondary location is a remote site that uses POP3 to get to the Exchange email. I have licenses to either Outlook 2000 or 2003 available. Is it possible, using either version (prefer 2003), to set up her email account on this one laptop to access her company email via direct network connection AND POP3 when she is at another site? Thanks in advance! Jeff Jeff wrote: > Ok, here goes. I have a user that needs a laptop set ...

Access the "automatically suggest a name to enter" data ?
When typing in a name in a new e-mail, Outlook automatically suggests a name to enter, and I like this. However, I have many in there that I would like to add to my contacts. At present I do not want to type in "a" and then sellect one, then add it to my Outlook contacts etc and then repeat all they way through to "Z" Other than doing this ONE at a time, is there another way? Thanks You can't even add them one at a time the way you suggest. You can only add your autocompletion cache to your Contacts using third party software. http://www.ingressor.com/deskt...

Need Formula To Find Data
I am using Excel 2000. I need a formula to find particular data within an array of data. I have a worksheet displaying inventory activity. Cell A1 is a cutoff date. Cells A4 thru A1000 are the dates of the activity. Cells B4 thru B1000 are descriptions. Cells C4 thru C1000 are additions. Cells D4 thru D1000 are subtractions. Each row will have a date, description and either an addition or subtraction amount (but not both on the same row). I need a formula to return the date, description and adddition amount of the last row with an amount in the additions field where the date is ...

Displaying sheet and cell value in another cell
I am using the Min formula to find the minimum value from tables on seperate worksheets. The summary sheet displays the minimum value. Is there a way to indicate on the summary sheet, from which sheet that the min value was selected from ? -- MB Hi using an approach from Harlan Grove try the following: 1. put the names of all your sheets in a range of your summary page. e.g. in the cells X1:X10 2. In A1 put your MIN formula (I assume your values are in A1:A10 on all sheets) 3. Now use the following array formula (entered with CTRL+SHIFT+ENTER) =INDEX(X1:X10,MATCH(TRUE,COUNTIF(INDIRECT(&qu...

Outlook 2003 closes when saving new Note
Hi, I've got a problem with Outlook 2003 and cant find the answer anywhere. I use Notes, well I would like to, in Outlook 2003; I can create the Note fine, but as soon as I click on the X to close the Note I get 'Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience.' and then the option to Restart Outlook or not, either way Outlook closes. The error signature is: - AppName: outlook.exe AppVer: 11.0.8312.0 AppStamp:4a403990 ModName: unknown ModVer: 0.0.0.0 ModStamp:00000000 fDebug: 0 Offs...

How to set a value in a cell whose address is held in another cell
Say I have A1=4 and A2="D3", can I write a macro to set D3=4, using the information in A1:A2? -- Ellis Morgan Sub MakeItReal() Dim strText As String strText = Range("A2").Text Range(strText).Value = Range("A1").Value End Sub -- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Data Options add-in: row stuff/date picker/random stuff) "Ellis Morgan" <ellis@nospam.demon.co.uk> wrote in message news:HYPSVWGORzfPFwv1@mrtlfrm.demon.co.uk... > Say I have A1=4 and A2="D3", can I write a macro to se...

Compose new message text in Plain Text mode
Plain Text format is very small. How do I increase it ? It was OK for a month or so but now is very small? I can increase it in Rich Text mode OK. WLM Menu/View/Text Size - increase the size -- ...winston ms-mvp mail "Maurice" <mplewton@hotmail.com> wrote in message news:C12C3B6C-F663-4AA6-9A6C-B332E26E386F@microsoft.com... > Plain Text format is very small. > How do I increase it ? > It was OK for a month or so but now is very small? > > I can increase it in Rich Text mode OK. > ...

Excel 2003 . . . Can't Delete Corrupted Defined Names
I have an Excel workbook that I created way back when I was using Win 95. I used it for the years I had Win 98, and now I'm using it under Win XP Pro. My problem is that long ago two of the Defined Names apparently became corrupted, and I want to delete them. When I click the Delete option, it goes smoothly, but the names remain unscathed. One name has a definition that refers to a non-existent workbook, and the other contains a #REF! in it. How can I make them go away?? Hi try the following add-in to find/delete these links: http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindLink....

Formula require for complex lookup
Please help I am looking for a formula that will give me the correct Result depending on the Unit & Part I type into the cell The result must pick the highest value with ref to the part and the unit used If I use formula =INDEX($G$24:$G$41,INDEX(MATCH($B$13&$B$15,$B$24:$B$41&$C$24:$C$41,0),0)) Eg. If I select BBB with 10106378 the result should be "J" not "g" If I use formula {=VLOOKUP(MAX(IF(($B$27:$B$43=$B$15)*($C$27:$C$43=B16),$F$27:$F$43)),$F$27:$G$43,2,FALSE)} Eg. If I select BBB with 10106195 the result should be "f" not &...

Search help needed
Access 2003 search form frmSearchDonors will have 1 field "txtSearchPhones" it will also have one button that is meant to open a form with the donor record containing that phone number in any one of its 3 Phone fields (Lets assume for a minute that no phone number can have duplicate donors). Phone fields are: phone_1 phone_2 phone_3 This is what I have but it is not working Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmEditDonor" stLinkCriteria = "[phone_1]=" & "'" & Me![txtSearchPhones] & " &...

another adress for out of office messages
is there a way to specify another email adress only for out of office messages to something like noreply_usermailadress@domain.com ... any configuration ideas or sink scripts out there? H.S. wrote: > is there a way to specify another email adress only for out of office > messages to something like noreply_usermailadress@domain.com ... any > configuration ideas or sink scripts out there? No, not for OOF. It comes from the default address on the mailbox itself. But it replies only once per sender, so you are unlikely to experience mail loops. ...

excel formula 02-15-10
Hi i have this formula =INDEX(Consol!$A$13:$BD$1000,MATCH($C$13,Consol!$A$13:$A$1000,0),MATCH(D2,Consol!$A$11:$BZ$11,0)) i want to use same formula but this time it has to match more than one cell and add. Thanks See your later post, follow-up there. This .newusers group is very well attended to by a lot of responders around the world. If you don't get any response, that probably means responders don't understand what you're after. So you could try explaining/re-wording it better, paste sample data, show before/after scenarios, etc -- Max Singapore --- &...

I need a formula to compare percentage increase or decrease from .
if new value in cell b1, old value in cell a1 % increase/decrease = (b1-a1)/a1 format this cell as -- duan ----------------------------------------------------------------------- duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1162 View this thread: http://www.excelforum.com/showthread.php?threadid=26923 ...

Formula Using Hours and Minutes
How can a formula be constructed using time (in hours). For example: car travels 470 miles in 9hrs26mins. How many miles does it do in 2hours28mins? Dave, A1 holds mileage, A2 holds time, In B1, put =A1/(A2*24) and format as General. This gives the MPH In B2, put =B1*TIME(2,28,0)*24 or =B1*A3*24 if the time of 2:28 is stored in A3, again format as general -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave" <david.woolford@btinternet.com> wrote in message news:019b01c391ab$5a5b16a...

underscores in the server name--affect on BP
Hello: I read somewhere that the server that you use for Business Portal 4.0 should not have underscores in its name. Our internal IT staff set up a server for me to use today to install MOSS, BP, etc. on it. It just dawned on me, though, that this test server has two underscores in its name. Is this really going to be a problem? Is there any sort of remedy? I don't suppose renaming the server is an option, huh? childofthe1980s It's a bug or a fix depending on how you look at it. Check out this link and it should answer your question. My gut tells me if you type in the h...

Where are the sheet tabs in new version?
Just got the new version of Excel (2010) and I opened an older version document. The older version of the document had nine different sheets. Now I can't find the other eight. Any suggestions? C. Presuming you mean Excel 2007? The tabs are at the bottom in 07 just as in 03. "Carrie1104" wrote: > Just got the new version of Excel (2010) and I opened an older version > document. The older version of the document had nine different sheets. Now > I can't find the other eight. Any suggestions? > C. Office button (large round one, top left)>Excel Opt...

How do I exchange data between worksheets?
Hi, I tried to do a data exchange between worksheets, should I use "Macro" or "VB"? To explain it in a a little detail, I got some experimental data ouput and saved on one worksheet (named as "Cent1" under "workbook1"). I want to export these data to another worksheet (named as "sheet1" under "workbook2") to do some calculation. After the calculation and I want the result from "sheet1" exporting to and showing in "Cent1". Ideally I want the calculation in "sheet1" runing behind, which means I don&#...