Cara menggunakan dynamic cell reference excel

Firstly, thank you for creating such a comprehensive article! Extremely helpful to many, I'm sure. Secondly, I don't think this specific post can help with my issue, but perhaps you can point me in the right direction?

I have a template that I am creating (so formulas will need to be dynamic as rows will be added) where I'm trying to continue the row numbers while skipping headers. Here is an example of what it should be when complete:

A B C
1 data data
2 data data
3 data data
Header
4 data data
5 data data
6 data data
Header
7 data data

The issue is that the template doesn't look like the example above, as the data isn't populated yet. The template looks like this:

A B C
formula data data
Header
formula data data
Header
formula data data

How can I start numbering in A1, skipping headers and continuing numbering, without being able to tell Excel where the last number above Header 1 left off?

Thomas says:
March 25, 2022 at 7:36 pm

This is an excellent article. My problem seems not addressed exactly with the INDIRECT and ROW syntax

Here is what I am trying to do:

I have a row of stock values in row 12 in Excel with the headings above that row, row 11. Like so:

Date Open High Low Close Adj Close* Volume
Jan 31, 2022 173.88 175.28 172.75 173.40 173.47 65,518,109

In another column to the right, I have the 12 months of the year, starting with January down to December.

In the column right next to the months, I have a formula to calculate the Net Value for that month.

Each close of the month, I insert a new cells below row 11 (the headings) containing the same data as above only with the next last day of the months values. So in the above, it will be February 28th.

I want January's Net Value to stay the same. But I want February's value to now be of off the new inserted row.

I cannot seem to get there with the INDIRECT and ROW functions. If I insert a new row, January's Net Value changes to February's.

Can you help?

Rico says:
March 6, 2022 at 9:35 am

Hi there,

My wife asked me to help her create an excel document for her work where in two columns I need dependent drop down lists.

The main drop down (cell G3, only has single word data) and the first dependent list (H3, gives results of a couple of sentences) was easy. In a parameters sheet I grouped the specific data and gave it a name that corresponds to one of the four choices of cell G3. On the work sheet I used the =INDIRECT function in cell H3 (first dependent drop down)

Now I've been struggling for a couple of days on getting the second dependent drop down (I3) to work. If I use the same function as in H3 then it returns the same options as in H3. When I try to direct it to the correct group name it doesn't work.

The drop down options in H3 are all sentences (with spaces) and I think that's what causing the issue.

Can anyone help/advice on how to get the right drop down data in I3 based on the main drop down (G3)
I3 doesnt even need to depend on whats in the first dependent drop down list (H3) solely on whats in the main drop down list in G3.

Thank you in advance!
Rico

Jason says:
December 14, 2018 at 5:42 pm

I am having an issue getting something to work properly.

I have a workbook that contains a list of Websites, and under each website are the names of the users that are considered experts with those websites. Rather than having people have to search through to find an expert they want to ask questions of, I created a new sheet that includes a drop down list of the websites.

When a user clicks on the drop down list (which is in C2), and selects a website, I am wanting to populate that list in in column F, starting at F2.

I have the basics of it working fine. The issue I am having is that each website has a different number of 'experts'. Some only have one, and some have over fifteen.

My issue is that (unless I am missing something), I needed to place the formula in all 20 cells, so that all the experts would fit. Now, when a website is selected that has less than the maximum number of experts, the remaining cells show #N/A.

I have tried using an =IFERROR to get rid of the #N/A, but am not having any luck.

Do you have any suggestions?

Here are the basics of the code:

The website drop down list is done with Data Validation, using the named range: =Websites

For the experts list, I have used: =INDIRECT($C$2)

When that came up with the extra #N/A's, I change it to: =IFERROR(INDIRECT($C$2),"")

Unfortunately, that did not get rid of the #N/A's.

The list looks similar to this:

John Smith
Fred Jones
Steve Major
Rod Hawke
#N/A
#N/A
#N/A

Please help!

Thank you,
Jason

Roberto says:
October 14, 2016 at 4:32 pm

Good day and thank you for helping all of us!
I have an issue and I'd be so thankful if you could help me.
I have a spreadsheet (daily data) that pulls data from a closed workbook.
I have a folder (auto) with data from every day as a different workbook. Nov 09.xlsx, Nov 10.xlsx, Nov 11.xlsx, etc.

in my example, the data is shown in cell V21 in my 'daily data' spreadsheet. the formula for V21 is ='I:\DOD\Intervals\auto\[Nov 09.xlsx]Daily DOD'!$F21

Now, what I need is the ability to use another cell in my 'daily data' spreadsheet, where I type the date I need the formula in V21 to pull data from.

so I want to use cell A1 to type a date (Nov 09 in this case) and have cell V21 go to 'I:\DOD\Intervals\auto\[Nov 09.xlsx]Daily DOD'!$F21 and give me that data. but if I change the date in A1 to, say Dec 10, I want cell V21 to give me the data in 'I:\DOD\Intervals\auto\[Dec 10.xlsx]Daily DOD'!$F21 instead.
Is there a way to concatenate the date in A1 to the formula in V21? or is there other function/ formula I could use to automate this process, instead if changing the formula in V21 manually every time I change the date I want to see?

Thanks so much!

Brian says:
May 4, 2015 at 10:55 pm

Thank you for the above information. It looks like I am doing this wrong by not using named ranges but I would like to ask anyways.

I'm working on a project that requires a separate "data" workbook and a number of "template" workbooks that are applied to the data workbook to create new user workbooks on the fly. My question is about using indirect to point to tables.

In the template workbooks I have a number of formulas that are pointing to the data workbook. When I create a merged user workbook by adding the template workbook to the data workbook, I'm am using VBA to edit out the file pointers to the data workbook so that the new workbook will not be reaching out to the original data workbook, becoming self contained.