Link formula to a range of cells based on a condition

Resolved · High Priority · Version 365

Sat 11 Sep 2021, 15:41 Delegate Ajay said...

Ajay has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Link formula to a range of cells based on a condition

Hi Jen,

I have a spreadsheet for budgets and once I have populated the data from ERP system. I need to link the a formula to a range of cells within the same row where the account code is = 3999. The formula is ='Sheet 1'!I10

Example, IF account code in Column B =3999 is found at row 27,then on the same row across a few columns say Colum I:0, I need to copy the fomula ='Sheet 1!I10 in cell I27, then pull it across to Column O and the color the cell orange and lock the cell too.

So cell I27 will have this formula = 'Sheet 1!'I10
Cell J27 will have the formula = 'Sheet 1!'J10
Cell K27 will have the formula = 'Sheet 1!'K10 etc

Once the cells the filled in, those cells I27 to O27 needs to have fill color as orange the cells to be locked too.

Once the sheet is protected those cells also become protected.

Can these codes be dynamic in the sense, on workbook 1 the account code 3999 in Column B could be at row 27, whereas in workbook 2 it could be at row 36 .

Your guidance will be appreciated.

Regards

AJ

Wed 15 Sep 2021, 10:31 Trainer Jens said...

RE: Link formula to a range of cells based on a condition

Hi Ajay,

Thank you for the forum question.

It is not an easy task. I assume that you have many account codes. Can you have the same account code multiple times on the same worksheet?

I would create an Array to store all the account codes and then use the Find function to find the account codes to return the row numbers.

Then you can store the row numbers for the account codes in the array for each account code. Then the Address function can help you with the destination cells where you want the formulas to be pasted.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 16 Sep 2021, 17:27 Delegate Ajay said...

RE: Link formula to a range of cells based on a condition

Thank you Jen for your reply.

I do have the account code 3999 multiple times, but as the account codes are sorted, they tend to be in adjacent rows (max 4 or 5 rows).

I also have situation where the account code 3999 appears once in the row in the sheet and then the formula needs to be copied across after using offset and moving across a few columns, but staying on the same row.

Is it possible to provide a sample example, then perhaps I would modify it to suit our needs or build on it to find a solution.

Regards

Ajay

Tue 21 Sep 2021, 12:53 Trainer Jens said...

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Shortcuts for working with named ranges in Excel

If you are working with or creating named ranges in your spreadsheets, then you may find the following shortcut keys useful.

- Bring up the Define Names dialogue box on screen by using Ctrl + F3 (instead of going to Insert - Names).

- Create Names from labels you have entered into the spreadsheet by highlighting the labels and related figures, then hold down Shift + Ctrl + F3. You can then choose to create names from the top or bottom rows, or left or right columns.

- Go directly to a named range by hitting the F5 key. The Go To dialogue box will open and display any named ranges in the spreadsheet. Simply select the named range to navigate to it in the spreadsheet.

View all Excel hints and tips