Autofill The List Page
Aug 5, 2008
I am creating a workbook that easily allows me to keep track of items and costs. So on one page I have a list of items in one Coloumn - column A eg apples, oranges, banana etc.
On the cell next to them (Column B) are their prices. This page is purely used to list items and costs.
On page 2 - is the easy to fill list page. Theres a drop down list in each cell in column A listing the items on page 1. I have set this up already.
But i need the associated price to appear in coloumn B austomatically.
So when i select apples in column A - their price automatically appears in next to it.
View 9 Replies
ADVERTISEMENT
Mar 24, 2014
I'm looking to populate a series of sequential codes based on a code prefix.
Sheet 1: Master Code list
Column A = Lookup Value
Column B = Code Prefix
Sheet 2 - Results page
Column A - list of lookup values - sorted in value order - The number of occurances varies for each value
Column B - Results list
I want to populate Column B in the second sheet with a sequential code list for each different value in column A. So for the first value it would have the prefix then proceed numerically (Prefix0001, Prefix0002, Prefix0003...) then for the next value its prefix in sequence (2ndPrefix0001, 2ndPrefix0002, 2ndPrefix003...), etc
I'm wondering if there is a way to do this all in one step or else if there is a way way to number each occurance of the lookup value in the second page, in which case the code can be concatenated easily by a lookup of the code.
I suppose on the back of this there is another query - is there a way to concatenate a number where the format is 000# where the concatenated value will retain the leading zeros? I've been trying and it is stripping them out.
View 2 Replies
View Related
Feb 14, 2014
I have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.
View 2 Replies
View Related
Jun 16, 2012
I was wondering if it was possible to have some cells where when I start to type it suggests me from a list from another sheet?
View 1 Replies
View Related
Feb 25, 2006
I have a cell which contains a validation list with about 350 names to select from. When the user is selecting the name they wish to make changes upon, is there a way to allow the user to hit say 'H'f and skip down to the last names beginning with H?
View 3 Replies
View Related
Feb 19, 2014
I have a list of model numbers that corresponds to part numbers i.e:
model number
part number
abc
123
def
456
[code]....
I can type in A2 = ab and it autofills the model number abc, and have B2 automatically have the part number which is 123 filled in as well.
View 4 Replies
View Related
May 25, 2012
I have an Excel file for work that has the following:
Column 1 is Agent
Column 2 is Interval (in 15 minute intervals from a report that I pulled)
Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)
What I have currently is that if it says:
John Doe 15:00 5
It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.
I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.
Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.
I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.
Here's how it looks when I'm ready to start the macro:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:45 4a
[Code] ......
And I need it to look like this:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15 4b
JohnDoe - Doe, John15:15 4c
JohnDoe - Doe, John15:15 4d
[Code] ........
View 7 Replies
View Related
Jun 9, 2009
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies
View Related
Jun 15, 2012
I am trying to do an if/then off a list in the same page.
I have a list of departments with Managers and Supervisors. I would like to match the manager / supervisor to an imported list to fill in the correct person.
My import has the department labeled as shown below:
G
H
I
1
Department
Manager
Supervisor
[code].....
At the bottom of the page I made a list of the names associated by department:
A
B
C
D
30
Department
Manager
Supervisor
[code].....
Is there a way to do an if/then to check if the department in G2 contains a number in B31 to B33 then put the name in H2? Then I could repeat the same in I2 and move it down the sheet where needed.
View 2 Replies
View Related
Aug 27, 2013
I have a spreadsheet with only entries in column A - 154 cells. Nothing in other columns. When I try to print, it says it'll be 4 sheets long...
1
2
3
4
down to
154
How can I get it to print in a snake-like fashion so all on one page?
View 2 Replies
View Related
Mar 24, 2014
I have a voucher that I am trying to print out for everyone with their name and Employee number on it. Now, I have the generic voucher and the list of name. I am wondering if there is a way to automatically print out vouchers with their name on it, according to the list? Or if some VBA can allow me to create a sheet for each person in the workbook.
View 3 Replies
View Related
Jun 2, 2009
I've a Pivot Table which i would like to get a list of it.
It is from an OLAP cube from Oracle
Based on the image below, every month i refresh this PT, there will be an additional WK. Lets say WK22.
How do i export the whole list from WK08 to WK21 everytime after i refresh my PT?
How do i select the last item in the Page field? If there is WK22, den it will auto select WK22. If not WK21.
View 9 Replies
View Related
Mar 3, 2009
I'm trying to display in a condensed fashion (in a list box, that way users can just scroll that box instead of the entire sheet), but it keeps giving me error messages saying it doesn't have enough memory to open. Granted, it is a lot of information (a full page's worth at least), but I moved some values around and had it load a different list of only 4 values and it still gave that message- so I can only assume I'm only using it wrong.
View 3 Replies
View Related
Jun 5, 2014
I would like to be able to display all of the sheets in a workbook as a list on the front sheet.
I have a front "Contents" Sheet (attached at the bottom of my post)
In the "Ticket No." column I wanted to have the sheet name, in the "Date" and "Description" column I wanted to display cell values from each sheet; which would be the date and description of that sheet/"Ticket". The "Resolved?" column I will be able to do after I have this part figured out.
The workbook is also attached :
Attached Image : contents.jpg
Attached Files : Troubleshooting Tickets.xlsx
View 14 Replies
View Related
Jan 3, 2013
I am after some VBA code that chooses each value from a drop down list (in cell A1) then copies a separate sheet 1 (sheet 1) into another workbook (can have both workbooks open at the same time or the first step will have to create a workbook) with the name of the added sheet renamed to the value chosen from the list. When the second value is chosen then sheet 1 should be copied into the newly created workbook as well.
Sheet 1 has lookups that are updated when the new value in the drop down list is chosen
There are around 85 values in the drop down list so I'm expecting a workbook with 85 tabs (copy pasted values and formats)
View 1 Replies
View Related
Jun 12, 2009
Whats the VBA code for transfering selections on a multi select list box to a page.
ex. the list box has selections from 1 to 20 and I want to select 3,4, and 5
then on the page I want it to say 3,4,5
View 9 Replies
View Related
Feb 27, 2007
I want to print one sheet for each of the values in a page field drop-down. I can do this by supplying the values individually (code below) but would rather determine the list of values dynamically. Can this be done?
Private Sub pbPrintAll_Click()
Dim cix As Integer
Dim Ctrct As String
cix = 3
While (Sheets("Database"). Range("B" + Trim(Str(cix))).Value <> "")
' Get value from database sheet
Ctrct = Sheets("Database").Range("B" + Trim(Str(cix))).Value
' Set CurrentPage value
Sheets("Customer P&L Pivot1"). PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = Ctrct
' Print formatted sheet
Sheets("Customer P&L").PrintOut
cix = cix + 1
Wend
' Reset Current Page value
Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)"
MsgBox "Prints sent to printer."
End Sub
View 4 Replies
View Related
Nov 1, 2011
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
View 4 Replies
View Related
Nov 20, 2009
I am trying to write a list of pivot table page filter pivot items to the Immediate window - but only hidden items. The code below should do the job where the active sheet is a pivot table.
However when I change the page filter pivot items being hidden, the pivot items returned by the macro don't change. It seems to assume that all pivot items are hidden when in fact it may be only one or two. It will work though for pivot row items (pvt.RowFields) and pivot column items (pvt.ColumnFields) where the user changes them.
Does the pf.HiddenItems collection work for RowFields and ColumnFields but not PageFields? If so, is there a pivot field object that reliably holds hidden pivot items residing in the page filters?
Sub ListHiddenPageFilterPivotItems()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.ActiveSheet
Dim pvt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
View 8 Replies
View Related
May 18, 2006
I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage()
On Error Goto ErrorHandl
Dim strPage As String
Dim webIE As SHDocVw.InternetExplorer
Dim myURL As String
Dim tableBeg As Long
Dim tableEnd As Long
Dim RowBeg As Long
Dim rowEnd As Long
Dim cellBeg As Long
Dim cellEnd As Long
Dim strBeg As Long
Dim strEnd As Long
Dim myCell As Range
Dim rowNum As Integer
With Sheets("INJ")
Set myCell = .Range("A2")
.Range("A:F").Value = vbNullString
rowNum = 2
Set webIE = New SHDocVw.InternetExplorer
myURL = "http://www.sportsline.com/nfl/injuries"
webIE.Navigate myURL
Do Until webIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
strPage = webIE.Document.body.innerhtml...................................
View 6 Replies
View Related
Mar 26, 2006
I suddenly can't adjust my page breaks in any Excel spreadsheet while in Page Break Preview. Is there some option to turn it on and off?
View 7 Replies
View Related
Oct 16, 2013
I have a sheet that changes the number of columns based on data from other sheets. This is the only sheet in the workbook that I will print out every day. I have it set right now to fit all columns on one page, however sometimes this will result in the bottom 1/3rd of the sheet not being used. If I use fit all rows on one page, then some of the columns will get cut off. Is there a way to scale in both directions? My guess is there is not, but I thought I would ask because it would work great for me if I could.
View 4 Replies
View Related
May 21, 2009
I have a report that I use and right now I have a command button that prints the report x2. What I would like to do is to have it print one in color and one in grayscale. I could do this I think with two different buttons, but I would like to utilize just one. I tried creating a macro but it did not pick up the fact that I changed the properties to black and white.
View 2 Replies
View Related
May 1, 2009
I am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:
A B
1 q 2009.05.01
2 w 2009.05.02
3 w 2009.05.01
4 q 2009.05.01
I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.
View 3 Replies
View Related
Jul 23, 2006
Is it possible to Change Start Page Of Multi Page UserForm Based on 8 CommandButtons. on a WorkSheet, for example.
CommandButton1
UserForm1.MultiPage1.Value = 0
CommandButton2
UserForm1.MultiPage1.Value = 1
CommandButton3
UserForm1.MultiPage1.Value = 2
CommandButton4
UserForm1.MultiPage1.Value = 3
Could I use 1 multipage UserForm Instead of 8 UserFoms. Is it better to use 1 multipage UserForm with 8 pages or 8 UserForms. I dont know if this make sense or not?
View 2 Replies
View Related
Mar 15, 2007
I have a workbook that has 9 different tabs in it. Once all the information has been completed throughout the workbook, is there a code that could transfer specific data on each page to a different colum on that same page? I need to do this for 9 pages at one time. The information is listed differently on each page. I need to transfer data from the current data column to the previous data column once the entire workbook has been filed out.
View 2 Replies
View Related
May 18, 2007
how to get rid of the page numbers that appear when using page break preview mode. The page numbers appear in large grey font in the middle of each page and sometimes makes it difficult to read cells. Can I use this view with all the same functionality without that one feature?
View 3 Replies
View Related
Jun 29, 2007
I need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
View 3 Replies
View Related
Nov 4, 2008
Working with 2000 Excel is bringing some new chalanges. I see things are done different. I am trying now to keep my header to repeat from page to page.
For example I have a header that may say something like:
People, numbers, date, ect. And there are so many new rows they go on for pages long. How can I keep my header on the top of each page when it prints out on paper.
View 5 Replies
View Related
Mar 27, 2009
Assume that calculation is set to automatic and not manual, then is every formula on a page updated when any value on the page is changed, or only when the values in the cells pertaining to a particular formula change?
For instance, if cells a1-a10 have values and in a11 is a sum a1-a10 formula, and in b1-b10 are values and a sum b1-b10 formula in b11, if I change a value in range b1-b10 but do not change any values in a1-a10,then as well as the formula in b11 updating will the formula in a11 recalculate or does excel keep track of which cells have changed and thus is aware that the formula in a11 does not need to be recalculated?
View 3 Replies
View Related
