•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Connecting sheets where I add rows in sheet 1 but want data to stay connected and not change on sheet 2?
Our take
Are you looking to maintain a seamless connection between two sheets in your spreadsheet while adding new data? It’s a common challenge to add rows in Sheet 1 without disrupting the alignment of data in Sheet 2. You may already know how to link cells, but the key is to use absolute references. By leveraging functions like INDIRECT, you can ensure that your data remains connected, even as you introduce new information in Sheet 1.
My title sucks. But here’s my problem.
On sheet 1 I have data. Most of is is in row A1, B1, C1…etc.
But in sheet 2 I need to break that down into “tiers” so that it shows sheet 1 A1’s data on sheet 2 A1; sheet 1 B1’s data on sheet 2 B2, sheet 1 C1’s data on sheet 2 C3, etc. etc.
Then let’s say a new group of numbers in sheet 1 starts at row A3
New group at A5, and so on.
Now I know how to connect the cells =sheet1! A1, on sheet 2 etc.
The problem is sometimes I need to add rows to sheet 1, but I DON’T want the data in sheet two to change. So like the data I have on sheet 1 A1, B1, C1 has been tiered to sheet 2 but a new set of data comes in and I have to change A1, B1, C1 but now A2, B2, C2 needs to live down on both sheets.
How do I ensure that the rows moved down in sheet 1, also live down but stay in alignment with sheet 2?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Hot folder data sorting.My goal is to create a hot folder that I am able to continuously drop data sheets into. Then have excel transfer a few specific cells ie.( totals, names )into a uniform row on a new table/sheet. •all data sheets will be formatted the exact same and information will be in the same positions across all of them •data sheets will be added daily •each data sheet needs to create its own respective line representing information from only that sheet •some of the cells needed are a result of total formulas in the data sheet •no information besides the specific cells should reflect in the new table (Photos in comments) P1- example of the data sheets used with the cells circled that are wanted to be transferred. P2- example of what it should somewhat look like when presented in the table/sheet submitted by /u/Notsuspicious444 [link] [comments]
- Info within cells or new sheet?I am trying to figure out the best option for data within a cell. So the company I work for has about 200 properties in an excel spreadsheet. Some are single family up to multi apt complexes. With any property having more than unit for rent, we would like to be able to see a break down by address. I created a tab, and linked the tab to the address on the main tab. But now that new tab is over 400 lines long and it makes it hard to find certain data. Is it possible to make the link take me to the corresponding cells in the new tab? Or can I make something on the main sheet, pop up with a breakdown per unit? Thanks submitted by /u/AcanthisittaNeat7655 [link] [comments]
- Data entry in one sheet modifying values in another sheet?Hi everyone! I’m creating an inventory workbook. What I’d like to do is have one tab with our inventory counts, and another tab with data entry that will modify our inventory counts. For example I start my sheet with 5 blue shirts on tab A; tomorrow Katie comes in and asks for 2 blue shirts, which I will enter on tab B, which will then bring my blue shirt count in tab A down to 3. Is there a way to do this? I figure there must be, but having a hard time figuring it out and Googling it 😂 any tips? Thanks so much!! submitted by /u/penniesfromheaven_ [link] [comments]
- How to autofill data from a row to a column on a different sheet in the same folder?I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs. Hoping someone has the solution I'm looking for and is willing to share.. I've exported questionnaire results from Mentimeter to Excel. The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something. I would like to make this more user-friendly by: 1) putting each respondent's answers in their own sheet in the folder, and 2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'. Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3. The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on. What I'm looking for would ideally display answers in !AnswerA3 through to A80. When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want. When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem. I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that. Would anyone be able to provide me with the correct solution for doing this? I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally.... Thank you Excel wizards! :) submitted by /u/Lost_Mud2097 [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#row zero#google sheets#Excel compatibility#financial modeling with spreadsheets#Excel alternatives#sheet 1#sheet 2#data