CODEWITHSUNDEEP
vbaexcelexcel-formula
1nullpointer
1nullpointer

Reputation: 1262

Excel - Automatically Generate Reverse Sheet

Am trying to automate a workflow . Basically its a Bus schedule timetable .

Say , I have a worksheet with multiple columns And a Set Formula to calculate Reach Time Between Various Stops . I want to calculate another sheet with the Return Journey Timings .

Source Sheet : One Way Bus Route

Automatically Generate Reverse Sheet with Modified Formula: Reverse Route

The New Reverse Sheet Should Copy the columns in the Reverse Order . And modify the original formula using the time.

Target : Create a Sheet where I enter a start Time , the other times should be automatically populated . I have the start times for the sources Idea is - once I populate One way route with a formula , create a new sheet with return journey details and give an option to enter start time from the return source .

Could this be done in Excel Macros or any faster approach ? Tried the excel record option but that takes the hard coded column names. Its not dynamic as to if i have a new route with different number of stops , i want it to generate the reverse order sheet based on original

Appreciate some help . Thanks !

Upvotes: 0

Views: 148

Answers (1)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

I have not tested in Excel, but this uses functions that are shared between Sheets and Excel, so this should work. You can jazz this up to use sheet references, but I kept it simple.

Assume your forward schedule sits in rows 10 and 11, starting in column A. In A13 enter (columns matching matters here)=iferror(offset($A10,0,counta(10:10)-COLUMN()),"") dragging it right as far as you can imagine stops going as well as down one row (for cosmetics you will hide this row (14 in mine) later). Then one row down from that you can enter (and mess around with) whatever time you want in the first column. In column B next to it, place =A15+A14-B14, dragging that right as far as the last stop. These times will be right and will adjust as you change the contents of the first column or the rows on which things are based. You will get nonsense if you drag things further right. (probably there is a way to fix that)

Upvotes: 2

Related Questions