Pasting formulas in non filtered ranges into filtered ranges

TheSingularity

New Member
Joined
Jan 7, 2014
Messages
1
I am about ready to go cuckoo for cocoa puffs over this. I have tried EVERY workaround, formula etc. etc. This is what I am trying to do:

On worksheet 1 we have (lets say A1-A4) the values (A1)ABCD, (A2)EFGH, (A3)IJKL, (A4)MNOP. In column B we have (B1)1234, (B2)5678, (B3)9101112, (B4)13141516. Then in column C we have (C1)=concatenate(A1,B1), (C2) =concatenate(A2,B2) and you get the picture.

Now here is the fun part!

On worksheet 2 we have (A1)Microsoft, (A2)Excel, (A3)I, (A4)Hate and (A5)You. Now in A6 we have (A6)=sheet1!A1.

Basically in a nut shell I am trying to:

A) Filter out A1-A5 on the second sheet so only A6, A12, A18 etc show but to where I can drag the formula in (A6) down and get (A6)=sheet1!C1 [which results in ABCD1234], (A12)=sheet1!C2 [EFGH5678], (A18)=sheet1!C3 etc etc.

B) Not do the monkey work to accomplish (A) manually

C) Not spend a decade to write a god**** macro to format whats actually a macro itself! lol

As an aside the values on sheet 1 A1-A4+ and B1-B4+ change. I am essentially "templating" a VB script where certain values change (ie A1-A4 etc) but the rest of the code outside the filter on sheet 2 remains constant and unaffected by dragging the formula down (ie Microsoft excel I hate you remains unchanged and repeats in between said cells with the formulas). Just want to paste/drag drop into the VISIBLE cells only.

Any thoughts suggestions, feedback or pointers are welcome. Die Excel. Die >_<!!!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,130,297
Messages
5,641,415
Members
417,209
Latest member
Agbarker

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
Top