How do I dynamically create a new list from an existing list?

russgc

New Member
Joined
Jan 25, 2012
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
I want to build a dynamic list from an existing list.

From the image you can see the list of budget items in column C. Some of those items are "savings items". Meaning I don't buy tires every month, but I want to save a little every month towards a future purchase of tires.

The list in Column I is a list I want to dynamically create from the Budget Items list in C. If you notice there is an Asterisk in column B (could be any character) that indicates if that budget item is a "savings item".

Question: How can I create a list of savings items by searching for the asterisk and populating the item and monthly savings amounts in column I, L, and M? At any time I could change any item in column B to a savings item by placing an asterisk next to the item. I would then expect the list in column I to reflect that new change.
 

Attachments

  • Budget-List.jpg
    Budget-List.jpg
    133.3 KB · Views: 23

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Russgc,

You can use AGGREGATE to retrieve each * row in turn.

Russgc.xlsx
BCDEFGHIJK
1
2Marked *1st Half2nd Half
3IndItem1st half2nd halfMonthlyYearlyTires3030
4Auto Loan4004004800Oil Change2020
5*Tires303060720Reg1515
6*Oil Change202040480Clothes2222
7*Reg151530360Dry Cleaning1212
8Gas50501001200   
9Bus Pass00   
10*Clothes222244528   
11Toiletries111122264   
12*Dry Cleaning121224288   
Sheet1
Cell Formulas
RangeFormula
I3:K12I3=IFERROR(INDEX(C$4:C$999,AGGREGATE(15,6,ROW($B$4:$B$999)-ROW($B$3)/($B$4:$B$999="*"),ROW()-ROW($I$2))),"")
F4:F12F4=SUM(D4:E4)
G4:G12G4=F4*12
 
Upvote 0
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option if you have 365 & the filter function
+Fluff v2.xlsm
ABCDEFGHIJK
1
2Marked *1st Half2nd Half
3IndItem1st half2nd halfMonthlyYearlyTires3030
4Auto Loan4004004800Oil Change2020
5*Tires303060720Reg1515
6*Oil Change202040480Clothes2222
7*Reg151530360Dry Cleaning1212
8Gas50501001200
9Bus Pass00
10*Clothes222244528   
11Toiletries111122264   
12*Dry Cleaning121224288   
13
14
Main
Cell Formulas
RangeFormula
I3:K7I3=FILTER(C4:E100,B4:B100="*")
F4:F12F4=SUM(D4:E4)
G4:G12G4=F4*12
I10:K12I10=IFERROR(INDEX(C$4:C$999,AGGREGATE(15,6,ROW($B$4:$B$999)-ROW($B$3)/($B$4:$B$999="*"),ROW()-ROW($I$2))),"")
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top