Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Automatically Fill In Sheet Name In Formula

I am creating a register of first aid kits on my site. What I want to do is have a summary as the first worksheet, and then have contents, and individual expiry dates for each kit on seperate kits. Is there a way to autofill, or auto complete, a different sheet name in formulas?

='Kit 1'!$B$27
='Kit 2'!$B$27
='Kit 3'!$B$27

='Kit 44'!$B$27

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Making A Formula To Automatically Fill A Label
As I am a fresh user of excel 2007 I do not know how hard it is to solve the problem I am facing but hopefully there is a solution that the readers here are willing to share with me.

I have a list of **-s that I have recorded as a hobby with my friends (karaoke, free concerts...) and I have them coded in a specific way, so that I can find them on a shelve easily. The list is built up in a form that is seen on the example file that I added.
So far I have used my own handwriting and a sticker to label my ** boxes. Itīs not a very good looking combo to be honest. Since I already have all the data in my excel tabel it would be good to use excel to take all the things from there and just print it out.
How to do that? The front cover is easy, I used VLOOKUP function. All I have to do is enter a number into the column C1 and the front label gets filled.

The problem for me is the back cover that should take the right track nr, title and length from several rows (VLOOKUP uses only one row at a time)? How to make it work, that excel would know that on the first album there are 3 tracks and when chaging a number in C1 there would be 2 tracks on a second one.

View Replies!   View Related
Auto Fill Dates: The Sheet To List The Dates In A Column For Each Month Automatically
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date ďjan07Ē and the period ď10Ē months. The rows A1 to A10 should have jan07Öjan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.

View Replies!   View Related
Function To Fill In A Day Automatically
In the worksheet just say:Cell A1: Jan-11-2009 as today date. Cell A2: What is a date function I need to put in there so when a computer clock change to 12:01am it automatically fill in a next day it should be Jan-12-2009 and continues do so for the next day at A3 ....when computer clock change again. Remember I wanted a cells A2...A3...A2000 are blank until the computer clock change to 12:01am.

View Replies!   View Related
Automatically Fill In To Specific Row ?
to illustrate I attached a spreadsheet.The spreadsheet contains 5 colunms.

1. Names
2-4. Numbers
5. Sum of numbers (the formula for the sum is only entered in the first row)

What I want to do is automatically fill in sums from the second to the last row. I was trying it with the "record macro" function but failed because I don't know how to jump to the last row. CRTL-Shift-down is not useful in this case.

View Replies!   View Related
Automatically Fill Date For Whole Month
I'm using sheet for ledger. For entering date I use =today() but I want to do that in date column(A) I set a formula only once that if I enter data in 2nd column it automatically add today date and if data enter day after tomorrow it will add corresponding date. Is this possible?

View Replies!   View Related
Automatically Fill Information Across Worksheets
I have created a spreadsheet that I must fill out daily. this worksheet has averages that must be automtically shared with a "master" worksheet.

for example
information from sheet2!b23
information from sheet3!b23
information from sheet4!b23
must automatically be transfered to
in that order

at this time I am typing in =sheet2!b23 on sheet1 everytime, I want to avoid this.

If you are asking yourself why? I dont blame you but it needs be this way so I can average each days spreadsheet.

View Replies!   View Related
Fill Data Automatically After Choosing Name
I'm trying to create an invoice with customer list, and the problem now is I'm trying to find a list-dependent data way to input my customer's address.

At the moment, all I can do is a dependent dropdowns from a sorted list (see method here). So it's kinda troublesome in such that my list (of customers) opens up a list for address that has only one option (since there's only one address for each customer).

I hope there's a method out there whereby I can have a list of customers, then select the customer I want, and the address of this particular customer will appear in another cell below automatically (without the need of selecting it in another list).

My address is actually split into three different cells - Address, Building & Postal Code.

View Replies!   View Related
Automatically Fill Formulas In Newly Inserted Row From Row Above
What I would like to do is on a sheet when I insert a new row that it will "FILL" the formulas that are the row above it. For example I have cells A1-F1. On cell A1 there is 1, B1 there is 2...etc. When I then insert a new row I would like the row below A1-F1 to read. A2 = 2, B2=3 so it had a linear growth. I want to do this with my formulas so whenever someone adds a new line it knows to copy the formula as well but only in certain cells if possible.

View Replies!   View Related
Automatically Fill From Dynamic List As I Type
I am not sure of what is it called, but I would like to be able to start typing in a cell and it to start giving me options until there is only one.

