Manas S.
asked • 05/16/20Copy a column in excel to another area of a sheet that matches a field in the top
I have some data trying to organize using excel functions, one of the question i have is below.
I have 2 tables in one sheet (those can be in two sheet/tabs also). In table 1, the data in Apr-2020 column needs to be copied to table 2 that has the same title 'Apr-2020'. Both tables will have same number of rows. For example, here is table-1. Lets assume current month is Nov-2019.
<Can't add the sample table>
We need to create a table-2 for last 3 months starting from the Current-Month value, taking the values from the table-1. We don't like to do copy/paste to avoid any errors. The table-2 as below is already exising, it just needs to update with the lastest 3 months of the data. The titles of the below table-2 is calculated with EDATE(of the Current-Month) field, and with -1, and -2 for the previous two months.
More
2 Answers By Expert Tutors
Art B. answered • 05/25/20
Tutor
5
(4)
Learn Excel, Word, Access and Scrum, Update and repair as well
Can you paste a picture of your sheet? It's hard for me to visualize exactly what you're driving at without seeing it. If you can't paste it, I'd be happy to take a look for free and advise you. What you copied and pasted below just came over as text.
Art B.
One general piece of advise however is to use absolute or relative references where appropriate. If either dataset would ever expand, you want to allow for that.
Report
05/25/20
Manas S.
Thank you for the response. I could not attached the picture/snippet, but here is a text form of cut/paste. Current-Month Apr-2020 Metrics-2018 2018-01 2018-02 2018-03 2018-04 2018-05 2018-06 2018-07 2018-08 2018-09 2018-10 2018-11 2018-12 Total AWS Revenue Total $236,427 $399,394 $59,299 $158,211 $116,585 $117,139 $124,311 $180,400 $116,897 $36,755 $116,191 $93,956 $1,755,565 ES Revenue Total 12,973 18,521 10,082 9,343 7,361 7,388 7,729 10,400.00 7,257.00 7,436.00 7,342.00 6,284.00 $112,116 Contribution Margin 85.18% 85.95% Metrics-2019 2019-01 2019-02 2019-03 2019-04 2019-05 2019-06 2019-07 2019-08 2019-09 2019-10 2019-11 2019-12 Total AWS Revenue Total $170,781 $141,619 $133,390 $87,594 $211,589 $86,734 $95,228 $5,196 66,241 130,343 14,256 49,781 $1,192,752 ES Revenue Total $9,589 $9,589 $9,210 $9,195 $13,862 $9,291 $15,000 $15,000 $15,000 $15,000 $15,000 $15,220 $150,956 Contribution Margin 81.30% 73.52% 88.47% 93.44% 88.33% 73.61% 62.08% Metrics-2020 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 2020-01 2020-02 2020-03 Apr-2020 May-2020 Jun-2020 Jul-2020 Aug-2020 Sep-2020 Oct-2020 Nov-2020 Dec-2020 Revenue Total $35,987 $5,196 $34,235 $39,456 $14,256 $34,390 $120,290 138,234 144,385 122,680 Support Revenue $8,234 $8,390 $10,234 $12,345 $11,234 $10,890 $12,678 11,567 11,234 12,223 Incoming Cases 5 5 1 1 4 5 0 4 1 2 Percentile TTR 0.64 0.64 0.15 0.49 1.23 4.63 8.27 2.19 8.24 5.8 Metrics Sep-19 Oct-19 Nov-19 Trend Revenue Total Support Revenue Incoming Cases Percentile TTR
Report
05/26/20
Patrick B. answered • 05/17/20
Tutor
4.7
(31)
Math and computer tutor/teacher
Either copy and paste or reference the cells from the other sheet
Here the formula to do that
='Sheet Name'!cell
For example
='Totals'!C5
references cell C5 on the sheet names Totals
Manas S.
Sorry for late reply. I can not copy or reference the source cell. It needs to search one of the column that matches with the current month, and calculate accordingly.
Report
05/26/20
Still looking for help? Get the right answer, fast.
Ask a question for free
Get a free answer to a quick problem.
Most questions answered within 4 hours.
OR
Find an Online Tutor Now
Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.
Manas S.
Trying to copy the table-1 Metrics-2020 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Revenue Total $35,987 $5,196 $34,235 $39,456 $14,256 Support Revenue $8,234 $8,390 $10,234 $12,345 $11,234 Incoming Cases 5 5 1 1 4 Percentile TTR 0.64 0.64 0.15 0.49 1.2305/16/20