Named ranges - column/row question

Excel 2003

I have a total sheet that gets data from three different shift sheets (all 
in the same workbook) for a monthly report.  I have named ranges on each 
shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) 
where S1M1 =A2:AC147, S1M2 = A148:AC329, etc.  The old formula 
was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147).  I'm stumped as 
to what the new formula would be - how would I direct Excel to column L of 
S1M1 to search for "BB", then add the contents of column T?  The range 
changes each month, and I thought it would be better to change the name range 
monthly, rather than the cells in 100+ formulas.

TIA
Carole O
0
CaroleO (115)
5/10/2005 5:50:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
819 Views

Similar Articles

[PageSpeed] 9

Hi!

To me:

=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147)

is a lot "simpler" than this:

You can use the INDEX function like this:

 =SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))

The above example also assumes that column L is the first column in the 
named range and column T is the second column in the named range.

If you don't know for sure which column numbers to use then you can 
complicate things even further and use a MATCH function to find the column 
for you:

=SUMIF(INDEX(sim1,,MATCH("name",A1:L1,0)),"BB",INDEX(sim1,,MATCH("time",A1:L1,0)))

Where "name" and "time" are the coulmn headers. "Name" is the column that 
would contain "BB" and "time" is the column that contains the numbers to 
SUMIF.

Sometimes named ranges aren't the best way to go!

Biff

"Carole O" <CaroleO@discussions.microsoft.com> wrote in message 
news:18E75FF1-80BA-429C-92A2-56B409B76E6F@microsoft.com...
> Excel 2003
>
> I have a total sheet that gets data from three different shift sheets (all
> in the same workbook) for a monthly report.  I have named ranges on each
> shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...)
> where S1M1 =A2:AC147, S1M2 = A148:AC329, etc.  The old formula
> was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147).  I'm stumped 
> as
> to what the new formula would be - how would I direct Excel to column L of
> S1M1 to search for "BB", then add the contents of column T?  The range
> changes each month, and I thought it would be better to change the name 
> range
> monthly, rather than the cells in 100+ formulas.
>
> TIA
> Carole O 


0
biffinpitt (3172)
5/10/2005 6:18:32 PM
Thanks, Biff -

I added another left paren to the Index function, and it works perfectly.

Just what I needed!!

Carole O
"Biff" wrote:

> Hi!
> 
> To me:
> 
> =SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147)
> 
> is a lot "simpler" than this:
> 
> You can use the INDEX function like this:
> 
>  =SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
> 
> The above example also assumes that column L is the first column in the 
> named range and column T is the second column in the named range.
> 
> If you don't know for sure which column numbers to use then you can 
> complicate things even further and use a MATCH function to find the column 
> for you:
> 
> =SUMIF(INDEX(sim1,,MATCH("name",A1:L1,0)),"BB",INDEX(sim1,,MATCH("time",A1:L1,0)))
> 
> Where "name" and "time" are the coulmn headers. "Name" is the column that 
> would contain "BB" and "time" is the column that contains the numbers to 
> SUMIF.
> 
> Sometimes named ranges aren't the best way to go!
> 
> Biff
> 
> "Carole O" <CaroleO@discussions.microsoft.com> wrote in message 
> news:18E75FF1-80BA-429C-92A2-56B409B76E6F@microsoft.com...
> > Excel 2003
> >
> > I have a total sheet that gets data from three different shift sheets (all
> > in the same workbook) for a monthly report.  I have named ranges on each
> > shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...)
> > where S1M1 =A2:AC147, S1M2 = A148:AC329, etc.  The old formula
> > was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147).  I'm stumped 
> > as
> > to what the new formula would be - how would I direct Excel to column L of
> > S1M1 to search for "BB", then add the contents of column T?  The range
> > changes each month, and I thought it would be better to change the name 
> > range
> > monthly, rather than the cells in 100+ formulas.
> >
> > TIA
> > Carole O 
> 
> 
> 
0
CaroleO (115)
5/10/2005 7:02:11 PM
>I added another left paren to the Index function

Don't know why you would have needed to do that!

Anyhow, glad to be of help! Thanks for the feedback.

Biff