Right now I have 9 dynamic lists with anywhere from 5-20 names per list and I have a report form with multiple data validated drop down lists each pulling from a separate dynamic list. This works ok, but as the user tabs to a cell with the drop down, they must use the mouse to initiate the drop down and then select the right data. Even if the user types the right data into the cell, it will not pass validation. Is there a way to start typing and it to keep throwing away things until only one option is available and then be able to use the tab button to enter that data into the cell and move to the next cell? Or as another option how hard would it be to activate the drop down when the cell is selected and then use the arrow keys to select the right one and tab to the next cell? I would want to be able to tab through the cell if no information was needed from that dynamic list.

View Replies!   View Related
How Do I Get My Customer Address To Automatically Fill In (cell B)
i have a an invoice template setup on my computer in excel.

what i would like to know is how do i get my customer address to automatically fill in (cell b) if i select them by customer number (drop down menu in cell a) in an different cell (cell c)

View Replies!   View Related
Fill Input Cell With Color Automatically
I am writing a program in excel spreadsheet and I was wondering how to fill an input cell with color automatically in excel 2007.

I mean that, say I am writing an addition program, I am giving an input numbers in B1 and B2 and i am writing formula in B3 (=B1+B2). I want excel to fill B1 and B2 with yellow/any color automatlically. Only the cells with number input should be highlighted.

View Replies!   View Related
Auto-fill To Not Automatically Use The Next Numbered Cell
How do you make the auto-fill to not automatically use the next numbered cell:

For example:

I want it to fill with every other cell from a different sheet (same column "D") so i would have a cell ='sheet1'$D10 or whatever then how would i do it so it will auto fill every other cell from then on so the first cell below the initial one would copy from the other sheet as D12 instead of D11?

View Replies!   View Related
Drop Down Box; Automatically Fill All The Information Into Address Column
I have inserted the sheet. As you can see in Sheet 1 I have the Store, Address, Town etc, I would like a drop down box in D13 ( Store Name ) so when I select a store it will automatically fill all the info into D15:D19 Address, Town etc. The info will be pulled from Sheet 2.

View Replies!   View Related
User Form: Automatically Fill The Dates To Which They Are On Holiday
I am making a holiday form with names down the page and dates along the top, what I am trying to do is create a user form so it will automatically fill the dates to which they are on holiday. What I would like the code to say to find the cell at a specific date (column) and person (row) so it will enter a custom text or number.
The data that is submitted on the user form would be a start date and end date and the personís name.

View Replies!   View Related
Automatically Fill Cell Down X Rows Based On 2 Other Cells
Suppose in cell A1 I type in the number "100" and in A2 I type in the number "5"
What I want to do: Have the number "100" repeat 5 times in a vertical list (so 100 appears in B1, B2, B3, B4, B5)

View Replies!   View Related
Automatically Fill Data Depending On Choice From Drop-down Menu
I have three lists in the first sheet 'Food', 'Quantity', 'Points Value'
Each food (i.e. tuna, pasta, anchovies) has a quantity (i.e. 40g) and a points value (i.e. '50'). The first list ('Food') has been copied as a drop down list in the second sheet. When selecting a food, I would like the cells 'quantity' and 'points value' to pre-fill - taking the data from the first sheet.

i.e. if say Anchovies has the quantity value '30g' and Points Value '2' on the first sheet, then in the second sheet if anchovies was selected from the drop-down menu, these values would add themselves. how to do this? I have attached a sample file shwoing what I want to do.

View Replies!   View Related
Automatically Color Fill Rows Based On Changing Column Information
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.

Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.

Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.

ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358

View Replies!   View Related
Select An Item From Drop Down List And Automatically Fill A Number Of Cells
I am kind of new to Excel programming and would like to know if there is a solution to my problem. What I need is to have a dropdown list (I know how to make this) then select an item from the drop down, then based on the name i select, Excel automatically fill up the rest of the cells.

For example, from the drop down list I select the company name then Excel automatically type the address, phone number, Fax number and the contact name of the company for me in corresponding cells. Is there a way to do this?

