Possible To Have A Sheet Name Derived From A Cell?

Nov 12, 2009

Is it possible to have a sheet name derived from a cell?

I am creating a workbook where the users will be able to edit a list of names, and add certain data to each person. There will then be a separate sheet for each person that gives analysis if their individual data. The user will change the names in the list as people come and go, and want the sheet names to match.

View 9 Replies


ADVERTISEMENT

How To Use VLOOKUP For Cell Value Which Is Derived By Formula

Mar 6, 2014

I'm wanting to lookup the content of a cell, which is a string, "Name", resulting from a formula within that cell.

For example the first code is referencing the cell output from the second formula.

Code:
=VLOOKUP(N2,RandomTab!$A$2:$E$60,5,FALSE)

where N2 is

Code:
=UPPER(VLOOKUP(B2,OtherTab!$A$1:$H$68,8,FALSE))

presently it, the first VLOOKUP, returns #N/A

View 3 Replies View Related

Cell Retains Value Derived From Dynamic Function.

Jan 14, 2009

Let's say I have three cells; A1, B1, C1. A1 = a random number (RandBetween 0 and 100) B1 = If(A1>99,1,0). I want to increment C1 everytime I refresh the randbetween via f9 so that the first time A1 returns a value of over 99, C1 picks up a value of 1 from B1 and then the next time I get a value of A1 over 99, C1 adds the value of 1 from B1 to its existing total, giving a total of 2 etc etc etc.

Essentially, what I want is: If A1>99,C1=C1+1. I can't work out how to get C1 to hold its value when I refresh the sheet. I realise it's probably pretty simple, but it would appear that so am I.

View 2 Replies View Related

SaveAs Method With Filename Derived From Cell Reference

Jul 24, 2012

I am trying to use a cell reference to have excel automatically Save a worksheet using a cell reference as the workbook name. When I step through the code and use the immediate window I get the correct name for the file (fname in this case) but as soon as Excel tries to save the file, I get an error.

Here is the code I am using:

fname = Sheet3.Range("I1").Value
ChDir "S:Op CostsBudget 2013Data"
MsgBox "The actice file will be saved as " & fname
ActiveWorkbook.SaveAs filename:= _
"S:Op CostsBudget 2013Data" & fname & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

View 5 Replies View Related

Cell Addresses Derived From Concatenated Values In Separate Cells

Apr 2, 2013

I need to return a value from a cell in Column H, but with a row number that varies with each entry.

I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:

=Display Value from in column H: (row number per cell B1)

And cell B1 would include the number 3789.

So the returned result is the value from cell H:3789

View 4 Replies View Related

Right Axis Derived From Values?

Feb 28, 2014

The goal of this chart is to show how sales are adding up relative to bonus levels for the month. The problem for me is in getting the right axis tick marks, labels and chart lines to match the values in Columns C and D. This need may be further complicated because the target amounts increase by a fixed percentage (11% in this sample, but it can vary).

View 4 Replies View Related

Conditional Sum Derived From A Date Interval

Feb 21, 2010

I'm trying to sum each months visa purchases. The data is in three columns: date,value and payment type.

I can't get this formula to work:-

{=SUM(IF(AND(Purchases!C6:C999>DATE(2009,12,28),Purchases!C6:C999<DATE(2010,1,29)),IF(Purchases!K6:K 999="Visa",Purchases!H3:H999)))}


It's returning a value of 0 (and if that were true I'd be delighted!)

View 12 Replies View Related

Hiding Formulated Values Derived From 0 Value Cells..

Dec 7, 2009

I have a sheet with cells that will vary and change in value. The far right column (in this case G) is calculating a percentage from cells in columns D and F. At the moment only 9 rows are being used but I have made provision for the list to extend down to row 100 for future use. Cells in columns D and F are receiving their values from sumif formulas on another sheet, therefore a lot of cells are showing a 0 value for the moment (which I have formatted to show blank).

My problem is that I have cells in column G (percent formula) which are of course showing #DIV/0! due to the fact that they are calculating cells with 0 value. How can I format the cells in column G to show blank until they receive a real calculation!

View 2 Replies View Related

Automation Of Solver: Must Inputs Be Derived From Cells?

Jan 20, 2010