"Carole O" <CaroleO@discussions.microsoft.com> wrote in message 
news:362F0F96-1DA4-41B5-B68A-DBBDFCACA542@microsoft.com...
> Thanks, Biff -
>
> I added another left paren to the Index function, and it works perfectly.
>
> Just what I needed!!
>
> Carole O
> "Biff" wrote:
>
>> Hi!
>>
>> To me:
>>
>> =SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147)
>>
>> is a lot "simpler" than this:
>>
>> You can use the INDEX function like this:
>>
>>  =SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
>>
>> The above example also assumes that column L is the first column in the
>> named range and column T is the second column in the named range.
>>
>> If you don't know for sure which column numbers to use then you can
>> complicate things even further and use a MATCH function to find the 
>> column
>> for you:
>>
>> =SUMIF(INDEX(sim1,,MATCH("name",A1:L1,0)),"BB",INDEX(sim1,,MATCH("time",A1:L1,0)))
>>
>> Where "name" and "time" are the coulmn headers. "Name" is the column that
>> would contain "BB" and "time" is the column that contains the numbers to
>> SUMIF.
>>
>> Sometimes named ranges aren't the best way to go!
>>
>> Biff
>>
>> "Carole O" <CaroleO@discussions.microsoft.com> wrote in message
>> news:18E75FF1-80BA-429C-92A2-56B409B76E6F@microsoft.com...
>> > Excel 2003
>> >
>> > I have a total sheet that gets data from three different shift sheets 
>> > (all
>> > in the same workbook) for a monthly report.  I have named ranges on 
>> > each
>> > shift sheet for each month's production data (S1M1, S1M2, S1M3, 
>> > S2M1...)
>> > where S1M1 =A2:AC147, S1M2 = A148:AC329, etc.  The old formula
>> > was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147).  I'm 
>> > stumped
>> > as
>> > to what the new formula would be - how would I direct Excel to column L 
>> > of
>> > S1M1 to search for "BB", then add the contents of column T?  The range
>> > changes each month, and I thought it would be better to change the name
>> > range
>> > monthly, rather than the cells in 100+ formulas.
>> >
>> > TIA
>> > Carole O
>>
>>
>> 


0
biffinpitt (3172)
5/10/2005 11:37:17 PM
I bet Carole was writing about this one:

=SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
that became:
=SUMIF((INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
or removing the extra ")"
=SUMIF(INDEX(sim1,,1),"BB",INDEX(sim1,,2))




Biff wrote:
> 
> >I added another left paren to the Index function
> 
> Don't know why you would have needed to do that!
> 
> Anyhow, glad to be of help! Thanks for the feedback.
> 
> Biff
> 
> "Carole O" <CaroleO@discussions.microsoft.com> wrote in message
> news:362F0F96-1DA4-41B5-B68A-DBBDFCACA542@microsoft.com...
> > Thanks, Biff -
> >
> > I added another left paren to the Index function, and it works perfectly.
> >
> > Just what I needed!!
> >
> > Carole O
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> To me:
> >>
> >> =SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147)
> >>
> >> is a lot "simpler" than this:
> >>
> >> You can use the INDEX function like this:
> >>
> >>  =SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
> >>
> >> The above example also assumes that column L is the first column in the
> >> named range and column T is the second column in the named range.
> >>
> >> If you don't know for sure which column numbers to use then you can
> >> complicate things even further and use a MATCH function to find the
> >> column
> >> for you:
> >>
> >> =SUMIF(INDEX(sim1,,MATCH("name",A1:L1,0)),"BB",INDEX(sim1,,MATCH("time",A1:L1,0)))
> >>
> >> Where "name" and "time" are the coulmn headers. "Name" is the column that
> >> would contain "BB" and "time" is the column that contains the numbers to
> >> SUMIF.
> >>
> >> Sometimes named ranges aren't the best way to go!
> >>
> >> Biff
> >>
> >> "Carole O" <CaroleO@discussions.microsoft.com> wrote in message
> >> news:18E75FF1-80BA-429C-92A2-56B409B76E6F@microsoft.com...
> >> > Excel 2003
> >> >
> >> > I have a total sheet that gets data from three different shift sheets
> >> > (all
> >> > in the same workbook) for a monthly report.  I have named ranges on
> >> > each
> >> > shift sheet for each month's production data (S1M1, S1M2, S1M3,
> >> > S2M1...)
> >> > where S1M1 =A2:AC147, S1M2 = A148:AC329, etc.  The old formula
> >> > was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147).  I'm
> >> > stumped
> >> > as
> >> > to what the new formula would be - how would I direct Excel to column L
> >> > of
> >> > S1M1 to search for "BB", then add the contents of column T?  The range
> >> > changes each month, and I thought it would be better to change the name
> >> > range
> >> > monthly, rather than the cells in 100+ formulas.
> >> >
> >> > TIA
> >> > Carole O
> >>
> >>
> >>

-- 

Dave Peterson
0
ec357201 (5290)
5/10/2005 11:43:03 PM
That's the one!  I'm so grateful for this forum!  No one else at work does 
what I do with Excel, so it's nice to have someone to ask.

Carole O

"Dave Peterson" wrote:

