EXCEL FORMULAS

[helipad920]

I have a spreadsheet that I am trying to write a formula for and I can't seem to get it right. I was wondering if someone in the community could assist.

Here is what I have.

 I have dates and text in column A of a spreadsheet. The dates have blank cells below them and then reach a text component (total for transaction 1, 2, 3 etc) before another date is seen. Each change of date should have the corresponding date autopopulate below it until it reaches the next text in a cell.  I am trying to copy the dates, based upon the last date changed but it isn't working. How do i get it to ignore all text in any cell and just populate the dates. The other issue I had was that the date was showing as 1900, even though the date format for the cell is selected.

Appreciate any ideas to reslove this.

[#2 · Accepted solution]

@helipad920 

=IF(ISNUMBER(A2),A2,IF(ISBLANK(A2),B1,A2))

Does this return the intended result?

 

[#3]

@OliverScheurich thanks for reaching out Oliver. The dates are changing at the right moment but the blanks below each new date do not complete with the new date.

For example:

In Cell A6 I have the date 6/1/2023. The formula populated all of the dates in blank cells below that date and copied the text where it came after the populated dates. It recognized the next new date but then populated the original date 6/1/2023 below that date, and so on. See example below.

6/1/2023

6/1/2023

6/1/2023

6/1/2023

6/1/2023

Total for transaction 10

7/12/2023

6/1/2023

6/1/2023

6/1/2023

6/1/2023

Total for transaction 11

7/14/2023

6/1/2023

6/1/2023

6/1/2023

6/1/2023

Total for transaction 12

6/30/2023

6/1/2023

6/1/2023

6/1/2023

6/1/2023

6/1/2023

6/1/2023

[#4]

@helipad920 

=IF(ISNUMBER(A6),A6,IF(ISBLANK(A6),B5,A6))

This formula returns the intended results in my sheet starting with cell A6. Can you attach a screenshot which shows your worksheet along with the applied formula without sensitive data?

[#5]

Cant see screenshot when I paste it into the response. Can you see it? Looks like your formula is working for the dates. I also have numbers in another column. Can I use the same formula or does it need to be different? The data is below. Date in column A and Document in column B. You will note that Document number starts on the same line as date. I need to populate all of the cells with their corresponding number, so date 6/1/2023 has number one in Column B. It needs to copy down to row 8, 2nd number begins at row 10 as it shouldnt copy "Total for Transaction" into column B as this works in column A. The sequence repeats.

Date Document
6/1/2023 1


Total for transaction 1
7/1/2023 2


Total for transaction 2
8/1/2023 3

Total for transaction 3
9/1/2023 4


Total for transaction 4

[#6]

@helipad920 

=IF(ISTEXT(A4),"",COUNT($A$4:A4))

I can't see your screenshot. I'm not sure what you exactly want to do but if you want to return the result shown in my screenshot then you can try the above formula.

 

[#7]

Oliver, just testing to see if you get this. It stated my previous messages could not go through.

[#8]

@helipad920 

Hi Andrew, yes i've received this message. No other messages have been delivered since my last post however.

[#9]

Hey Oliver, is there a way for me to share the spreadsheet I am working on?

[#10]

@helipad920 

Here is an explanation how you can send a private message. It's a bit small i hope you can read it or increase it with zoom.