I'm trying to automate the solver using a vb program. I have found this example which is just about spot on for my needs:

View 4 Replies View Related

Reverse Pivot Table :: Recreate The List It Was Derived From

May 14, 2009

I am trying, starting from a list that has the same structure as a Pivot Table, to recreate the list it was derived from.

I added a sample to show what I mean.

View 8 Replies View Related

Evaluate Formula Derived Empty String In SUMPRODUCT?

Dec 10, 2013

I've been trying to get this SumProduct formula to return anything except an error for a while now.

=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17

View 2 Replies View Related

Adding Values Between Two Dates And Dates Are Also Derived By Formula?

Dec 9, 2013

I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.

The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column

These dates also have formula by which there are derived

-------------------------Current Ageing-------------------------
Date Range

Bucket
Amount
Percent
Start Date
End Date[code].....

I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.

View 9 Replies View Related

Returning Cell Value From Sheet 1 Based On Cell Reference From Sheet 2

Oct 29, 2013

In Sheet2 I have a list of cell addresses showing values such as D5, D32, D59, D221, D869 stored in cells AB7:AB16. In Sheet1 the "D" column holds dates. I want to return the corresponding date for each D# cell into Sheet2 in column AC7:AC16. I'm unaware of the proper syntax for this. I though it would look something like:

='Sheet1'!('Sheet2'!(AB7))

however that doesn't work.

View 2 Replies View Related

Autofill Cell On Sheet 2 With Value From Colored Cell On Sheet 1?

Nov 1, 2013

I want to auto populate a call with the value of a different cell that has been colored on a different page.

Example:

1.) On Sheet 1, Cells range A1-A10 have values to choose from
2.) I choose A7 and change the cell color to gray
3.) It auto populates Cell B1 on Sheet 2 with the value of the gray colored cell I selected on Sheet 1

View 3 Replies View Related

Cell Reference To Another Cell Using Sheet Number Not Sheet Name?

May 22, 2012

How can this be done?

E.g. say I want to create a list of cells each referencing to the first, second, third sheet, and so on.

Say, on one sheet cell A1 references to the second sheet's A1, cell A2 references to the third sheet's A1, and so on.

Ideally I'd love to be able to write something like
=Worksheet(1)!A1
=Worksheet(2)!A1
=Worksheet(3)!A1

and so on.

View 1 Replies View Related

Copy From One Cell In Sheet 1 Into Next Blank Cell In Row In Sheet 2

Sep 17, 2012

The problem is:

I Sheet 1 cell I33 is updated weekly. It would then be great with a macro that could copy from I33 in Sheet 1, into C5 in Sheet 2. But if C5 is filled, it should paste into D5, and so on.

View 5 Replies View Related

Return Value From Another Sheet Cell Where Sheet Name Resides In Cell

Feb 27, 2008

This function =INDIRECT(""&A17&"!"&$A$8) works only once or twice. Then I rewrite it as =INDIRECT(""&A17&"!"&A8), without the dollar sign, $, at A8. But then that only works once or twice.

This is how it works: In Grid A17, type name of worksheet in which to perform the data search. Then, the function goes to that worksheet and selects the data in Grid A8.

View 6 Replies View Related

Excel 2003 :: Conditional Format - Highlight Cell In Sheet 2 If Duplicated From Sheet 1

Mar 25, 2014

I have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.

I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.

View 3 Replies View Related

Excel 2010 :: Match 1 Cell And Column In Sheet 1 To 2 Columns In Sheet 2 Return Data From A 3rd

Jul 23, 2012

I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.

My attempt is in cell D2 on sheet1.

View 5 Replies View Related

Copying Sheet Names And Cell Data Into New Overview Sheet

Mar 21, 2013

I have workbook that has several sheets within the workbook that are set up identical. Each of the sheets in the workbook are for a specific company.

As of right now I have been adding a sheet to the workbook that is an overview for what is in each sheet (the individual companies). Currently I am doing the formatting of the heading and column names manually and I pull the data from each sheet with a VLookup. I have been trying to enhance my VBA skills with coding something that will fill in the appropriate cells from worksheet to worksheet.

What I am trying to do is to populate an overview sheet with cells C24, C25, and B36 being static on each row per sheet. Then each row will be populated with cells C(36, 59, 70, 81), D(36, 59, 70, 81), F(36, 59, 70, 81), G, and H(36, 59, 70, 81). The overview sheet will have the diagram below in a ru

I attached an example : example.xlsx

Sheet 1
C25
C24
B36
D36
C36
F36
G36
H36
I36

[Code] .....

View 1 Replies View Related

Cell Formatting - Type In A Word In Sheet 1 Say Mike On Sheet 2?

Jan 28, 2014

I was wondering if there was any way to do a cell format so that if I type in a word in sheet1 say "mike" on sheet2 the word "mike" cell would be highlighted? or if there is a formula you could use to do that.

View 14 Replies View Related

Copy Rows From Selected Sheet To New Sheet Based On Cell Value

Mar 27, 2008

i have a workbook that has a lot of sheets but i need to pull information from the one sheet "Veneer Log" i Need it to make new sheets with the same heading as on the "Veneer Log" (Rows 1 & 2) Sheet but it needs to be filtered by the "Product" Column (H) with a new sheet made for all the diffrent products i.e. Dimensional, Drywall, Corners - Thin V., Accents,..... so each product will have a new sheet with i am hoping someone can help me with this. This log changes Daily and it would be nice to have a sheet with only the same product on it to compare new orders so we can batch run. i hope i have given you enough information so someone can help me with this. i have attached a sample log the real log has about 10 worksheet for diffrent departments but i only need info from the Veneer Log Sheet.

View 14 Replies View Related

Reference Sheet (return The Value Of Cell B2 On Whichever Sheet The Text Of A1 Says)

Nov 1, 2009

If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?

As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:

View 2 Replies View Related

Update A Cell Value From A Previous Sheet After Copying The Sheet And Renaming It

Nov 21, 2009

I have copied a sheet, moved it to the end and renamed it with a date that is in cell "A1"

Now after that process is finished I need it to update the date in cell "A1" of the newly created sheet with the next day's date.

I am stuck however referring to the previous sheet to update the date value in "A1"

View 7 Replies View Related

Copy Cell From Specific Sheet & Paste To Active Sheet

Mar 31, 2008

I would like a macro that will go to a fixed sheet, copy the format, go back to the previous sheet and paste the format. My problems arise going back to the previously activated sheet rather than just a fixed sheet.

View 2 Replies View Related

Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet

Aug 10, 2008

I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated:

1-the sheet is renamed to the value of F1,
2-a new tab is made (a carbon copy of the hidden sheet "Template")
3-the new tab is named "New Tab" and marked as unhidden.

Public Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("F1").Value
End Sub

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function..................

View 2 Replies View Related

Populate Row Of Cells On One Sheet Depending On Value In One Cell On Other Sheet

Apr 27, 2014

with the attached spreadsheet?

I want to populate "selections" sheet with data from the previous sheet (it will be the date) depending on the value of a cell in column G. You will see I have already got the data from 27th April into my selections sheet but wondered if it can be done by a formula to save me copy and paste time?

View 2 Replies View Related

If Data Is In Cell Copy To New Sheet And Print New Sheet?

Jun 13, 2014

Ok so i have 2 sheets. Sheet 2 is a form that needs to be printed.sheet one will have data pasted into it by the user. The data will be placed in column a and b. If a has data in it then so will b. Now I need the macro to identify if data is in a then the macro needs to then copy a and paste special into A18 on sheet2 then copy b and paste into A6 on sheet2 then print sheet2. Repeat this process to every row as long as A has data in it.

View 3 Replies View Related

Copy Row From Sheet 1 To Sheet 2 When Data Entered In Cell F

Jul 9, 2009

I have Worksheet 1, with columns A to E. I would like a row to be copied to Worksheet 2, as soon as cell F in Worksheet 1 is populated. Also the row to be deleted from Worksheet 1.

So, as soon as F1 in worksheet 1 is populated and enter button pressed, row A1:F1 will be copied to the next empty row in worksheet 2, while being deleted from worksheet 1. So eventually all rows in worksheet 1 will be deleted and rows in worksheet 2 will be populated.

View 2 Replies View Related

Sort Page (Sheet 2) #REF! In Cell With IF Formula To Sheet 1

Oct 25, 2009

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?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved