I have a workbook with a sheet per day, each sheet has a variety of 'jobs' as columns and a variety of people and kit as rows. Hours are manually input each day during the month.
Simplified version... These are two separate sheets 'M060114' and 'Tu070114'.
Monday Dig Drive
Dave 4 5
Bill 2 7
Tuesday Dig Drive
Dave 2 7
Bill 8 1
I have a Pivot Table on a separate sheet. This was created using the Wizard (ALT +D +P).
Dave 6 12
Bill 10 8
So far, lovely.
But when I double-click on a number or right-click and choose 'Show Details' I don't get the sheet names in the newly created information sheet. I get the Row label, column label and a list of the entries.
So if I clicked on 10 above...
Row Column Value
Bill Dig 2
Bill Dig 8
That's fine with just a couple of entries but with about 80 columns and 250 rows spread across a month, therefore 30ish sheets, it's difficult marrying up the 'Show Details' sheet to the actual data.
So after all that, the question. Is there a way of getting the sheet name to appear in the 'Show Details' information sheet?
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
I have a Workbook containing seven or eight Worksheets. The first Worksheet is a data entry and options selection sheet displaying a selection of Textboxes, Option buttons, Combo boxes etc, the second sheet contains reference data, following these are a number of Worksheets that are calculated and filtered as a result of those options and inputs. A 'print' button on the input Worksheet then runs a Procedure that applies the relevant filters, assigns print areas and prints out the 4 or 5 filtered worksheets. I now want to expand this Procedure to take a copy of just these filtered Worksheets and save them as another Workbook (values only) for subsequent free editting.
I have started by selecting the relevant cells on the first filtered Worksheet, copying, opening a new Workbook, renaming the first Worksheet to match the one I'm copying, PasteSpecial formats, PasteSpecial the values, go back to the original Workbook, select the relevant cells on the next Worksheet, copying, opening the new Workbook, renaming the Worksheet etc etc. Now this seems to be a very labour intensive approach and I'm wondering if there is an easier way and how to do it. Ideally something like... taking a copy of the whole Workbook somehow changing all the cells to values only, rather than formulars then deleting the first two Worksheets (input and data ones).
I am trying to copy a range of valid cells "non-blank" from "sheet_a" ,"sheet_b" ,"sheet_c" to "sheet3" i was successful to copy from one sheet only. how to copy from all the sheets listed from the same workbook.
following is the VBA code i am using
Sub CopySample() Dim shSrc As Worksheet Dim shDst As Worksheet
I have mananged to find and tweak the following code in order to serve my copy / paste to master sheet requirements.
At the bottom of the code I have added a delete rows based on column contents routine although I'm not sure if it is actually the right one to use.
I have included it in the same module in order to tidy up the whole process and have it all operate under one click.
As stated on the sheet in this exmple, The paste could consist of any number of sheets although the range is always the same on each sheet. (only amounting to around 20 rows that we are dealing with so no reams of data with odd empty rows that would take an age to find otherwise).
The data can look like it does on Sheet 2 here and also could look like it does on sheet 3. (this data is coming from a sales rep's order sheet)
From the routine in module 1, I just don't know where an "add an empty row" or delete all empty rows bar 1" code would need to be inserted in order to keep the process going until all sheets are copied.
I'm aware that with the delete code at the bottom, the whole copy process is completing before the delete process then does it's bit so think I'm on the right track in thinking that the delete all empty rows but leave one" requirements needs to be further up the code but I just don't how to get the result I need or where that code should break into the routine.
The only other way I can think to acheive the result is to allow the range to increase by 1 row on the form but somehow make sure that the last row in the range contains a character in column B to "trick/force" the delete routine to leave that row in. That would be do-able but the trouble is, how do you get a value into a cell that the delete proces would treat as data but not be visible so keeping my spacer line tidy? I've tried just putting a space in the last row of the range in column B but the row still gets deleted.
I have an interesting problem on my hands. I work for a gas station, and for our cigg orders, we fax an excel speadsheet to SAMS CLUB. I recently have done some modifications by applying conditional formating to indicate negitive numbers and zeros, however, I am trying to find a way to blank out 2 adjacent cells when a cell = 0.
Here is a sample:
Order# Desc #to order
921934 (brown) Bx 0
The Zero is already whited out in my sheet, however I am trying to find a way to make the Order Number and Desc. be whited out also when the Number to order = 0
If I have 2 columns (A with data to be extracted, B for output). And 11,000 rows of data. How do I take information from column A and out to column B. For example, I want to identify characters in Column A, for example I want to identify "A" "#" and "SQ" to output. For example If column A, has "A" then Invitation, If column A has "#" then Business, if column A has "SQ" then square
I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1".
Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...
I have a Listbox (Listbox2) in column C. The listbox allows users to select multiple items (in this case it is countries). So the user could select 1 or 20 countries based on the previous criteria.
What I would ideally like is for the next column (column D) to automatically list the selected items without having to use a command button in the actualy cell. However, I could put a command button at the end to say "Confirm" which would then transfer the above into the relevant cells.
This is an excel file, and each row would works the same. Basically users are entering products for set up row by row, so the file could be 10 rows or 200.
Just to be clear, it would look a little like this:
Drop Down Listbox Column C Column D
Canada (Selected) Canada, USA South Africa USA (Selected)
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)
it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!
i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).
any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!
should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
I have the following code in Destination.xlsm that is intended to:
request the user to select a multi-cell range in a column of single-sheet Source.xlsm, in which some but not all cells contain "Y" (to indicate that this row of data relates to a National Account, versus a Territory Account)loop through the selected range, and whenever a cell contains "Y", copy the entire row and insert it in Destination.xlsm above a cell named "rngDest".
Const Message As String = "Select the entire range containing National Account Y flags, then press Enter or click OK." Const Title As String = "Copy National Accounts"
Dim rngSource As Range Dim rngDest As Range Dim r As Range Dim c As Integer Dim wbk As Workbook
I'm looking to auto populate "K7" with a color to indicate project status (i.e. green, yellow, & red) based on a percentage in "K1". The tricky part for me is I want a date range in G58 & G59 to drive the color in K7. So, when I enter a percentage in K1 and (for example I'm not at 90% with 3 days left in the project K7 will be RED.
Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet. 1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12. 2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
I have a listbox that has row and columns. The rows are combinations of options and the columns are Additives.
After selecting a row in Listbox1, (first column are names) i would like the value of the second column to goto a specific area.... find that value and in the cell to the right of it place a "Y". The "Y" would indicate that "yes" it was part of the selection row of the listbox. Same for third column, forth, fifth and sixth. The result from the listbox is a number of Y's and N's in the result page. Then making all that were not part of selected line and equal to "N" to hide row (height = to zero).
Listbox and expected result are in attached example worksheet.
The attached does show the need much better then I can explain it.
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court B2 Hampton Heath C2 Hampton Town D3 Hamptonshire E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
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: