I have a master tab that has data grouped as follows
Column 1
title of a section (written once then left blank for the rest of a section)
Column 2
numerous descriptors (each in a new row)
Column 3
The response to the descriptors in column 2
I want to link entire sections from column 2 and 3 into different excel files so that if I make changes to column 2 or 3 in the master tab, the changes are made to all tabs. If there is a way, I'd like it if I add a row to the master, it is automatically added to the others.
I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.
Part 1:
I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up...
I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up... I can SEND THE SHEET IF YOU WISH....
I have cells of block addresses that are delimited (tab I believe - there's a small square that appears).
I need to split the addresses out into separate columns for each line of address. I tried using the Text to Columns wizard as suggested by Excel Help, but ended up with only the first line of address being posted in the cell to the right (the rest of the address was nowhere to be seen).
I'm sure I've done this before but can't for the life of me remember how I did it.
I saw a post that needed a solution for moving blocks of cells in order to get fewer rows but more columns. The post is gone but I'd like to think my work wasn't in vain as the algorithm was more difficult than I thought. The problem was wanting make a 5000 X 4 grid into a 1700 X 12 grid, 55 rows and 4 columns at a time. This is sometimes done for visiblity or printing purposes to get more data going across instead of down. An example would be wanting to have more data accross on a print page and the page holds 55 lines of data.
For example, A56:D110 would be moved to E1:H55, A111:D165 to I1:L55, A166:D220 to A56:D110 etc. The code is somewhat generic so different blocks of rows and columns can be specified. To see this code work, enter the number 1 in columns A-D, then fill series -> step by one for a thousand or so rows. Then run the code.
Sub FewerRowsMoreColumns() 'Rearranges blocks of cells so the end result is more columns and fewer rows Dim RowStop As Long Dim ColStop As Integer Dim RowStep As Long Dim ColStep As Integer Dim RowOffset As Long Dim rw As Long Dim col As Integer
ColStop = 12 'Enter the last column number you want the cells moved to RowStep = 55 'Enter the number of rows you want to move at one time ColStep = 4 'Enter the number of data columns you are starting with.....................
create cells which could be expanded/shrunk, to either show the first sentence or so of the essay, or the full essay. I'd need to be able to expand/collapse each cell individually, not by row or column as the tutorials I've found have shown. I'm pretty sure I've seen this done with a little +/- sign in the corner of the cell but I can't find the option. It would be a bit like on a website that have show more, show less buttons.
i think this will be simple for most of you. i need a macro that will move blocks of cells from one column into the succeding columns. say i have one column of 1000 values and i need to break that up into columns of 50.
I have three sheets "Contacts"/Sheet 1, "Judges"/Sheet 2, "Sponsors"/Sheet 3. Row 1 and 2 have been used as headers in each of them.
I would like to be able to enter a value in A3 of Sheet 1, such as 'judge' (a number would work fine too), and have the contact information in the same row (columns B-M) copy into the next available row of Sheet 2 in columns B-M.
I don't know if a formula exists for this but if I could type anything in the formula box and have Excel be smart enough to figure out what I'm asking I'd type something like this:
"Hey Excel. Follow this formula: IF 'judge' in sheet 1, A3; THEN copy sheet 1, B3-M3 to sheet 2, B-M next available row...Then do the same thing for 'contacts' but copy into sheet 3."
So I have a list of names, and I need to keep track of whose paperwork I have. There's a column that says Yes/No.
I would like to link everything to another sheet in the file. So for every name that says "No," I would like that name to appear in the next sheet. And when someone updates the first page to put in a "yes," I want it automatically taken off the second sheet.
I have a large workbook, split into sheets, with very complex formulas in it. I want one sheet at the end of this workbook to reflect all the data from the other sheets, if you know what I mean - so it is ALL the data from the different sheets, in one "all info" sheet.
I also want it to update automatically so I don't have to re-enter lots of data when updating the separate sheets. Is there any way of doing this? I have looked at some of the answers on here but I got very lost.
I have a table in sheet1 in excel containing 10 rows. I want to copy the first column of this table into sheet2 in the same workbook but what I want is that in case I inserted a row or more in Sheet1, I wanted to be copied to sheet2 automatically. How can I do this?
I am trying to link 2 excel sheets together and I am wondering if using the SUMIF Function is the best way to do this. I will try to explain and illustrate my position.
First I have a sheet called Master where I import my companies inventory and prices. It contains ALL our inventory. Looks like this:
(Column A) (Column B) (Item Code (Item Price (ROW1) THUMBRID $5.00 (ROW2) THUMTUBE $2.00 (ROW3) ZPIN $0.30 Now this master sheet is changing all the time as we import new products and prices all the time. So, this is a "list" where I can sort it by Item Code alphabetically.
Now, I am trying to make another worksheet with a list of what each of our kits contain and there prices. I will call this ThumbPiano. Like this:
Column A: COLUMN B: THUMBRID $5.00 THUMTUBE $2.00
We have many different kits, that only take a couple of products, and our inventory list is at like 3,400. I want to be able to go into the Master sheet and update an Item price and have it automatically update it in whatever worksheet contains those items. But how can I do this if I add an item into the workseet. Then the cells move and whatever data was in that cell before is not there now.
So, that is why I chose the SUMIF function. Maybe there is a better one? Here's what I tried...assuming the two lists before, and the code Zpin is extra in the Inventory list because we do not use it in the thumb piano hardware pack.
I tried: =SUMIF([Master.xls]Sheet1!$A$1:$A$3,A1:A2,[Master.xls]Sheet1!$B$1:$B$3)
It works, but then when I save it and close out of it and then re-open the ThumbPiano sheet it asks me if I want to update the sheet. I click yes, and then all the cell says "#VALUE!" "A value used in the formula is of the wrong data type." Please let me know what is going wrong. I know there has to be a way to do this. I just wonder if the SUMIF formula is not the way to link 2 sheets together...but if it isn't please keep in mind that I have this looking through 3,400 parts and I will be adding to them all the time, so it needs to search through all those parts and display the correct price....
I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").
I have two excel sheets, one has partial information (displayName, title, company, streetAddress, city, state, postalCode, Pager, homephone, fax) the other has the missing information that I need on the first sheet.
Sheet 1 (DB1, has partial info) and Sheet 2 (DB2, has the missing info). I need to somehow link these two, and what they both have in common is name. DB1 has "displayName" which is just First Name Last Name (e.g. John Smith). DB2 has First Name and Last Name, but i'll concatenate that to a new column named "displayName" ( which I assume will be needed to link? ).
The missing information in DB1 is title / streetAddress / city / postal code. DB2 has "Location" (which is a company code, and I want to replace the code with the address, city, postal code) and "Group" (which is title).
To make it easier, I could just do a find and replace on DB2 (e.g. A01-DFW-HWY67) and do it that way right? Or is there any easier way to do that?
Other than that though, how would I link DB1 and DB2, using displayName and fill out DB1 with the information from DB2?
I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
I have large data sheets with blocks of blue-colored cells. I want to surround them with lavender-colored cells. Then I want to run a command to Clear Contents of all lavender-colored cells. Is this possible?
So first I have: Black-text blue-text blue blue Black-text blue-text blue blue Black-text
Then I want: Lavender Blue Blue Blue Lavender Blue Blue.................
I am currently working with a hidden sheetname "hide_template" which contains formula linking to another sheetname "rawdata" of same workbook.
In this "rawdata", the contents is updated from time to time.
The problem is when I open this workbook and update the entries in "rawdata", the formulas/results in "hide_template" is not updated and invalid (ie. #N/A, #VALUE..)
This is the overview of process that Im working with:
===> open workbook ===> paste new entries on "rawdata" ==> run a macro to copy range in the "hidden_template" to paste to new sheet of same workbook
I already have a working macro to copy range from the "hidden_template" which works as follows:
===> set to visible the "hidden_template" ===> copy range from "hidden_template" ===> paste to new sheet ===> set to hidden the "hidden_template"
I have a spread sheet for my checking accounts. There are 3 sheets, BW, QW, and Chase CC. In the 1st 2 sheets row D is named "Category" and the 3rd sheet has the same name but is in column C.
How to link the 3 sheets to a summary sheet keeping track and totals of the categories ?
Example in sheet BW I have a category called "Utilities" and the column to the right of that is the amount. Then ect for other categories.
I'm wondering if there is a way of linking to cells by looking up part of the link in another cell. E.g.In Sheet1, cell A1, i have some data that I want link to from Sheet2. Normally I'd do that by using "=Sheet1!A1" in the cell in Sheet2.
But I'd like to lookup the "A1" part of the link from two different cells in Sheet 2. Something like: =Sheet1!(Text from B1 in Sheet2 AND Text from A2 in Sheet2) or: =Sheet1!(Sheet2!B1&Sheet2!A2). So that if Sheet2!B1 would contain an "A", and Sheet2!A2 would contain a "1" the resulting link would be the correct "=Sheet1!A1"
Example: Lets say I have a workbook with 6 sheets on it and the sheets names are:
Sheet1 Sheet2 Sheet3 Sheet4 Sheet5 Status
And we will just use cell A1 through sheet1 to sheet5 and the status sheet to grab the numbers from sheet 1 through sheet5 Sheet1 through sheet5 will have numbers in cells A1
And in the status sheet I would like to find the highest number from these 5 sheets and put it in cell A1 status sheet and have it linkable like when it finds the highest number it will put it in cell A1 on the status sheet and when I click on the A1 cell in the status sheet I would like it to goto that sheet that has the highest number it found and maybe highlight it or something.
I need to have three rows link to each other. The first column cell A is a paste link and is always changing. The second B is a vlookup referenceing A and finding it in a changing table...the source for the paste link. C needs to follow the other two cells and remain "attached" to it. However, C has to be a cell that is hardcoded somewhere, meaning that C is not found in the source data and must be determined by a person. I have tried to link the sheets through vlookups and other formulas, however, this only works when the data is sorted and nothing new is added. I need a static sheet that can take in new values. I need to create some sort of relationship between the cells
I have list of names of people in a billboards league. Directly to the right of everyone's name in the adjacent cell is their "win percentage". These percentages are sorted in descending order and I need the names to move along with the adjacent cell as the percentages change. When i merge the cells things get really messy.
I have a VBA code that pulls a value from a single cell on a separate tab. Instead of just pulling the value (ex. 3.44589348) is there a way to have it pull the value as a link to the other tab? It still would display the number but if you click on the cell it would display =Sheet2!A1 (for example).
How do I link two cells so that when I change one value the other changes and vice versa?
How about 3 cells?
Example:
Cells A1, A2 and A3 are all the same part number, but in diffrent catagories. If I used a part and reduce my number in A2 I want it to reflect the change in A1 and A3. The next day I use a part and reduce it in A1 and I want the new value to reflect in A2 and A3.
I can make it so A2 will equal A1, but if I was to update the value at A2 it overwrites the formula.
I am trying to link data from one workbook into another and get it to work but it puts in sample data or dates which I dont want. How can I set it up were the cells are blank until data is input in the main sheet and then linked to the destination sheet?
i am trying to get data from my original (vertical data) to copy to other group of cells (horizontal) without having to refer to every single cell to be copied. is there a way to get excel to do something like this: if b12=x then c12=next - but for 5 rows.
i am such a excel newb but i have asked some guys who say they know excel and i left them scratching their heads. i have all the data in i need in other sheets.