View Replies!   View Related
Formulas: Get Data Added In One Sheet Of A Workbook To Automatically Be Entered Into Another Sheet
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 Replies!   View Related
Select Item On Sheet A Automatically Paste Info To Sheet B
I have a workbook with a Cost Analysis sheet, a master menu sheet, and then all the sub sheets. I have coded command buttons on the master sheet, so once a category is selected, it goes to the appropriate sub sheet. On the subsheet is a list of items of varying sizes, and command buttons beside that which will allow the user to "Select" that particular size. Once the size is selected, the verbage in cell B5 plus the verbage (size) beside the selected amount, and the prices (labor and materials) in the next two columns, need to be transferred to the first available blank line, starting with line C5, to the Cost Analysis sheet. I have never done coding before for a Loop (i.e. For i = ???) and am not sure how to get it to do what I want. After they select that, they can pick a command button that takes them back to the master menu so they can continue picking various items until they are through. Then the Cost Analysis sheet will show all the items they picked, (name of item, material cost and labor cost: Columns B, C, & D). Not everything is coded yet (such as selecting a command button to go back to the main menu, because I wanted to make sure I could solve this problem before doing the easy stuff).

I read over quite a few of the previous threads and am not sure how to change them to suit my circumstance. I know some code (VB), but not much. I do not understand the concept of DIM, and how the loop works to make it happen myself, and all the websites I could find were not suited to my situation of copying three cells of data to the first blank cell on another sheet.

View Replies!   View Related
Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet
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 Replies!   View Related
Automatically Refresh The Data In Sheet 2 And Put It In Sheet 1
I have 2 sheets.

Sheet2: it contains data that is being imported from an external link with the following info: ....

View Replies!   View Related
Find The Data In Some Rows That Match With One Cell And Automatically Fill The Data
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni

View Replies!   View Related
Fill Next Row On Separate Sheet
I am trying to create a macro that will take several values from one sheet (these values are from various spots. They are not all in one row) and paste them in the next blank available row on a different sheet under set headings.

View Replies!   View Related
Use Dropdown To Fill Cells From A Different Sheet
I have 2 sheets that are used for an inventory that tracks hours on a tool that we use where I work. One sheet allows the user to add items to the inventory along with the current hours on the items. On another sheet, I have several dropdown boxes based on the category of items that are autopopulated with the item's serial numbers through data validation. So far, I'm only able to have the serial numbers fill the dropdown boxes, but I also need to have the hours associated with the individual serial numbers transfer over to this sheet as well in the cell adjacent to the dropdown list.

I assume the pseudocode for this would be for each cell in the dropdown sheet, if it matches a cell in the inventory sheet, grab the cell's value in the hours column and paste it into the adjacent cell on the dropdown sheet, but I'm not sure how to do the actual VBA for it.
I've included my sanitized workbook so you get a better idea of what I need.

View Replies!   View Related
Fill Cells From A List On Another Sheet
I need to populate a range of cells with names from a list on another sheet based on the contents of another cell.

In the attached example;

on sheet 2 cell E3 has a number in it that corresponds to a number in column D on sheet 1, I want to use that as the reference to fill in cell E4 on sheet 2 with the value in column A on sheet 1, then I need to populate the YELLOW cells on sheet 2 with the names from sheet 1 column C that also match the values in sheet 2 cell E3.

The attached file has an example. I have been trying to use LOOKUP and VLOOKUP but can't get it to work.

Can someone please take a look at this for me?

View Replies!   View Related
Fill Combo Boxes On Each Sheet
How do I change the below code to fill all combo boxes all on sheets. All the names of the controls should be the same on each sheet since each sheet was created from a copy of the original.

View Replies!   View Related
Drag & Fill Series Only In One Particular Sheet
I am not able to drag & fill series only in one particular sheet, in other sheets this function is working fine.

View Replies!   View Related
Userform Fill In Correct Sheet
I wrote my program with several user forms and private subs. All the results was going to my Report sheet. However, now, I want to be able to run those userforms in another sheet. All the results are coming up in this selected sheet. Is there a way that I can program to for all the results to go to the report sheet without going back and fixing every private sub?

View Replies!   View Related
Using A Combobox To Fill Data In Sheet
Using a combobox with dropdown choice you can select today or yesterday. After this i want to fil in cell A1 the date (now) if choose today, and date (-1) if choosen yesterday. I attach a test sheet with this post.

View Replies!   View Related
Automatically Fill In Column When Certain Cell In Column Changed
I am trying to have some automation to my WS and what I want to do, is if a certain column (column 104) is changed, it will fill out that column with the data entered as long as the account is the same (column 5). Here is the code I tried, but realized very quickly, it puts me into a loop. This is being called by a change worksheet fuction

