Same Code, Different Cells

I have just coded a user interface in the guise of a few forms for a
spreadsheet I've created.

The spreadsheet has the same table duplicated 27 times all the way
down it but I have only coded the forms (user interface) to work for
the first table on my spreadsheet and it took ages to give
instructions for each cell etc.

Is there anyway I can duplicate my code for the rest of my tables (in
the same sheet) easily? ie can I duplicate the code but change the
cells that are referred to in that code to suit the other tables? I
know I can copy and paste but I'd still have to go through it all and
change the cell numbers. I was hoping to just use one cell as a start
ref point and the other cell refs will automatically change.

Let me show a grab of my sheet
http://www.geocities.com/coope1999/excel1.jpg

Notice I'd like to use for example cell b40 as the ref point for table
one and b76 as the ref for table 2 and so on.

Any ideas?

Cheers....

Craig...


--
The Zero ST
0
coope1999 (41)
3/17/2007 11:45:59 PM
excel 39879 articles. 2 followers. Follow

11 Replies
641 Views

Similar Articles

[PageSpeed] 25

Craig,

You can give each table a name (menu Insert>Name; i've the Dutch version and 
don't know the exact menu names in the English version) and refere to the 
cells within the table relative to the table:

say Range("A39:K69") is called Table_01
then you can  refere to A39 with
Range("Table_01").Range("A1")

so only changing the name of the table will do.

You can use a combobox (cboTableNumber) with the numbers 1 to 27
choose a number and you can refere to Range("A1") within the chosen table 
with:

Range("Table_" & Me.cboTableNumber).Range("A1")

Hope this will do the job for you.l

Jan


0
jg8091 (25)
3/18/2007 10:50:48 AM
Craig,

An other possibillity is more like you suggested: "I was hoping to just use 
one cell as a start
ref point and the other cell refs will automatically change"

Range("A39").Range("A1") refers to Range("A39")
Range("A39").Range("C2") refers to Range("C41")

So, use a reference-cell for each table instead of giving a name to each 
table.

You could fill the combobox I mentioned in my previous message, with the 
addresses of the reference-cells for each table or still fill it with the 
numbers 1 to 27 and make a translation to the reference-cells within the 
code.

Jan




"jan" <jg@releerf.nl> schreef in bericht 
news:ujHxBtUaHHA.4772@TK2MSFTNGP05.phx.gbl...
> Craig,
>
> You can give each table a name (menu Insert>Name; i've the Dutch version 
> and don't know the exact menu names in the English version) and refere to 
> the cells within the table relative to the table:
>
> say Range("A39:K69") is called Table_01
> then you can  refere to A39 with
> Range("Table_01").Range("A1")
>
> so only changing the name of the table will do.
>
> You can use a combobox (cboTableNumber) with the numbers 1 to 27
> choose a number and you can refere to Range("A1") within the chosen table 
> with:
>
> Range("Table_" & Me.cboTableNumber).Range("A1")
>
> Hope this will do the job for you.l
>
> Jan
>
> 


0
jg8091 (25)
3/18/2007 11:08:22 AM
On Sun, 18 Mar 2007 12:08:22 +0100, "jan" <jg@releerf.nl> wrote:

>Craig,
>
>An other possibillity is more like you suggested: "I was hoping to just use 
>one cell as a start
>ref point and the other cell refs will automatically change"
>
>Range("A39").Range("A1") refers to Range("A39")
>Range("A39").Range("C2") refers to Range("C41")
>
>So, use a reference-cell for each table instead of giving a name to each 
>table.
>
>You could fill the combobox I mentioned in my previous message, with the 
>addresses of the reference-cells for each table or still fill it with the 
>numbers 1 to 27 and make a translation to the reference-cells within the 
>code.
>
>Jan

Thanks for the advice Jan but I am still struggling to get my head
around it. I'm going to paste you a bit of code.


If ToggleButton1.Value = True And ComboBox1 = "HIN" And Range("b40") =
0 Then
    Range("b40") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b40") > 0 And Range("b46") = 0 Then
    Range("b46") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b46") > 0 And Range("b52") = 0 Then
    Range("b52") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b52") > 0 And Range("b58") = 0 Then
    Range("b58") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" And
Range("b58") > 0 And Range("b64") = 0 Then
    Range("b64") = Time()
End If

Now you don't have to understand what the code does, I just want to
give my example. This is a very small part of the code but for the
second table in my sheet it would look exactly the same apart from the
cells would be something like b76 instead of b40 and b82 instead of
b46, but the amount differences between the cells will always be the
same. I hope that makes sense.

So somehow I want to capture the above code by somehow putting
brackets around the code and then naming it code1 or something like
that. I'm sure that is not how it is done but I really don't know how
to do it.

Then hopefully write a simple line suchas

table2 = code1 (but with different cell numbers.) I wish it was that
easy.

Hope you can help further...

Thanks again,

Craig...


--
The Zero ST
0
coope1999 (41)
3/19/2007 9:40:36 PM
Craig

For the first table you can use:

With Worksheet("Sheets1").Range("A39")
    If ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") = 0 Then
        .Range("b2") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") > 0 And .Range("b8") = 0 Then
        .Range("b8") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b8") > 0 And .Range("b14") = 0 Then
        .Range("b14") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b14") > 0 And .Range("b20") = 0 Then
        Range("b20") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b20") > 0 And .Range("b26") = 0 Then
        .Range("b26") = Time()
    End If
End With

So changing all ranges by subtracting  38 and put a dot in front of the word 
Range.

The second table you could access with the same code but with Range("A75") 
as reference:

With Worksheet("Sheets1").Range("A75")
    If ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") = 0 Then
        .Range("b2") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") > 0 And .Range("b8") = 0 Then
        .Range("b8") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b8") > 0 And .Range("b14") = 0 Then
        .Range("b14") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b14") > 0 And .Range("b20") = 0 Then
        Range("b20") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b20") > 0 And .Range("b26") = 0 Then
        .Range("b26") = Time()
    End If
End With

etc.

But it would be nicer if you had a combobox (cboRef) in your form with the 
addresses of the reference ranges to choose.
A39
A75
etc.

and then write the code:

With Worksheet("Sheets1").Range(Me.cboRef)
    If ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") = 0 Then
        .Range("b2") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") > 0 And .Range("b8") = 0 Then
        .Range("b8") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b8") > 0 And .Range("b14") = 0 Then
        .Range("b14") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b14") > 0 And .Range("b20") = 0 Then
        Range("b20") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b20") > 0 And .Range("b26") = 0 Then
        .Range("b26") = Time()
    End If
End With

Depending on the choice in this combobox  your Userform writes/reads the 
corresponding table.

I hope this explains things a bit.

Jan




0
jg8091 (25)
3/19/2007 10:18:56 PM
Craig,

Of course I made a mistake:

Range("b20") = Time() misses the dot and should be:
..Range("b20") = Time()

Jan



0
jg8091 (25)
3/19/2007 10:34:10 PM
On Mon, 19 Mar 2007 23:34:10 +0100, "jan" <jg@releerf.nl> wrote:

>Craig,
>
>Of course I made a mistake:
>
>Range("b20") = Time() misses the dot and should be:
>.Range("b20") = Time()
>
>Jan
>
>

Cheers Jan...I understand it now I think...

I'll give it a try when I have a spare minute.

Craig...

--
The Zero ST
0
coope1999 (41)
3/20/2007 9:39:20 AM
----- Original Message -----
From: "jan" <jg@releerf.nl>
Newsgroups: microsoft.public.excel
Sent: Monday, March 19, 2007 10:18 PM
Subject: Re: Same Code, Different Cells

>
> But it would be nicer if you had a combobox (cboRef) in your form with the
> addresses of the reference ranges to choose.
> A39
> A75
> etc.
>
> and then write the code:
>
> With Worksheet("Sheets1").Range(Me.cboRef)
>     If ToggleButton1.Value = True And ComboBox1 = "HIN" _
>             And .Range("b2") = 0 Then
>         .Range("b2") = Time()
>     ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
>             And .Range("b2") > 0 And .Range("b8") = 0 Then
>         .Range("b8") = Time()
>     ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
>             And .Range("b8") > 0 And .Range("b14") = 0 Then
>         .Range("b14") = Time()
>     ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
>             And .Range("b14") > 0 And .Range("b20") = 0 Then
>         Range("b20") = Time()
>     ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
>             And .Range("b20") > 0 And .Range("b26") = 0 Then
>         .Range("b26") = Time()
>     End If
> End With
>
> Depending on the choice in this combobox  your Userform writes/reads the
> corresponding table.
>
> I hope this explains things a bit.
>
> Jan

Thanks again for your help Jan. I got the first example to work but like you
said using a combobox would me much better as it would mean I would have to
store less code. I've created the combobox on a different form and have
tried several different ways to write the syntax but I can't get it to go..

I have

With Sheet1.Range(UserForm1.ComboBox1.Value)
    If ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") = 0 Then
        .Range("b2") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b2") > 0 And .Range("b8") = 0 Then
        .Range("b8") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b8") > 0 And .Range("b14") = 0 Then
        .Range("b14") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b14") > 0 And .Range("b20") = 0 Then
        .Range("b20") = Time()
    ElseIf ToggleButton1.Value = True And ComboBox1 = "HIN" _
            And .Range("b20") > 0 And .Range("b26") = 0 Then
        .Range("b26") = Time()
    End If
End With

It is just the first line of code I need help with. I've put in the address
of my combobox (which will have the ref a39 already in it) but I'm getting
errors. What is the correct syntax to refer to the ref in my combobox?

I hope that that is all I need to know :o)

Craig...


0
coope1999 (41)
3/20/2007 10:05:09 PM
Craig,

With Sheet1.Range(UserForm1.ComboBox1.Value)

 should be something like:

With Worksheet("Sheets1").Range(UserForm1.ComboBox1.Value)

and the property ShowModal from UserForm1 should be False.

But why not have the combobox with the ranges on the same UserForm.
I think its much easyer to use and I don't sea the advantages of the second 
Userform.

Jan


0
jg8091 (25)
3/21/2007 6:41:47 AM
"jan" <jg@releerf.nl> wrote in message
news:u9Ur#P4aHHA.588@TK2MSFTNGP06.phx.gbl...
> Craig,
>
> With Sheet1.Range(UserForm1.ComboBox1.Value)
>
>  should be something like:
>
> With Worksheet("Sheets1").Range(UserForm1.ComboBox1.Value)
>
> and the property ShowModal from UserForm1 should be False.
>
> But why not have the combobox with the ranges on the same UserForm.
> I think its much easyer to use and I don't sea the advantages of the
second
> Userform.
>

Sorry Jan...I still get the same error message..

I have moved my cmbo to the same form and have got the following

With Worksheet.("Sheet1").Range(Me.ComboBox2.Value)

I have tried changing sheet1 to sheets1 and the same with worksheet but I
get object not defined or subfunction out of range....but I'm sure that the
latter half of the code line is correct ".Range(Me.ComboBox2.Value)"
It is called combobox 2 now because it is the second on that form.

With your code after I have changed the address of my cmbo I get

With Worksheet("Sheets1").Range(Me.ComboBox2.Value)

but when I run the code it says function or subfunction not defined and it
highlights "Worksheet" in blue so I assume there is something wrong with
that part of the address...I have not changed the names of any of my sheets
in Excel...

Any ideas?

Thanks again for your time....



0
coope1999 (41)
3/21/2007 10:02:26 AM
Craig,

It's a little mistake:

In

With Worksheet("Sheets1").Range(Me.ComboBox2.Value)

the s after Worksheet is missing (I gave it to you in this way and that 
wasn't right)

it has to be:

With Worksheets("Sheets1").Range(Me.ComboBox2.Value)

where Sheets1 is the name of the sheet (or it has to be Sheet1?)

Jan



0
jg8091 (25)
3/21/2007 12:32:49 PM
"jan" <jg@releerf.nl> wrote in message
news:OeAfIU7aHHA.4872@TK2MSFTNGP03.phx.gbl...
> Craig,
>
> It's a little mistake:
>
> In
>
> With Worksheet("Sheets1").Range(Me.ComboBox2.Value)
>
> the s after Worksheet is missing (I gave it to you in this way and that
> wasn't right)
>
> it has to be:
>
> With Worksheets("Sheets1").Range(Me.ComboBox2.Value)
>
> where Sheets1 is the name of the sheet (or it has to be Sheet1?)
>
> Jan

Thanks for all your help Jan, Looks like I have all I need to finish my
project....


0
coope1999 (41)
3/22/2007 9:26:42 AM
Reply:

Similar Artilces:

Duplicating a workbook only with cell's values
Hello every body, I have a complex workbook. It it is becaming a little bit difficul to work into it. It is possible to do a copy of this workbook but thi copy should have only cell�s values not cell�s formulas. As I said thi workbook is complex to replecate sheet by sheet to another workbook an do the paste especial process. thank you in advanc -- Message posted from http://www.ExcelForum.com Hi Adrix There is no option to save it like this Try this macro on a copy of your workbook Sub test() Worksheets.Select Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).S...

Keeping the Same Cell References
What's the easiest way of copying a cell with a formula in it and pasting it into another cell with the exact same formula? I also have ASAP utlities, and I couldn't figure out how to do it with that either. One way: Remove the = sign before copying and re-insert it in the new location after pasting -- Kind regards, Niek Otten Microsoft MVP - Excel "JP" <JohnP26@msn.com> wrote in message news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com... | What's the easiest way of copying a cell with a formula in it and | pasting it into another cell with the exact same for...

Re: Find last cell in a column, Delete its contents and make it ac
Sub uselastcellinL() Application.Goto Cells(Rows.Count, "L").End(xlUp) ActiveCell.ClearContents End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Don Guillett" <dguillett1@gmail.com> wrote in message news:... > Sub deletelastcellinL() > Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp > End Sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "George" <George@discussions.microsoft.com> wrote in message > news:D...

Cell Reference Formula Outside Workbook
I am trying to reference a cell outside of my current spread sheet. I am using a cell in my current sheet as an input for the extension of the file I would like to look in. So I have this defined as LINK. And the value of link is something like C:\\Myfolder\ready.xls I am trying to set up a formula in my sheet that will use this reference to the cell on the first page, which I defined as LINK so when I change the value of the cell all my values will update to the new folder and document, but retain their previous page, column and row references. For example. My current formula reads [...

Code pour suppression module
Bonsoir tout le monde. Pour des besoins de maintenance d'une petite appli qui tourne sous Excel j'aimerais pouvoir par code supprimer un module et le remplacer par un autre. Es ce possible???? Par avance merci pour votre aide. http://www.cpearson.com/excel/vbe.htm -- Kind regards, Niek Otten "tip.tiptop" <tip.tiptop@free.fr> wrote in message news:4419d838$0$20837$636a55ce@news.free.fr... > Bonsoir tout le monde. > Pour des besoins de maintenance d'une petite appli qui tourne sous Excel j'aimerais pouvoir par code supprimer un module et le >...

Altered Ship Country Code Not transfering
Ok, long drawn out case I have been working with Microsoft to no success. When we alter the ship to address details, the country code and country do not move along with the order to backorder/reorder or to invoice. Microsoft has "confirmed" yep, it happens on your system. But they are blaming it on modified forms. I have only modified certain fields to make them "required" so data entry folks put in details we require for our business processes. Microsoft can not, will not, determine what is wrong with the forms file itself or which requirement is causing the ...

Printing different Worksheets to PDF
Is it possible to print selected worksheets to one single PDF file? More specifically, can I print pages 1 and 3 from 10 differen worksheets into one single PDF? Please help the noob. Le -- SirLoi ----------------------------------------------------------------------- SirLoin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1593 View this thread: http://www.excelforum.com/showthread.php?threadid=27413 ...

count cell with background color 36
First I want to count the number of cells in a range that have text in them. Then I want to count how many of these cells have the back ground color 36 Oldjay This will achieve what you want for a continuous range, Column A in this example. Change the variables to suit. Take care Marcus Option Explicit Sub MyCount() Dim lw As Integer Dim counter As Integer Dim MyConstant As Integer Dim i As Integer lw = Range("A" & Rows.Count).End(xlUp).Row counter = 0 MyConstant = Range("A2" & lw).SpecialCells(xlCellTypeConstants).Count MsgBox "The...

Ooh .. Linking a list to a list to an output cell
I have a single cell in which i enter the price of a home. Elsewhere, I have two columns of ten items each: one column is formulated to give relative prices based on the price entered in the single cell, and the column adjacent to this column describes these numbers in nominal terms (i.e. ten items in increments of five percent: "25% more" .. " 0% change" .. "25% less"). Further along, I have a list box created which uses the nominal column at the Source for the list. I would like to have a cell adjacent to the list box cell which will give the price b...

Named range(s) of non-adjacent cells return #VALUE! error in array formulas
I have ranges made up of non-adjacent cells from one worksheet and I get a #VALUE! error when I try to do any conditional count or sum calculation with an array formula on either or both of them. Each range is a selection of 32 non-adjacent cells from a single column. The cells contain array formulas that return percentages. Example of range values I8: 5.9% I11: 12.1% I14: 22.3% I17: 0.0% I was able to get values returned from simple functions like Max and Min but the following example gives me the #VALUE! error: {=SUM(IF((Rng>0),1,0))} =COUNTIF(Rng,"<.0125") Any wisdom ...

VLOOKUP from different sheets
Hi, I am trying to VLOOUP from other sheets. Exemple: I have 3 diffrent recipec in 3 different sheets in column A the weight value and in B the description of ingredient. In sheet 4 I want to have a list in column A of all ingredients used in all 3 sheets and in column B the number of the calculated values I can Email a Excel flie pinponting the question See reply in .misc Biff >-----Original Message----- >Hi, >I am trying to VLOOUP from other sheets. >Exemple: >I have 3 diffrent recipec in 3 different sheets in column A the weight value >and in B the descriptio...

Comparing columns for duplicates/differences
I have a spreadsheet that contains 2 columns of information containing numbers. It was created by the query function from my AS400, saved in an excel spreadsheet, sent to a 3rd party for verification and then returned in a comma delimited format with the updated information (the first column being the info I provided, the second being the info that was returned). I then saved the worksheet in an excel format before running the test. I've tried comparing the information for discrepancies using the 'Worksheet Formula' method without success. I have also determined that i...

Pls Help me for install different version office on one computer
As I have old version of account software I must keep MSWord 2000 & MSExcel 2000 on my computer. However,I only get license of standard version of office(without access 2000) I try to install office office XP(with access xp) on my computer. So I install office xp (choose access xp only) and I keep excel 2000 & word 2000 on the computer. However I found that I can't use " export" function to excel2000 on access xp. when I do it so,the computer become hang and no response.and then I can't open excel again. Thx help me for doing so I don't know if this will hel...

Font change on a different computer
I create a Word document using Verdana font. When I send it to another person they tell me the font is converting to Verdan Lantin. Any ideas why this would happen? -- Thanks...John Sorry...this is a duplicate question. Please disregard. -- Thanks...John "John" wrote: > I create a Word document using Verdana font. When I send it to another > person they tell me the font is converting to Verdan Lantin. Any ideas why > this would happen? > -- > Thanks...John ...

Locking cells #7
Is it possible to lock the cells with conditio. I want to lock all the cells after one week from the certain date on the sheet. for Ex. $A$23 cell have the date 15 Aug 2005 and i want to lock the cell $C$8 to $H$23 after one week from that date. so that no one can change the data. Thanks in advance By default all cells are locked. You would need to unlock all desired and then have a macro to lock the desired cells. Sub lockcells() Range("b1:d12").Locked = False If Date > Range("a4") + 15 Then Range("b1:b4").Locked = True End Sub -- Don Guillett SalesA...

Licence code
I want to install CRM 1.2 but it asks me for a Licence code. When I look in the readme file on the cd-rom it sends me to www.greatplains.com. This site is not active and I have only the cd-key Can you give me information about how to aquire a licence key? Hi Albert, You might want to check out the Microsoft Business Solutions website: http://www.microsoft.com/BusinessSolutions/howtobuy_overview.aspx Regards Rob Bakkers, Avanade Netherlands "Albert van Boerum" wrote: > I want to install CRM 1.2 but it asks me for a Licence code. When I look in > the readme file on the ...

Search Code Problem
I have a search that I would like to have run from three tables. 'strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID, tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo, tblListNos.MainListNo1, tblListNos.MStatus, tblListNos.ProjectID, tblHouses.Strata, tblHouses.Inactive, tblHouses.FirstOfPostalCode, tblHouses.FirstOfHouseNumber, tblHouses.FirstOfRoadName, tblHouses.LotNo, tblHouses.Telephone1, tblHouses.Telephone2, tblListNos.MPriority, tblListNos.MaintenanceComments, tblListNos.MainIssuedDate " _ '& "FROM (tblHouses LEFT JOIN tblListNos ON tblHouses.R...

"Not in list" event code
Hello everyone: I'm having problems with the below code for adding combo box input if input is not' in the list. What are the variables here that I must change the names of, etc...? I've tried a couple things but was not successful. Thank you! ********Code Start****************** Option Compare Database Private Sub Combo54_NotInList(NewData As String, Response As Integer) Dim db As DAO.Database Dim rs As DAO.Recordset Dim strMsg As String strMsg = "'" & NewData & "' is not an available Document Number Name " & vbCrLf & vbC...

stop rounding in cell
i want the numbers in my cells to show as they are without being rounded but to show in thousands. I want to maintain integrity. Eg. 97,654 should just show as 97. i don't want it to round up or down. is this possible? Hi Try =INT(A1/1000) but note anything less than 1000 will show as 0. Regards Roger Govier Lady112017 wrote: > i want the numbers in my cells to show as they are without being rounded but > to show in thousands. I want to maintain integrity. Eg. 97,654 should just > show as 97. i don't want it to round up or down. is this possible? Try this formula: ...

view cell content in header
Hi folks Can I customise the header so the left section = the content of a cell in the sheet eg A1. All I seem to be able to do is add date, page etc etc.. thanks john Images of home (NZ) http://www.titahi-bay.co.nz/home What we are up to in the UK http://www.titahi-bay.co.nz You need some VBA code to do this: You can use the workbook_beforeprint event to modify the header. Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) With Me.Worksheets("sheet1") .PageSetup.LeftHeader = .Range("a1").Text End With End Sub This goes behind the ThisW...

Click Entering Absolute Cell References into a Formula
When one is in the process entering a formula into an EXCEL spreadsheet it is possible to click on another cell to automatically have this cell reference placed into the formula. However this entry always is entered as a RELATIVE cell reference. Is there a way to have EXCEL insert an ABSOLUTE cell reference into the formula?? It seems to me that it would be so common and yet I have never been able to figure this one out. Please Help Michael Karas Hi Michael! Is this what you want? Click the cell and then press F4. F4 acts as a toggle going through the four reference options. -- ...

The Code One
http://www.thecodeone.com/vb/index.htm ...

duplicate items with different item look up codes ---
I have several items that are the same, but since I get them from different suppliers, they have different item look up codes. For example, I have 4 different vendors for cherry tomatoes. The problem is when reporting it can show as no item xxxxx tomatoes were sold where in fact I sold $200.00 worth of cherry tomatoes under a different look up code. Is there a way to link the multiple item look up codes? Thank you. Could you used aliases? Just use one lookup code, say "CT", then use each of your old lookuup codes as aliases for "CT", then put all four cherry toma...

Windows error code 80070652
After updating manually, get error 80070652--unknown error encountered Started as 4 updates, 1 failed with error. rebooted same results. x86 Win 7 ultimate, 2 gig ram, Acer Aspire 6920 -- Art Artsr3 wrote: > After updating manually, get error 80070652--unknown error encountered > > Started as 4 updates, 1 failed with error. rebooted same results. > > x86 Win 7 ultimate, 2 gig ram, Acer Aspire 6920 80070652 ERROR_INSTALL_ALREADY_RUNNING Another installation is already in progress. Complete that installation before proceeding with this install. Sinc...

Advanced Filter sort on "asterisk" (wild card) hides data, not just empty blank cells.
In debugging a program, I isolated the problem to the following issue. I manually created the following situation in column A, rows 1 to 6, by copying and pasting actual data in order to accurately re-create the problem: ABN/ACN/BN * ABN/ACN/BN 3158816 40000545415/005 Excel "Help" has this to say about the asterisk: " * (asterisk) Any number of characters in the same position as the asterisk For example, *east finds "Northeast" and "Southeast" " I am assuming that the asterisk, being the wild card symbol, being the "cell not empty" symbol, ...