Classify Formula Type

E

Excel4444

New Member
Joined
Apr 17, 2017
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I am trying to distill/consolidate the number of formulas in a workbook. I would like to attempt to take a list of formulas (IMAGE A) and where ever the macro identifies a valid cell reference to either retain only the first instance of that formula or replace said cell reference with the term "CELLREFERENCE" (IMAGE C)

Examples (IMAGE B)

I have sorted through several posts, but have not found a solution. any help would be great. thanks in advance.
posts:
www.mrexcel.com

Vb code to check if Excel formula contains a cell reference

Hi I am currently trying to write a code to check to see if the formula in a cell references another cell (cell custom named X_1) its for a uni project. Basicly the user inputs a formula into the cell (e.g. =sin(X_1)*sin(3.14159)) and I want the code to run only if the user references cell...
www.mrexcel.com www.mrexcel.com
www.mrexcel.com

VBA Determining if a cell is Relative or Absolute

Hi all, Excel 97, VBA ... Is there a property or ??? to determine if a formula is absolute or relative?? ie: $A$1 or A1 Or does one have to look for the '$' ... Thanks -Enjoy fh : )_~
www.mrexcel.com www.mrexcel.com

IMAGE A
sample_file.xlsx
ABC
1INPUT LIST
2sheetcellformula
3FormulasB2=namerange1/namerange2
4FormulasC2=namerange1/namerange2
5FormulasD2=namerange1/namerange2
6FormulasB3=namerange1/namerange2
7FormulasC3=namerange1/namerange2
8FormulasD3=namerange1/namerange2
9FormulasB4=namerange1/namerange2
10FormulasC4=namerange1/namerange2
11FormulasD4=namerange1/namerange2
12FormulasB5=namerange1/namerange2
13FormulasC5=namerange1/namerange2
14FormulasD5=namerange1/namerange2
15FormulasB6=namerange1/namerange2
16FormulasC6=namerange1/namerange2
17FormulasD6=namerange1/namerange2
18FormulasB7=namerange1/namerange2
19FormulasC7=namerange1/namerange2
20FormulasD7=namerange1/namerange2
21FormulasB8=namerange10/B7
22FormulasC8=namerange10/C7
23FormulasD8=namerange10/D7
24FormulasB9=namerange10/B8
25FormulasC9=namerange10/C8
26FormulasD9=namerange10/D8
27FormulasB10=namerange10/B9
28FormulasC10=namerange10/C9
29FormulasD10=namerange10/D9
30FormulasB11=namerange10/B10
31FormulasC11=namerange10/C10
32FormulasD11=namerange10/D10
33FormulasB12=namerange10/B11
34FormulasC12=namerange10/C11
35FormulasD12=namerange10/D11
36FormulasB13=namerange10/B12
37FormulasC13=namerange10/C12
38FormulasD13=namerange10/D12
39FormulasB14=namerange10/B13
40FormulasC14=namerange10/C13
41FormulasD14=namerange10/D13
42FormulasB15=namerange10/B14
43FormulasC15=namerange10/C14
44FormulasD15=namerange10/D14
45FormulasB16=namerange10/B15
46FormulasC16=namerange10/C15
47FormulasD16=namerange10/D15
48FormulasB17=namerange10/B16
49FormulasC17=namerange10/C16
50FormulasD17=namerange10/D16
51FormulasB18=namerange4*namerange1
52FormulasC18=namerange4*namerange1
53FormulasD18=namerange4*namerange1
54FormulasB19=namerange6*B18+namerange10
55FormulasC19=namerange6*C18+namerange10
56FormulasD19=namerange6*D18+namerange10
57FormulasB20=namerange6*B19+namerange10
58FormulasC20=namerange6*C19+namerange10
59FormulasD20=namerange6*D19+namerange10
60FormulasB21=namerange6*B20+namerange10
61FormulasC21=namerange6*C20+namerange10
62FormulasD21=namerange6*D20+namerange10
63FormulasB22=namerange6*B21+namerange10
64FormulasC22=namerange6*C21+namerange10
65FormulasD22=namerange6*D21+namerange10
66FormulasB23=namerange6*B22+namerange10
67FormulasC23=namerange6*C22+namerange10
68FormulasD23=namerange6*D22+namerange10
69FormulasB24=namerange6*B23+namerange10
70FormulasC24=namerange6*C23+namerange10
71FormulasD24=namerange6*D23+namerange10
72FormulasB25=namerange6*B24+namerange10
73FormulasC25=namerange6*C24+namerange10
74FormulasD25=namerange6*D24+namerange10
75FormulasB26=namerange6*B25+namerange10
76FormulasC26=namerange6*C25+namerange10
77FormulasD26=namerange6*D25+namerange10
78FormulasB27=namerange6*B26+namerange10
79FormulasC27=namerange6*C26+namerange10
80FormulasD27=namerange6*D26+namerange10
81FormulasB28=namerange6*B27+namerange10
82FormulasC28=namerange6*C27+namerange10
83FormulasD28=namerange6*D27+namerange10
84FormulasB29=namerange6*B28+namerange10
85FormulasC29=namerange6*C28+namerange10
86FormulasD29=namerange6*D28+namerange10
87FormulasB30=namerange6*B29+namerange10
88FormulasC30=namerange6*C29+namerange10
89FormulasD30=namerange6*D29+namerange10
90FormulasB31=IFERROR(namerange7/namerange3,$A$6)
91FormulasC31=IFERROR(namerange7/namerange3,$A$6)
92FormulasD31=IFERROR(namerange7/namerange3,$A$6)
93FormulasB32=IFERROR(namerange7/namerange3,$A$6)
94FormulasC32=IFERROR(namerange7/namerange3,$A$6)
95FormulasD32=IFERROR(namerange7/namerange3,$A$6)
96FormulasA2=namerange1/namerange2
97FormulasA3=namerange1/namerange2
98FormulasA4=namerange1/namerange2
99FormulasA5=namerange1/namerange2
100FormulasA6=namerange1/namerange2
101FormulasA7=namerange1/namerange2
102FormulasA8=namerange10/A7
103FormulasA9=namerange10/A8
104FormulasA10=namerange10/A9
105FormulasA11=namerange10/A10
106FormulasA12=namerange10/A11
107FormulasA13=namerange10/A12
108FormulasA14=namerange10/A13
109FormulasA15=namerange10/A14
110FormulasA16=namerange10/A15
111FormulasA17=namerange10/A16
112FormulasA18=namerange4*namerange1
113FormulasA19=namerange6*A18+namerange10
114FormulasA20=namerange6*A19+namerange10
115FormulasA21=namerange6*A20+namerange10
116FormulasA22=namerange6*A21+namerange10
117FormulasA23=namerange6*A22+namerange10
118FormulasA24=namerange6*A23+namerange10
119FormulasA25=namerange6*A24+namerange10
120FormulasA26=namerange6*A25+namerange10
121FormulasA27=namerange6*A26+namerange10
122FormulasA28=namerange6*A27+namerange10
123FormulasA29=namerange6*A28+namerange10
124FormulasA30=namerange6*A29+namerange10
125FormulasA31=IFERROR(namerange7/namerange3,$A$6)
126FormulasA32=IFERROR(namerange7/namerange3,$A$6)
127FormulasA33=IFERROR(namerange7/namerange3,$A$6)
128FormulasA34=IFERROR(namerange7/namerange3,$A$6)
129FormulasA35=IFERROR(namerange7/namerange3,$A$6)
130FormulasA36=IFERROR(namerange7/namerange3,$A6)
131FormulasA37=A$2+A33
132FormulasB37=B$2+B33
133FormulasA38=A$2+A34
134FormulasB38=B$2+B34
135FormulasA39=A$2+A35
136FormulasB39=B$2+B35
137FormulasA40=A$2+A36
138FormulasB40=B$2+B36
139FormulasA41=A$2+A37
140FormulasB41=B$2+B37
141FormulasA42=A$2+A38
142FormulasB42=B$2+B38
143FormulasA43=A$2+A39
144FormulasB43=B$2+B39
145FormulasA44=1000+$D32
146FormulasB44=1000+$D32
147FormulasC44=1000+$D32
148FormulasD44=1000+$D32
149FormulasE44=1000+$D32
150FormulasF44=1000+$D32
151FormulasG44=1000+$D32
152FormulasH44=1000+$D32
153FormulasH45=1000+$D33
154FormulasH46=1000+$D34
155FormulasH47=1000+$D35
156FormulasH48=1000+$D36
157FormulasH49=1000+$D37
158FormulasA49=namerange1/Sheet5!A14+Sheet5!A4/Formulas!A32
159FormulasA50=namerange1/Sheet5!A15+Sheet5!A5/Formulas!A33
160FormulasA51=namerange1/Sheet5!A16+Sheet5!A6/Formulas!A34
161FormulasA52=namerange1/Sheet5!A17+Sheet5!A7/Formulas!A35
162FormulasA53=namerange1/Sheet5!A18+Sheet5!A8/Formulas!A36
163FormulasA54=namerange1/Sheet5!A19+Sheet5!A9/Formulas!A37
164FormulasA55=namerange1/Sheet5!A20+Sheet5!A10/Formulas!A38
165FormulasA56=namerange1/Sheet5!A21+Sheet5!A11/Formulas!A39
166Sheet5A3=A2+8
167Sheet5A4=A3+8
168Sheet5A5=A4+8
169Sheet5A6=A5+8
170Sheet5A7=A6+8
171Sheet5A8=A7+8
172Sheet5A9=A8+8
173Sheet5A10=A9+8
174Sheet5A11=A10+8
175Sheet5A12=A11+8
176Sheet5A13=A12+8
177Sheet5A14=A13+8
178Sheet5A15=A14+8
179Sheet5A16=A15+8
180Sheet5A17=A16+8
181Sheet5A18=A17+8
182Sheet5A19=A18+8
183Sheet5A20=A19+8
184Sheet5A21=A20+8
185Sheet5A22=A21+8
186Sheet5A23=A22+8
187Sheet5A24=A23+8
188Sheet5A25=A24+8
189Sheet5A26=A25+8
190Sheet5A27=A26+8
191Sheet5A28=A27+8
192Sheet5A29=A28+8
193Sheet5A30=A29+8
194Sheet5A31=A30+8
195Sheet5A32=A31+8
196Sheet5A33=A32+8
197Sheet5A34=A33+8
198Sheet5A35=A34+8
199Sheet5A36=A35+8
200Sheet5A37=A36+8
201Sheet5A38=A37+8
202Sheet5A39=A38+8
input_list