If Target.Column = 104 Then
If IsEmpty(Target) Then
testacct = Cells(Target.Row, 5)
temptest = testacct
s_TempSwitch = Target.Value
Testoffset = 0
Do While temptest = testacct
temptest = Cells(Target.Row + Testoffset, 5)
Cells(Target.Row + Testoffset, 104).Value = s_TempSwitch
Testoffset = Testoffset + 1
End If
End If

View Replies!   View Related
Macro To Fill In Data At Botom Of A Sheet
I have a sheet where a user will enter x number of rows of data.

im trying to get it so that the macro, when run, will goto the last row of Data in Colum A, and delete all rows BELOW to the end.
then select the first empty row and do the following

in A =SUM(A30:A last row of data above this one)
in D ='TOTAL
in E = same as A but for colum E
in F = same as A but for Colum F
in K =same as A but for Colum k
in J =same as A but for Colum J

then select the row below that one and from cells A:M
and format
With Selection.Interior
.Pattern = xlCrissCross
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With

View Replies!   View Related
If Statement + Vlookup: Fill In The Third Column On Sheet 2 From The 3rd Column In Sheet 1??
I need to fill in the third column on sheet 2 from the 3rd column in sheet 1. do I need an IF statement combined with vlookup??

sheet 1
mark l XYZ l 45
katy l ABC l 3
mark l ABC l 6
katy l XYZ l 45
mark l DEFl 4

sheet 2:
mark l XYZ l
katy l ABC l
mark l ABC l
katy l XYZ l
mark l DEF l

View Replies!   View Related
Automatically Fill Cell Value Based On Another Cell
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?

View Replies!   View Related
Cells Cross Referancing Data From Another Sheet To Fill Themselves In?
I would like cells to fill themselves in automatically on one sheet, by getting the figure from the cell to the right of them, and seeing where the same figure lies on a different sheet. which ever row this figure lies in will be used to grab a different figure from colum "C" of the same row. This is the figure i would like to be put back on the first sheet.

Im aware that i havent explained this very well but i hope that my attached spread sheet with a couple of notes and highlighted bit will explain it much better.

Thank you very much in advance to anyone who can help me with this!


View Replies!   View Related
Multiple Timesheets, Auto Fill From Data Sheet
I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/times into individual employee timesheets. The timesheet layout has already been provided to me and cannot change.

Iím really struggling here and would appreciate any help.

Please reference the attached workbook for the issues described below:

1) Sheet "1_attlog" is the log that is imported from the electronic time clock.

2) Sheet "Sorting & Filtering" is what I have developed so far that manipulates the data from sheet "1_attlog" into something that can be easily understood, it also matches employee #'s to a name, and allows for date ranges to be set that copies over to the individual timesheets. Please do not change any formulas on this page if possible.

3) Sheets "#2 Dan through #10 Blank" are all individual employee timesheets that I need to import the data from "Sorting & Filtering" and populate the "Time In" (column D) and "Time Out" (column G) based on matching the data from "Sorting & Filtering" sheet, "Time Clcok ID" (column D) "Date" (column E), "Time In" (column F), "Time Out" (column G), "Emp Name" (column I).
The dates on the individual timesheets have been linked to the "Date Range" (cels F2 & F3) on the "Sorting & Filtering" sheet, please keep this link intact.

4) The individual timesheets already have formulas inplace to calculate the hours worked after the "Time In" and "Time Out" data has been imported.

View Replies!   View Related
Fill-down Formula
Can anyone supply a formula which can be filled down column J of s/s. The formula should look at column H and find those cells where the content of the cell is lower than the one above it. I supply, in the enclosed attachment, three examples and have marked with an X any qualifying cells in adjacent column I. The formula needs to incorporate cells above and below so as to produce a running average whereby no above cell is higher than any below.

View Replies!   View Related
Fill Down Formula
Is there an easy way to fill down this formula?


View Replies!   View Related
Formula To Fill In
In the attachment, Column A includes year with indefinite empty space between years. In column B, I want to use one formula fill in the results as shown.

View Replies!   View Related
Link Formula / Update Formula Automatically
Iím working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.

So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).

Iím wondering if itís possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).

I can copy the formula with the ďPast SpecialĒ function but the function is not updating if I change the formula on the main sheet.

So is it possible to link it and that itíll be updated automatically when Iím changing the formula on the main sheet?

View Replies!   View Related
Automating Fill Down Of Formula
I have a report which has several tabs of data input which is automated.