> I bet Carole was writing about this one:
> 
> =SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
> that became:
> =SUMIF((INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
> or removing the extra ")"
> =SUMIF(INDEX(sim1,,1),"BB",INDEX(sim1,,2))
> 
> 
> 
> 
> Biff wrote:
> > 
> > >I added another left paren to the Index function
> > 
> > Don't know why you would have needed to do that!
> > 
> > Anyhow, glad to be of help! Thanks for the feedback.
> > 
> > Biff
> > 
> > "Carole O" <CaroleO@discussions.microsoft.com> wrote in message
> > news:362F0F96-1DA4-41B5-B68A-DBBDFCACA542@microsoft.com...
> > > Thanks, Biff -
> > >
> > > I added another left paren to the Index function, and it works perfectly.
> > >
> > > Just what I needed!!
> > >
> > > Carole O
> > > "Biff" wrote:
> > >
> > >> Hi!
> > >>
> > >> To me:
> > >>
> > >> =SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147)
> > >>
> > >> is a lot "simpler" than this:
> > >>
> > >> You can use the INDEX function like this:
> > >>
> > >>  =SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
> > >>
> > >> The above example also assumes that column L is the first column in the
> > >> named range and column T is the second column in the named range.
> > >>
> > >> If you don't know for sure which column numbers to use then you can
> > >> complicate things even further and use a MATCH function to find the
> > >> column
> > >> for you:
> > >>
> > >> =SUMIF(INDEX(sim1,,MATCH("name",A1:L1,0)),"BB",INDEX(sim1,,MATCH("time",A1:L1,0)))
> > >>
> > >> Where "name" and "time" are the coulmn headers. "Name" is the column that
> > >> would contain "BB" and "time" is the column that contains the numbers to
> > >> SUMIF.
> > >>
> > >> Sometimes named ranges aren't the best way to go!
> > >>
> > >> Biff
> > >>
> > >> "Carole O" <CaroleO@discussions.microsoft.com> wrote in message
> > >> news:18E75FF1-80BA-429C-92A2-56B409B76E6F@microsoft.com...
> > >> > Excel 2003
> > >> >
> > >> > I have a total sheet that gets data from three different shift sheets
> > >> > (all
> > >> > in the same workbook) for a monthly report.  I have named ranges on
> > >> > each
> > >> > shift sheet for each month's production data (S1M1, S1M2, S1M3,
> > >> > S2M1...)
> > >> > where S1M1 =A2:AC147, S1M2 = A148:AC329, etc.  The old formula
> > >> > was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147).  I'm
> > >> > stumped
> > >> > as
> > >> > to what the new formula would be - how would I direct Excel to column L
> > >> > of
> > >> > S1M1 to search for "BB", then add the contents of column T?  The range
> > >> > changes each month, and I thought it would be better to change the name
> > >> > range
> > >> > monthly, rather than the cells in 100+ formulas.
> > >> >
> > >> > TIA
> > >> > Carole O
> > >>
> > >>
> > >>
> 
> -- 
> 
> Dave Peterson
> 
0
CaroleO (115)
5/11/2005 1:40:12 PM
Reply:

Similar Artilces:

Inserting a Row from a UserForm
Hi guys- I'm kind of a self-taught VBA programmer and I can usually blunder around and get a macro to work, but I'm having trouble with a rather difficult one right now. I've created a UserForm that has 6 fields the user has to fill in for a database. Once the user hits the "OK" button, I want the macro to look for the last entry in the database that is identical to what the user filled in Field 1 of the UserForm and insert a row beneath it and fill in the cells with the data from the UserForm. So, i.e: Apple Apple Orange Orange Orange Banana Grape Grape If I type &...

DoModal() question.
I have a dialog class dlg. When I use dlg.DoModal(), there're always two buttons "Cancel" and "Ok" on popup window which I didn't put there. They seem to be put there by system default. How to remove them? Thanks. Frank E Rogers wrote: > I have a dialog class dlg. When I use dlg.DoModal(), there're always two > buttons "Cancel" and "Ok" on popup window which I didn't put there. They > seem to be put there by system default. How to remove them? > Thanks. > > First, you better add message handlers for them. Your ha...

InPlace ugrade from Exchange 2000 to 2003 recovery question
Hi, Will shortly be doing an inplace upgrade from Exchange 2000 enterprise edition to Exchange 2003 Enterprise edition. After we do all the steps ie. Forestprep/domainprep etc then do the actual upgrade if the upgrade fails how do we roll back. We use Brightstor Arcserve version 11 which is supported by Exchange 2000 and we backup doing the Full method not bricks level. I have documentation on how to restore Exchange using this but this just restores the database to my knowledge (never had to do it) with exchange 2000 so how do I roll back a failed upgrade. We have to do an inplace...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