IMAGE B
sample_file.xlsx
GHIJKLMNOPQRSTU
21EXAMPLE
22
231input = formula
242output = (1) OR (2)
25(1)namerange10/B7 - in this option the first formula in the "group" is retained only
26(2)namerange10/CELLREFERENCE - cell reference is replaced with term "CELLREFERENCE" so that the list can simply be depupped
27Type of cell ref.translation
28a.=A1CELLREFERENCE
29b.=$A$2$CELL$REFERENCE
30c.=A$3CELL$REFERENCE
31d.=$A4$CELLREFERENCE
32
33
34
35
36sample from each group
37(1)=namerange10/B7
38(2)=namerange10/CELLREFERENCE
39
40(1)=namerange6*B18+namerange10
41(2)=namerange6*CELLREFERENCE+namerange11
42
43(1)=IFERROR(namerange7/namerange3,$A$6)
44(2)=IFERROR(namerange7/namerange3,$CELL$REFERENCE)
45
46(1)=namerange10/A7
47(2)=namerange10/CELLREFERENCE
48
49(1)=namerange6*A18+namerange10
50(2)=namerange6*CELLREFERENCE+namerange11
51
52(1)=IFERROR(namerange7/namerange3,$A$6)
53(2)=IFERROR(namerange7/namerange3,$CELL$REFERENCE)
54
55(1)=A$2+A33
56(2)=CELL$REFERENCE+CELLREFERENCE
57
58(1)=1000+$D32
59(2)=1000+$CELLREFERENCE
60
61(1)=namerange1/Sheet5!A14+Sheet5!A4/Formulas!A32
62(2)=namerange1/Sheet5!CELLREFERENCE+Sheet5!CELLREFERENCE/Formulas!CELLREFERENCE
63
64(1)=A2+8
65(2)=CELLREFERENCE+9
input_list