There are then several columns of formula which I manually fill down at the minute, is they an easy piece of code to automate this?

The attached example may help, on the tab data source I want to automate the copying down of the formulas in columns D&E.

Can this to be automated by the button on the tab front page?

View Replies!   View Related
Macro To Fill In A Formula In Each Row Where There Is A Value
I am looking for a Macro to fill in a formula in each row where there is a value. So if there is a value in row 1 Column A then place this formula into Column B and continue down the sheet untill there is not a value in Column A.

View Replies!   View Related
Formula To Fill In Cells
I am trying to create a nice equation that will look in row 35 for the highest value and then return the value that is 33 rows up and 1 column to the left. See attachment of for more details.

Basically, I need a formula to fill in cells B38:B41 (detail included in Excel doc attached).

View Replies!   View Related
Fill Down Cells With Formula
I have this formula in the cell

View Replies!   View Related
Auto Fill Formula Using $
I created a database and made one small mistake doing it. The database is infinite amount of rows down and my formula needs to be corrected as such. In the current cells: (C1:C1000) i have the formulas referencing cells $B1, but i need it to reference $B$1. In C2 I need it to reference $B$2, C3 $B$3 and so on. If I autofill, it obviously just keeps the following cells at $B$1 and i can't keep the dollar bill sign.

View Replies!   View Related
Fill ListBox With Found Values Sheet Names & Cell Addresses
I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.

However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.

Here is my code for one of the Userforms:

Option Explicit

Sub Locate(Name As String, Data As Range)

Dim rngFind As Range
Dim strFirstFind As String

With Sheet1.UsedRange
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address

I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.

View Replies!   View Related
Sheet Name From Tab Automatically Into A Cell
CELL("filename") will output the full path of the sheet and tab you are in


Will take just the tab name.

"SWalton" wrote:

> How do I get a cell to reflect the value which lies in a sheet's tab (the

> sheet name) without having to manually type it in

View Replies!   View Related
Automatically Listed The Other Sheet
I have a list of jobs names on one sheet and there are certain ones that i would like to put on a list on a second sheet.

what I would like to do is put a "1" in a cell next to the job names that I want on the other sheet and have them automatically listed.

View Replies!   View Related
VBA To Fill Blanks In A Range With A Formula
I am in need of a solution (probably VBA) that can fill a range of cells with a formula IF they are blank. Ideally that range is a named range I can define in Excel. If that is too hard, then a hard coded column I hand-edit the script for is tolerable. Also, ideally, this script auto-executes whenever data changes on the sheet.

You formula I will populate is:
..but a simpler formula can be stubbed in.

NOte that it does have relative references, so the script needs to adhere to normal EXCEL conventions of enumerating cell references.

If the script points to a refernce cell that contains the formula that is uber.
Maybe it should do copy and paste instead of a string replacement in order to leverage EXCEL's referencing?

I'm stuck on this, and this would be VERY useful for many of my sheets to be able to point to a refernce cell containing a formula to fill in.

View Replies!   View Related
Automate Formula Copy/Fill-Down Until Last Row
I have some formulas that combine template text and values from cells in that row to result in text that I then Copy and use in another place. I am trying to convert that to a Macro.

My Skills are Novice -- I have searched Google and Tutorials and it seems that my usage is not common so, I have not found answers to help me move ahead.

I'm working with a simplified version of what I want to do to eastablish a
Working model that I can modify and expand. I had a macro that ran to completion but I abandoned it. I realized that I was on a Wrong Track. It was created from recorded macro sections and googled routines and very clumsy.

Heres my Scenario

If my Excel Cells were

Cell A1 = 135252
Cell B1 = $13.95
Cell C1 = 3
Cell D1 = Oversize

I want to run a Macro from E1 that would Result in text of

Our Stock Number is 135252 - The Price is $13.95.
We have 3 in stock. Type of Packaging is Oversize

being left in Cell E1.

I would like the Macro be able to run on other rows as well when launched from Cell E of that row.
My Goal would be to make the macro work on a range of rows by Highlighing Cell E in those rows.

My Excel Formula to accomplish the same thing is -

="Our Stock Number is "&A1&" - The Price is "&B1&".
We have "&C1&" in stock. Type of Packaging is "&D1&"."

If I could get this much working, I think I can expand and edit to encompass my actual application.

View Replies!   View Related
Copyright © 2005-08, All rights reserved