What does #REF! mean in the Name Manager
I have a 2007 workbook with 6-7 sheets. I made some changes to cell names and apparently made some mistakes. When I open the Name Manager, the last entry looks like this: Name Value Refers to Scope Comments x #REF! =#REF!$C$25 Workbook I cannot find that name anywhere. I have looked in C25 on every sheet. What does this mean? >Name Value Refers to Scope Comments > x #REF! =#REF!$C$25 Workbook #REF! means it's an invalid reference. Did you delete a sheet? If so, that's probably why you're getting those. -- Biff Micro...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

Page Number in Repeat Rows area
Is there a way that I can put a Page Number in the Repeat Rows area of a sheet - and get it to update when I print? I have found one piece of VBA that puts a page number within a cell but it is only updated if the cell is outside the Repeat Row area. When it is within the Repeat Rows area I just get a page number of 1 corresponding to the original row location.. I do some VBA programming but am not an expert. TIA cheers Chris Nothing comes to mind that doesn't require VBA code. You can paste the following in the sheet header (File | Page Setup) and page x of y will print on ea...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at the end.. is there a function/formula I can use to remove them if it ends in A or B? For example (my list): Accounting Sr Mgr B Accounts Payable Sr Mgr B Ambulatory Plng Sr Prog Dir A Need it to look like this: Accounting Sr Mgr Accounts Payable Sr Mgr Ambulatory Plng Sr Prog Dir Thanks! This will get rid of the A or B at the end along with the space before it. Assuming the value is in A5: =IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5) kvc wrote: > Hi, I have a list of titles a...

simple xss question
Hello, One thing I dont understand about XSS: 1.There is a page with a text box 2. I inject some Javascript into that textbox that shows a form in a new div that can send content somewhere. Am I not the only one that sees this form? What good is it if the next person that goes to the website just sees the page sent from the server correctly? Thanks Its usually more of a problem when they get access to your filesystem or database ... As i've found out the hard way On 16/02/2010 14:51, in article 49e2f337-0f2c-46f1-87d4-b58b1275ef40@f17g2000prh.googlegroups.com, "...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

How do insert name automatically atop of each pg of doc?
Hello, How do I automatically insert name on each page of document like inserting page numbers? My professor told me not to type it in but to insert and I have not been able to do so. This is a new program for me, Word 2007. I have to submit my paper in MLA form and do not wish to lose points because for this. Thanks for your help. Click on the Insert tab of the Ribbon and then in the Header and Footer section, click on Header and make your choice from the options presented or click on the Edit Header button to get a blank header pane in which to insert the name in the...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

Question Re: Migration of Exchange to New Server.
I'm in the process of replacing an aging Exchange server with a brand new server. Once complete the old server will be taken off line and recycled. I figure the easiest way to do this is bring the new server up as a second Exchange server within the domain and then move the mail boxes from the old server to the new server but I have a couple of questions. 1) How do I move the public folders? I'm assuming I can setup Replication between the two servers and that will migrate the public folder data. 2) What happens when the current server goes off line? Other then having to upd...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

column value translation
I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

insert a user's name/id
I am aware of functions like =TODAY() which will automatically insert today's date into a cell. I was wondering if something similar existed for user ID's/name. When you create a new spreadsheet it bears the name of the user who first created it in the properties. Is there a function I can put in a cell that will show the user name/id who has opened or last mondified the file (for example)? Thanks. not that i know of but you can do it with code. here is some code we use in some of our files to keep a record of who opened a file, when and how long they were in it. to add cod...

Changing of range (Address) to (Cell)
Hi people I have encounter a problem with the use of range From a recorded macro, it's listed this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2,A24:M28"), PlotBy:=xlRow And I edit it to this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2," & Cells(StartX, StartY), Cells(LastX, LastY)), PlotBy:=xlRow And obviously VBA compiler won't let me go this easily, it happen to give an "evil-comment" on my source range, May i know how can i solve this Thank You Hi Kaiyang, Try this, assuming the...

How do i highlight the current row for data input
How do i highlight the current row for data input. Mike, Can you provide a little more detail about what you want to do? Your question isn't very clear. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "mike" <msclafani@massbay.edu> wrote in message news:04fe01c36129$81bfa600$a501280a@phx.gbl... > How do i highlight the current row for data input. Mike, If by highlight, you mean change the color of the current row as the user moves about, or something like that, it can be done via a ma...

Trendline Question
Hello, I have a chart from a simple data set that plots a new point each day. It's my blood p vs date. I also have it show on the chart a "Trendline" linear fit, which I guess is the rms of the data points. Works just fine. Question: Is it possible to have the Trendlinebe computed for, and show on the chart for, Only a particular range of dates in the data, rather than the for the whole data set ? e.g., if my data goes from Sept, 2006 to the present, can i just have the Trendline show for the Jan 2007 to the present data points ? How, please ? Thanks, Bob Hi, Ye...