IMAGE C

sample_file.xlsx
ABCDEFG
3TYPE 1ORTYPE 2
4sheetcellformulasheetcellformula
5FormulasB8=namerange10/B7FormulasB8=namerange10/CELLREFERENCE
6FormulasB19=namerange6*B18+namerange10FormulasB19=namerange6*CELLREFERENCE+namerange11
7FormulasB31=IFERROR(namerange7/namerange3,$A$6)FormulasB31=IFERROR(namerange7/namerange3,$CELL$REFERENCE)
8FormulasA8=namerange10/A7FormulasA8=namerange10/CELLREFERENCE
9FormulasA19=namerange6*A18+namerange10FormulasA19=namerange6*CELLREFERENCE+namerange11
10FormulasA31=IFERROR(namerange7/namerange3,$A$6)FormulasA31=IFERROR(namerange7/namerange3,$CELL$REFERENCE)
11FormulasA37=A$2+A33FormulasA37=CELL$REFERENCE+CELLREFERENCE
12FormulasA44=1000+$D32FormulasA44=1000+$CELLREFERENCE
13FormulasA49=namerange1/Sheet5!A14+Sheet5!A4/Formulas!A32FormulasA49=namerange1/Sheet5!CELLREFERENCE+Sheet5!CELLREFERENCE/Formulas!CELLREFERENCE
14Sheet5A3=A2+8Sheet5A3=CELLREFERENCE+9
output_list
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You must log in or register to reply here.

Similar threads

gtd526
  • Question
search and answer only ODD rows
Replies
6
Views
137
gtd526
gtd526
S
  • Question
Formula to identify if a range does not match other ranges
Replies
0
Views
206
Stuepef
S
B
  • Solved
Array Returning "0" value
Replies
5
Views
184
Fluff
Fluff
B
  • Solved
Parent Child Relation
Replies
7
Views
909
Peter_SSs
Peter_SSs
N
  • Question
Highest Value(s) displaying text
Replies
8
Views
313
Fluff
Fluff

Forum statistics

Threads
1,214,606
Messages
6,120,485
Members
448,967
Latest member
visheshkotha

Share this page

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